Cant delete records in query
Experts, I have a form that is built off a query of 3 tables. I can not delete any records. I am believe it is because of the joined tables. The form is opening in datasheet mode.
How can I delete records?
Below is the complete query:
thank you
SELECT
tblRepayment.*, IIf([CurrencyID]=1,0,([tbl
FROM
(tblRepayment INNER JOIN tblCurrencyExchange ON tblRepayment.Currency = tblCurrencyExchange.Curren
ORDER BY tblRepayment.ValueDate;
How can I delete records?
Below is the complete query:
thank you
SELECT
tblRepayment.*, IIf([CurrencyID]=1,0,([tbl
FROM
(tblRepayment INNER JOIN tblCurrencyExchange ON tblRepayment.Currency = tblCurrencyExchange.Curren
ORDER BY tblRepayment.ValueDate;
ASKER
hi Scott and thank you for the reply. Ok so I see what you are saying. I made a button and put that code in the event procedure but I do have a compile error. The error says "expected ='. Let me know what you think.
here is the code as I have it with the updated field names.
Private Sub cmdDelete_Click()
currentdb.Execute("DELETE * FROM tblRepayment WHERE DrawIDrpmt=" & Me.ID, dbFailOnError)
End Sub
here is the code as I have it with the updated field names.
Private Sub cmdDelete_Click()
currentdb.Execute("DELETE * FROM tblRepayment WHERE DrawIDrpmt=" & Me.ID, dbFailOnError)
End Sub
Deletes of rows in queries that join multiple tables can be difficult to grasp. You need to look at the relationship to understand that only the "lowest" level record will be deleted.
For example
1. tblCustomer - tblOrder, tblOrder is a child of tblCustomer. When you delete from this query, only the selected order will be deleted. The customer will not be deleted.
2. tblCustomer - tblOrder - tblOrderDetails, tblOrderDetails is a child of tblOrder and tblOrder is a child of tblCustomer. Only the selected row from tblOrderDetails will be deleted
Since tblCustomer is the "top" level table, you can only delete rows from a query that does not include "child" tables. You also need to consider the setting of Cascade Delete. Only set Cascade Delete to true when you want all childeren to be deleted also.
For example, you almost never want to delete a Customer if that customer has orders so this relationship would not specifiy Cascade delete. However, if your application allows Orders to be deleted, then you would want to specify Cascade Delete so that if you delete an order, all the related rows in tblOrderDetails would also be deleted.
For example
1. tblCustomer - tblOrder, tblOrder is a child of tblCustomer. When you delete from this query, only the selected order will be deleted. The customer will not be deleted.
2. tblCustomer - tblOrder - tblOrderDetails, tblOrderDetails is a child of tblOrder and tblOrder is a child of tblCustomer. Only the selected row from tblOrderDetails will be deleted
Since tblCustomer is the "top" level table, you can only delete rows from a query that does not include "child" tables. You also need to consider the setting of Cascade Delete. Only set Cascade Delete to true when you want all childeren to be deleted also.
For example, you almost never want to delete a Customer if that customer has orders so this relationship would not specifiy Cascade delete. However, if your application allows Orders to be deleted, then you would want to specify Cascade Delete so that if you delete an order, all the related rows in tblOrderDetails would also be deleted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Pat, thank you for the explanation. You are right it is a bit to grasp. I understand concept but just knowing the code is not so easy for me.
Rey, (not at a computer now) i think thats it because i do have a null. I will test in a few hours and revert.
Rey, (not at a computer now) i think thats it because i do have a null. I will test in a few hours and revert.
The error says "expected ='.You'd have to replace "Me.ID" with "Me.WhateverYourIDFieldIs"
ASKER
back at a computer.
I reimported into a new db.
The issue seems to be parenthesis though.
Rey's code does not have parenthesis.
You can see the only difference between the codes is one doesn't have parenthesis.
I think I should award Rey. Any objections please let me know.
I reimported into a new db.
The issue seems to be parenthesis though.
Rey's code does not have parenthesis.
You can see the only difference between the codes is one doesn't have parenthesis.
I think I should award Rey. Any objections please let me know.
ASKER
Thank you
If you want to delete a record from tblRepayment, you can do this:
Currentdb.Execute("DELETE * FROM tblRepayment WHERE YourIDField=" & Me.YourIDField, dbFailOnError)
If you want to delete a record from a different table, then you'd have to have either (a) the ID field from that table or (b) a combination of fields from that table which would point to the record.
I'd also urge you to use Joins instead of DLookups in this manner.