Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Update Two Recods In Same Table

Update Two Recods In Same Table

Update Two Recods In Same Table

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.

RE: Update Two Recods In Same Table

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

RE: Update Two Recods In Same Table

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.

CODE -->

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"

RE: Update Two Recods In Same Table

what is the value of TempVars!TempAssign

try msgbox TempVars!TempAssign

RE: Update Two Recods In Same Table

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 ;

RE: Update Two Recods In Same Table

No, it does not, to me:

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Update Two Recods In Same Table

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close