Delete records, where condition


What is the VBA code to delete records where
tblFX.FXID = tblFXRates.FXrateID ?

I have a form and subform but the delete button I have only deletes the tblFX.FXID and not the related record on tblFXRates.FXrateID.  I know I can set referential integrity to delete the related record but the db says I cant set this relationship due to referential integrity violations.  

pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
Try deleting the child record, tblFXRates.FXrateID, then the parent, tblFX.FX.ID.
pdvsaProject financeAuthor Commented:
What is the code though?  I am not a programmer.   Thanks
Jeffrey CoachmanMIS LiasonCommented:

First of all, you should check your RI settings...
The default setting *Should Not* allow you to delete the main record. (if it has existing child records)

But as  aebea states, typically, you must delete the child records first.
Also note that you always need some sort of validation before running any "destructive" code like this (because it cannot be undone)

<What is the code though?  I am not a programmer.>
You did not state whether this key value was text or numeric (here I will presume numeric).
You did not post any details about your tables or the form.
It is also not clear what your existing code is.  
Finally, It is not clear if this:
    tblFX.FXID = tblFXRates.FXrateID the actual expression needed, or just an example..
So as you can see, it is difficult to give you the exact code needed.
(This is why we always ask for a sample database)

In most cases it would be something like what I have here in the attached sample database.
I am sure with your experience here, you can adapt this to work in your database


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
thank you.  Sorry for delayed response.  I have been out of pocket for a bit.  Very nice explanation.
Jeffrey CoachmanMIS LiasonCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.