Code behind update query (also posted in general section)

[Sprocket]

I have two tables: Client and Appointments. They are joined by the field ClientID. This is the primary key in Client and the Foriegn key in Appointments. Sometimes it is nescessary to transfer all the appointments assigned to client "X" to Client "Y" and then delete Client "X"
To do this I want to run an update query from a button that asks the user for two ID numbers and inserts these numbers into the SET and WHERE arguments of the update query.

I thought I could use two Inputboxes to gather the values, hold these temporarily and insert them into the query but the script fails and I get the message:

"Too few parameters, Expected 2."

If I enter a numerical values directly it works OK but does not seem to recognise the variables: Client1 and Client2

This is the code I'm using

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click
Dim dbs As DAO.Database
Dim Client1 As Integer
Dim Client2 As Integer

Client1 = InputBox("Which Client ID do you want to delete?")

Client2 = InputBox("Which Client ID do you want to receive any appointment details?")

Set dbs = CurrentDb
dbs.Execute "UPDATE Appointments SET ClientID = Client2 WHERE ClientID = Client1;"

dbs.Close
Exit_Command23_Click:

Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub


If I can get this bit to work first I want to go on to delete the primary record in the Client table. I thought I might use the same approach but any suggestions would be gratefully recieved.

 

[David R]

Try storing your query as an Update Query and running it that way, instead of building it dynamically here.
Make a small form from which you can select the CLientIDs from combo boxes. Your execute button will just be:

DoCmd.SetWarnings False
DoCmd.OpenQuery "UpdateQueryName"
DoCmd.OpenQuery "DeleteQueryName" 'eventually
DoCmd.SetWarnings True

Your update query should be:
UPDATE Appointments SET Appointments.ClientID = Forms!FormName!SecondComboBox WHERE Appointments.ClientID = Forms!FormName!FirstComboBox

Make sure your queries work correctly before you turn off warnings however!