Run-Time Error 3464 on UPDATE SQL (1 Viewer)

donsi · Jul 26, 2019

Hi All,

I started exploring the SQL statement in VBA rather than building a query and I am having issue with Update query.

So basically what I want is to get a msgbox which is asking user "If you have a car or not" and based on the answer (YES/NO), I want to check off the checkbox (HasCar) in the TblUsers.

I am capturing UserID in TempVars when user logs in, and was hoping to use that in the query.

Code:

If MsgBox("Will you be driving a car to work?", vbYesNo + vbQuestion, "Car Owner Verification") = vbYes Then
CurrentDb.Execute " UPDATE TblUsers SET TblUsers.HasCar = -1 WHERE TblUsers.ID= '" & TempVars("UsrID").Value & "'", dbFailOnError

isladogs · Jul 26, 2019

Is it UsrID or UserID? Also is it a number datatype or text?

theDBguy · Jul 26, 2019

Hi. What is the error message/description for 3464? Try assigning your SQL statement into a String variable and then do a Debug.Print, so you can examine what is actually being executed.

donsi · Jul 26, 2019

isladogs said:
Is it UsrID or UserID? Also is it a number datatype or text?

The TempVars is UsrID which saves the ID field from the table when user logs in, which is set as Autonumber in TblUsers.

Now I am not sure when the TempVars is saved if it converts datatype to string

Code:

Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("TblUsers", dbOpenSnapshot, dbReadOnly)
    rs.FindFirst "EmpNo =" & Forms!FrmLogin.TxtEmpNo
    TempVars("GUserName") = rs!EmpNO.Value
    
    If rs.NoMatch = True Then
        GoTo KL
    ElseIf (rs!Deactive) = True Then
        GoTo DA
    Else
        TempVars("EmpName") = rs!EmpName.Value
        TempVars("LicNo") = rs!LicNo.Value
        TempVars("AccessLevel") = rs!AccessLevel_ID.Value
        TempVars("Sites_ID") = rs!Sites_ID.Value
        TempVars("UsrID") = rs!ID.Value

donsi · Jul 26, 2019

theDBguy said:
Hi. What is the error message/description for 3464? Try assigning your SQL statement into a String variable and then do a Debug.Print, so you can examine what is actually being executed.

Run-Rime error' 3464':

Data type mismatch in criteria expression.

Debug.Print result

UPDATE TblUsers SET TblUsers.HasCar = '-1' WHERE TblUsers.ID= '2684'

theDBguy · Jul 26, 2019

donsi said:
Run-Rime error' 3464':

Data type mismatch in criteria expression.

Debug.Print result

UPDATE TblUsers SET TblUsers.HasCar = '-1' WHERE TblUsers.ID= '2684'

Hi. Thanks for the clarification. You're getting a type mismatch error because you're delimiting the Tempvars value with single quotes in your code. Try changing the last part of the code to this:

Code:

...WHERE TblUsers.ID= " & TempVars("UsrID").Value, dbFailOnError

Hope it helps...

donsi · Jul 26, 2019

theDBguy said:
Hi. Thanks for the clarification. You're getting a type mismatch error because you're delimiting the Tempvars value with single quotes in your code. Try changing the last part of the code to this:

Code:

...WHERE TblUsers.ID= " & TempVars("UsrID").Value, dbFailOnError

Hope it helps...

Thanks for that. It fixed the runtime error, however, it is not updating the record on the table. Not even sure where to begin to look.

isladogs · Jul 26, 2019

Your HasCar field is Boolean but you are trying to update to a string value which won't work.
It should be

Code:

UPDATE TblUsers SET TblUsers.HasCar = True WHERE TblUsers.ID= " & TempVars("UsrID").Value

donsi · Jul 26, 2019

isladogs said:
Your HasCar field is Boolean but you are trying to update to a string value which won't work.
It should be

Code:

UPDATE TblUsers SET TblUsers.HasCar = True WHERE TblUsers.ID= " & TempVars("UsrID").Value

That did the trick. For I was looking at the value which gets assigned to the checkbox (-1 for true and 0 for false)

Thank you very much for quick replies.

isladogs · Jul 26, 2019

-1 would also have worked without the quote marks.
Anyway you now have a solution which does the job....
Good luck with the rest of your project.

theDBguy · Jul 26, 2019

donsi said:
That did the trick. For I was looking at the value which gets assigned to the checkbox (-1 for true and 0 for false)

Thank you very much for quick replies.

Hi. Congratulations! Glad to hear you're all sorted out. Good luck with your project.

Run-Time Error 3464 on UPDATE SQL (1 Viewer)

donsi

Registered User.

isladogs

MVP / VIP

theDBguy

I’m here to help

donsi

Registered User.

donsi

Registered User.

theDBguy

I’m here to help

donsi

Registered User.

isladogs

MVP / VIP

donsi

Registered User.

isladogs

MVP / VIP

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread