Update with where clause

[racha0601]

Hi, I have a Loans form and an Equipment table.

I would like the 'Available' checkbox in the Equipment table to be unchecked when when the user clicks the 'Submit' button on the Loans form (i.e. when an item is loaned out) - but I only want the checkbox corresponding to the item (Equipment ID) that has been selected to change.

I have tried this:

Private Sub Submit_Click()

CurrentDb.Execute "Update Equipment Set Available = 0 Where Loans.Equipment ID = Equipment.Equipment ID"

End Sub

but it doesn't work

I am very new to VB so apologies if the above is just silly coding!

Thanks in advance for any advice!

Rachael

 

[pbaldy]

More like:

CurrentDb.Execute "Update Equipment Set Available = 0 Where Equipment.[Equipment ID] = " & Me.[Equipment ID]

 

[Kiwiman]

Howzit

[Sorry Paul - Our posts must have crossed]

Try

Private Sub Submit_Click()

CurrentDb.Execute "Update Equipment Set Available = 0 Where Loans.Equipment ID =" & me.[Equipment ID]

End Sub

 

[racha0601]

Howzit

Try

Private Sub Submit_Click()

CurrentDb.Execute "Update Equipment Set Available = 0 Where Loans.Equipment ID =" & me.[Equipment ID]

End Sub

Thanks Kiwiman but I'm getting a syntax error! Tried moving the speech mark after = to the end but that didn't work either?

Rach

 

[racha0601]

More like:

CurrentDb.Execute "Update Equipment Set Available = 0 Where Equipment.[Equipment ID] = " & Me.[Equipment ID]

Thanks Paul but I'm getting a 'datatype mismatch in criteria expression'?

Rach

 

[Kiwiman]

Howzit

See PBaldy's post - Post 2 - mine forgot the square brackets.

 

[Kiwiman]

Howzit

What about this

CurrentDb.Execute "Update Equipment Set Available = 0 Where Equipment.[Equipment ID] ='" & Me.[Equipment ID]  & "'"

 

[racha0601]

Brill! Thanks Kiwiman!

Rach