Runtime error 3464

[gunterbrink]

I am having a problem with an input form where it takes data from the form and inserts into the table. I used the same code for the same database(different table names and forms etc) but i am having a problem when I either try to add a manifold or edit a manifold and update. This is where it is hanging. I cannot attach the db file as it is too large by 92kb. Any suggestions/ with explanation would be great as I am tring to learn Access. Thank you in advance.



CurrentDb.Execute "UPDATE mf_list " & _
" SET MFID=" & Me.txtMFID & _
", MFType='" & Me.cboMFType & "'" & _
", MFName='" & Me.txtMFName & "'" & _
", MFRecertDate='" & Me.txtMFRecertDate & "'" & _
", MFLocation='" & Me.txtMFLocation & "'" & _
" WHERE MFID=" & Me.txtMFID.Tag

[pbaldy]

What's the text of the error? You're treating all the update fields as text; is that correct? One looks like a date, which would require # as a delimiter instead of '.

[gunterbrink]

data type mismatch in criteria expresion

[CJ_London]

what is error 3464? this usually explains where the problem is. At the moment there are a couple of possibles - RecertDate looks like it should be a date field, but you are treating as text. The other is you appear to be trying to update an ID field which is not allowed if it is an autonumber and if not perhaps breaks data integrity rules.

[pbaldy]

Change the delimiter for the date field.

[gunterbrink]

runtime error 3464
data type mismatch in criteria expression. the MFID field is not an auto numbered field. How would I handel the # in the code:

", MFRecertDate= # & Me.txtMFRecertDate & #" & _

?

[gunterbrink]

I tried the above and it didn't work as well as a couple of other variations. I tried changing the field from date/time to short text, but it is still giving the same error code

[pbaldy]

I said # instead of ', so:

", MFRecertDate=#" & Me.txtMFRecertDate & "#" & _

[gunterbrink]

ok, so i tried that and it is still kicking me the same error, I hate to keep bothering you guys I'm sure this is EXTREMELY elementary. Is there a different way to attach the db as it is 762kb. or email to someone for a look.

[gunterbrink]

I was able to attach the db, please see first post for attachment.

[pbaldy]

You should be able to attach after zipping it.

[pbaldy]

Your MFID field is text, so needs ' delimiters both in the SET and WHERE clauses.

[gunterbrink]

Trying to learn, Like this?

CurrentDb.Execute "UPDATE mf_list " & _
" SET MFID='" & Me.txtMFID &"' _
", MFType='" & Me.cboMFType & "'" & _
", MFName='" & Me.txtMFName & "'" & _
", MFRecertDate=#" & Me.txtMFRecertDate & "#" & _
", MFLocation='" & Me.txtMFLocation & "'" & _
'" WHERE MFID="' & Me.txtMFID.Tag

[pbaldy]

More like:

CurrentDb.Execute "UPDATE mf_list " & _
" SET MFID='" & Me.txtMFID & _
"', MFType='" & Me.cboMFType & "'" & _
", MFName='" & Me.txtMFName & "'" & _
", MFRecertDate=#" & Me.txtMFRecertDate & "#" & _
", MFLocation='" & Me.txtMFLocation & "'" & _
" WHERE MFID='" & Me.txtMFID.Tag & "'"

You'll find it helpful to use a variable for the SQL and this technique to see how it's coming out:

BaldyWeb-Immediate window

[gunterbrink]

That fixed it! You are awesome! Thank you, Good explanation too.