Delete

[goldstar]

Hello All,

Just a quick query, i have two tables, one contains the list of items in each hire and the other table the client details etc with regards to that order.
I have a transaction list form where all the transactions that are placed will appear.
If the transaction for a certain order is deleted i want the details for that hire to be deleted from both tables.

This is what i have been trying but it does not like it.

CurrentDb.Execute ("Delete * from tblitemhire, tblhire Where tblitemhire.itemno & tblhire.itemno =" & Forms!frmtransactionlist!txtitemno)

[Scott Price]

I notice you are using the concatenate character & instead of AND in your sql statement. In normal English they mean the same, but VBA doesn't, actually, use 'normal English'...

Try changing it to AND, and see what happens.

Regards,
Scott

[FishVal]

Scott is absolutely right - SQL is not a human launguage.

"tblitemhire.itemno AND tblhire.itemno =" & Forms!frmtransactionlist!txtitemno

should be "rephrased" to

"tblitemhire.itemno=" & Forms!frmtransactionlist!txtitemno & " AND tblhire.itemno =" & Forms!frmtransactionlist!txtitemno

If that still doesn't help Then make deletions from each table by separate SQL commands
End If

Kind regards,
Fish

[goldstar]

Hi again,

That didnt work, i tried doing it seperatltey see below

CurrentDb.Execute ("Delete * from tblitemhire Where tblitemhire.itemno=" & Forms!frmtransactionlist!txtitemno)
CurrentDb.Execute ("Delete * from tblhire Where tblhire.itemno=" & Forms!frmtransactionlist!txtitemno)

Data Mismatch error appears highlighting the second of the two.

[FishVal]

Quotes (') and Double-Quotes (") - Where and When to use them.
And be aware of Nulls.

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [CODE] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

ADMIN.

[NeoPa]

Looking at this from a completely different angle, why not just set up the relationships between the tables such that casdcade updates and cascade edits are enabled. That way no code would be required at all.