INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

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

Jobs

Update Two Recods In Same Table

Update Two Recods In Same Table

(OP)
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

(OP)
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

(OP)
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

(OP)
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!

Resources

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