By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Delete

P: 49
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.
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute ("Delete * from tblitemhire, tblhire Where tblitemhire.itemno & tblhire.itemno =" & Forms!frmtransactionlist!txtitemno)
Mar 4 '08 #1
Share this Question
Share on Google+
6 Replies


Scott Price
Expert 100+
P: 1,384
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
Mar 4 '08 #2

FishVal
Expert 2.5K+
P: 2,653
Scott is absolutely right - SQL is not a human launguage.
Expand|Select|Wrap|Line Numbers
  1. "tblitemhire.itemno AND tblhire.itemno =" & Forms!frmtransactionlist!txtitemno
should be "rephrased" to
Expand|Select|Wrap|Line Numbers
  1. "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
Mar 4 '08 #3

P: 49
Hi again,

That didnt work, i tried doing it seperatltey see below
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute ("Delete * from tblitemhire Where tblitemhire.itemno=" & Forms!frmtransactionlist!txtitemno)
  2. CurrentDb.Execute ("Delete * from tblhire Where tblhire.itemno=" & Forms!frmtransactionlist!txtitemno)
Data Mismatch error appears highlighting the second of the two.
Mar 4 '08 #4

FishVal
Expert 2.5K+
P: 2,653

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 6 '08 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 6 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.