Coding Update SQL in an Event Procedure

[CBG2112]

I'm trying to store the value from cboStatus to StatusTx and from txtChangeDate to StatusDt where StatusID is equal to Status ID. I've searched and was able to get this. I am not familiar with SQL or VBA. I appreciate any help I can get. Thanks.

DoCmd.RunSQL "UPDATE tblTask SET "[StatusTx]=" & cboStatus & AND "[StatusDt]=" & txtChangeDate & WHERE "StatusID=" & [StatusID] &.

 

[vbaInet]

This is the basic syntax of an UPDATE statement. See if you can adapt your code:

docmd.runsql "UPDATE clients SET surname = 'Bloggs', firstname = 'Joe' WHERE clientID = 24;"

 

[CBG2112]

I was able to come up with this however an error message appears stating "Data type mismatch in criteria expression."


DoCmd.RunSQL "UPDATE tblTask " & _
"SET tblTask.[StatusTx]='" & Me.cboStatus.Value & "'" & _
"AND tblTask.[StatusDt]='" & Me.txtChangeDate.Value & "'" & _
"WHERE tblTask.[TaskID]='" & Me.TaskID.Value & "';"

 

[vbaInet]

Could you list out the datatypes of each of the fields?

 

[CBG2112]

TaskID = AutoNumber(PK)
StatusTx = Text
StatusDt = Date/Time
cboStatus comes from StatusID = AutoNumber(PK) and StatusDescTx = Text

Would it be the cboStatus producing the mismatch?

 

[vbaInet]

You just need to handle each datatype appropriately. Strings - single quotes, numbers - nothing, dates - hash character:

DoCmd.RunSQL "UPDATE tblTask " & _
             "SET [StatusTx]='" & Nz(Me.cboStatus.Value, "") & "' " & _
             "AND [StatusDt]=#" & Me.txtChangeDate.Value & "# " & _
             "WHERE [TaskID]=" & Nz(Me.TaskID.Value, 0) & ";"

I would also advise you validate the date using IsDate() before calling the code.

And remember the spaces at the end of each concatenated line, you missed that in your last post but I added it.