how to make a delete query

[androomc]

hey folks. a little of your help needed (im quite new to this and your wisdom seems infinite)

a quick summary of what i have...

customers table
vehicles table

linked by...

sales table (1-1)
interests table (1-N)

a vehicle is either 'in stock' or 'out of stock'. whilst a vehicle is 'in stock' customers can be 'interested' in that vehicle.

1) when i make a sale of a vehicle i'd like to change its vehicle_IN_STOCK control to false. not quite sure how to go about doing that (it doesnt exist on my sales page - only its vehicle_ID).

2) also, when a vehicle is sold i'd like all of the 'interests' records for that vehicle to be deleted, since its no longer in stock, its irrelevent if people are still interested in it.

i think i need a delete query (no idea how to make one) for the second problem but i have no idea how to solve the first.

your help appreciated.
andy.

 

[Calvin]

Although you tagged your post *** Solved *** I would like to offer the following:

1.

CurrentDB.Execute "UPDATE VehiclesTable SET [vehicle_IN_STOCK]=False WHERE [Vehicle_ID]=" & me![Vehicle_ID]

2.

CurrentDB.Execute "DELETE * FROM VehiclesTable WHERE [Vehicle_ID]=" & me![Vehicle_ID]

 

[androomc]

cheers i had only solved part 2 so your post helped me a lot

 

[androomc]

hmm. i've been messin with that code, and i wanted to use yours because it was more concise. but the problem is, execute doesnt seem to exist as a method. i get a method error when i try to add those lines into my command button in VB.

so i figured i'd just create them as queries and run those queries but i cant seem to get the syntax correct. could you maybe re-post those as queries i can run? also, i think there's an error in your 2). shouldnt it be "DELETE * FROM interest" since thats the table that contains the records i want to remove?

 

[Calvin]

Hay, my bad on the previous code it should be "CurrentDB.Execute" not "docmd.Execute"

I went ahead and corrected it.

If you need to delete ALL records in an existing table then yes use

CurrentDB.Execute "DELETE * FROM interest"

but it will do just that DELETE ALL RECORDS in the named table, so use with caution. Use a WHERE condition to delete specific records.

 

[androomc]

you are a genius. i tried that code at it worked without a hitch!

such a feeling of hardwork (and a lot of help) finally paying off

the only thing is, every time a problem is solved there's another waiting.

thanks again.

 

[Calvin]

I usually find there are two or more problems waiting for everyone solved.

Glad I could help, but I'm off to bed now.