Update Two Recods In Same Table

[StanJx]

I need to update two records of the same table (tblVehicle) during a button (cmdAssign) click. The record is selected from a list box and at the button click the currently assigned vehicle's info (vehiIn = True , vehiOut = False [Where = vehicleNo{Current}]) needs to be updated. And simultaneously the same new vehicle's info (vehiIn = False , vehiOut = True [where = vehicleNo{Changed}]) needs to be updated. I have tried to do this but I keep running into errors. Would appreciate advice on the best practice for this.

 

[PWise]

what are you trying to achieve?
what is your code ?
what error msg are you getting ?

 

[StanJx]

Thanks for your response. I think the issue is the WHERE statement because the value is taken from the same field. I used TempVars to store that value temporarily from a textbox that is populated on a list click.

I have made a change in the structure to have only one Yes/No field (vehiAvailable) rather than two.

Here is the SQL that I am using. I tried recordset but it was the same. The problem occurs only in the TempVars SQL string.

CurrentDb.Execute "UPDATE tblVehicle SET vehiAvailable = True WHERE DvrID= " & TempVars!TempAssign
CurrentDb.Execute "UPDATE tblVehicle SET vehiAvailable = False WHERE DrvID= " & Me.cmbVehicle

I am getting the error "Too few parameters. Expected 2"

 

[PWise]

what is the value of TempVars!TempAssign

try msgbox TempVars!TempAssign

 

[StanJx]

I checked the value with a debug.print the value seems fine. It doesn't update to the new value.

I checked the SQL string as well. This too looks fine

UPDATE tblDriver SET VehicleAvailable = -1 WHERE DvrID= PA-9788 ;

 

[Andrzejek]

No, it does not, to me:
[tt]
UPDATE tblDriver
SET VehicleAvailable = -1
WHERE DvrID = [highlight #8AE234]'[/highlight]PA-9788[highlight #8AE234]'[/highlight] [/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[StanJx]

Yeah I figured it out that plus I had spelled DrvID as DvrID and was trying to pass a string value on a number field (DrvID is a auto number) Once I figured the first part out the rest was simple. I set the WHERE parameter to the VehicleNo field and worked like a charm. Thanks for all your help and support apologies if I wasted your time.