Trying to update 3 fields form a text box... 1

[kalebson]

Can this be done? I have a text box that I would to update the Date in 3 fields in 3 different Tables. Basically if a person is Terminated or Resigns I want to simply put a date in the text box click a button and it change the Last Record for that employees termination date to the value in the text box. I tried to do an update query but I couldnt figure out how to make the query update use a value from the text box. When I put that value in the "criteria" section it didnt seem to work. Any help would be great.

 

[rjoubert]

You'll need to replace table and field names to match your table and form...

DoCmd.RunSQL "UPDATE Table1 SET DateField = #" & Me.FormDateField.Value & "# WHERE EmployeeID = " & Me.FormEmployeeIDField.Value

 

[kalebson]

Let me get a bit more defined. I have a form that simply has an Employee ID#, and 2 text boxes. 1 for Transfer Date and the other for Termination Date. Since a person may transfer to another Department many times they appear on the table more than 1 time in many cases. When a person transfers to another department we change the date of that occurance to the date they transferred add a new record with a default Termination and Transfer Date of 11/26/4637. What I need is..if a person is terminated I need it to change the Termination date that = 11/26/4637 based on the Employee ID #. hope that better explains it. Im not the best at drawing a picture. The update query is updating the wrong record currently ..I need it to look for the 11/26/4637.

 

[kalebson]

thx rjoubert...that works but its updating the first record..more efficient than actually having another query.

 

[rjoubert]

Unless I'm misunderstanding what you're trying to do, all you have to do is add a little to the WHERE clause...


DoCmd.RunSQL "UPDATE Table1 SET DateField = #" & Me.FormDateField.Value & "# WHERE EmployeeID = " & Me.FormEmployeeIDField.Value & " AND TerminationDate = #11/26/4637#

 

[kalebson]

That did the trick right there..Thx sir.