Problems with Update query using vba

[jackie77]

Hi All

Its been ages since I've been on here so hello to everyone again

Im having some probs getting an update query using vba to work have tried doing loads of things but cant seem to get it to work, Im sure its something small and stupid Im doing! what I have at the minute is below and i get an error too few parameters expected 4

Any help would be great

Cheers
Jackie

Private Sub cmdLogin_Click()
 
Dim strUpdate As String
Dim db As DAO.Database
Dim Exp1 As String
Dim Exp2 As Boolean
Dim Exp3 As Integer

    Exp1 = Me.ExpirationID
    Exp2 = Me.Removed
    Exp3 = Me.cboEmployee.Value
           
        
strUpdate = "UPDATE Expiration Set Removed = Exp2 AND RemovedBy = Exp3 WHERE ExpirtionID = Exp1"
CurrentDb.Execute strUpdate, dbFailOnError

        DoCmd.Close acForm, "Login3", acSaveNo

 

[pbaldy]

You have to concatenate variables into the string. If they're all numeric:

strUpdate = "UPDATE Expiration Set Removed = " & Exp2 & " AND RemovedBy = " & Exp3 & " WHERE ExpirtionID = " & Exp1

 

[jackie77]

Cheers thanks for the reply I have made the changes you have suggested but its still getting an error but its now on line:

CurrentDb.Execute strUpdate, dbFailOnError
and it says "to few parameters expected 1"

any ideas?

Cheers

Jackie

 

[pbaldy]

Double check the spelling of everything, plus the data types. This may help:

http://www.baldyweb.com/ImmediateWindow.htm

 

[jackie77]

Hi
I have double checked everything and found a spelling mistake it all looks good now with no errors so thanks again for the help however when I look in the table the record is not updated I have checked the variables by adding in

MsgBox Exp1 & Exp2 & Exp3
to the code and they all look good not sure whats going on?

Any further help would be great

Cheers

Jackie

 

[Ari]

Hi

strUpdate = "UPDATE Expiration Set Removed = '" & Exp2 & "' , RemovedBy = '" & Exp3 & "' WHERE ExpirtionID = " & Exp1

 

[pbaldy]

What is the SQL produced by the method in post 4? What happens if you paste that SQL to a blank query and run it? What are the data types of the fields?

 

[jackie77]

Hi

Thanks again for the replies

Ari - I tried ur suggestion but Im back to getting an error this time its "Data type mismatch in criteria expression"

Pbaldy - added code from post 4 and sql all looks good, I have pasted it to new query and it pastes in fine - the data types are autoNumber, Yes/no Booleon, Long integer

Cheers

Jackie

 

[pbaldy]

I missed the "And" in your SQL, which should be a comma as Ari has it.

 

[jackie77]

Hi There

Got it sorted, just think I needed a nights sleep was looking at it for way to long

I used Ari sugesstion and changed my variable declarations to strings and bobs your uncle lol

Thanks to you both for your help was most appricated

Thanks again