Update Two Recods In Same Table (1 Viewer)

[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.

 

[Minty]

Firstly you don't really need two fields for vehiIn and vehiOut , as they are presumably mutually exclusive ? e.g. if vehiIn is false it must be out?
What is the SQL for the query your have that isn't working, if you post it up I am sure we can assist.

 

[StanJx]

Hi Minty, 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.

Like you suggested I changed the structure to have only one Yes/No field.

Here is the SQL that I am using. I tried to 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 tblDriver SET vehiAvailable = False WHERE DrvID= " & Me.cmbVehicle

 

[Minty]

I think you may need to use 0 and -1 for the True / False values.
If you add a debug.print TempVars!TempAssign just to make sure the value is being passed in.

 

[StanJx]

Just realized a small mistake on the code I posted. Here's the correct post.

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

I used the debug.print and the value seems to be accurate. I am getting an error on the first of these two lines. Too few parameters Expected 2.

 

[Minty]

Did you try setting it to -1 for True ;

CurrentDb.Execute "UPDATE tblVehicle SET vehiAvailable = [COLOR="Red"]-1[/COLOR] WHERE DvrID= " & TempVars!TempAssign & " ;"

 

[StanJx]

Did you try setting it to -1 for True ;

CurrentDb.Execute "UPDATE tblVehicle SET vehiAvailable = [COLOR=Red]-1[/COLOR] WHERE DvrID= " & TempVars!TempAssign & " ;"

I tried it out but I am still getting the "Too Few Parameters. Expected 2" 3061 Error. The TempVars Value seems to be fine. Any other method to get this done.

I need to update two boolean records in the same table at the click of a button. One to a old record. and one according to the new. Running the new record seems fine when I test it solo. Just can't seem to get the old record line to update.

 

[Minty]

Copy and paste the sql generated into a new query and run it, what happens?

 

[StanJx]

Are you asking me to run the code like this?

UPDATE tblVehicle SET vehiAvailable = -1 WHERE DvrID
Or with values?

 

[Minty]

Create a string variable, sSql or similar, build your string exactly as you are now but assign it to the string - this is a good way of debugging and helps clarify your code, so something like;

Dim sSql as String

sSql = "UPDATE tblVehicle SET vehiAvailable = -1 WHERE DvrID= " & TempVars!TempAssign & " ;"

debug.print sSql

CurrentDb.Execute sSql

Now in the debug window you can examine and if required cut and paste your sql query.

 

[StanJx]

Create a string variable, sSql or similar, build your string exactly as you are now but assign it to the string - this is a good way of debugging and helps clarify your code, so something like;

Dim sSql as String

sSql = "UPDATE tblVehicle SET vehiAvailable = -1 WHERE DvrID= " & TempVars!TempAssign & " ;"

debug.print sSql

CurrentDb.Execute sSql

Now in the debug window you can examine and if required cut and paste your sql query.

I tried this out but I am still getting that error.

Here's the debug print result. It seems fine. The value is of the old Vehicle No.:banghead:

UPDATE tblVehicle SET vehiAvailable = -1 WHERE DvrID= PA-9788 ;

 

[Minty]

Ah ha - now I see - the DvrID is a string - you need to put single quotes around it, I assumed as it was an ID it was a number in which case your syntax was correct.

Dim sSql as String

sSql = "UPDATE tblVehicle SET vehiAvailable = -1 WHERE DvrID= [COLOR="Red"]'[/COLOR]" & TempVars!TempAssign & "[COLOR="red"]'[/COLOR] ;"

debug.print sSql

CurrentDb.Execute sSql

This should work. SQL query strings need quotes around strings, # # around dates and Access expects them to be formatted in US style e.g. #09/30/2016#
Number don't need escaping with anything.

 

[StanJx]

Ah ha - now I see - the DvrID is a string - you need to put single quotes around it, I assumed as it was an ID it was a number in which case your syntax was correct.

Dim sSql as String

sSql = "UPDATE tblVehicle SET vehiAvailable = -1 WHERE DvrID= [COLOR=Red]'[/COLOR]" & TempVars!TempAssign & "[COLOR=red]'[/COLOR] ;"

debug.print sSql

CurrentDb.Execute sSql

This should work. SQL query strings need quotes around strings, # # around dates and Access expects them to be formatted in US style e.g. #09/30/2016#
Number don't need escaping with anything.

Now I am receiving Too few Parameters Expected 1 :banghead::banghead::banghead:

 

[Minty]

Are your tables in access or sql server ? You may need to add to the execute method
CurrentDb.Execute sSql , dbSeeChanges

What happens if you paste the sSql string into a new blank query and try and run it?

 

[StanJx]

Hi, was able to get this working. Can't believe it was such a simple thing :banghead::banghead::banghead::banghead::banghead::banghead:. One was the string. And the other was the fact that I had miss spelled DrvID as DvrID and next was the fact that I was trying to pass a string value to a number filed. DrvID is a auto number field once I gave the WHERE parameter as VehicleNo it worked like a charm. Thanks for all your help. And apologies if I wasted your time.