Record won't delete using db.execute

[GK in the UK]

Here's an article about transactions in bound forms:

How to use transactions in Microsoft Access VBA - Codekabinett

I haven't tried it largely because of the warning against user input within a transaction.

 

[Galaxiom]

Thanks for the transacted bound form example which I'd not seen before. Very clever!
Do you have any links to other attempts to get this working for a form & subform, even if they are unsuccessful.

It isn't my idea. I don't know who did it originally but I have since seen it mentioned with Leigh Purvis in a thread from 2009.
I came across it in this thread. ChrisO (RIP) had some criticisms of it there. Best pay attention when ChrisO has an opinion.

Spikel was looking for a solution here but nobody came up with anything.

I mentioned on one of the threads that I thought it should be possible but couldn't get it to work though I didn't really persist. I still hope it would be possible as it would a fantastic structure. Good luck if you want to give it a shot.

I don't really remember the problem I ran into but it was to do with moving to the subform. Maybe it was with the master record not being saved so the subform records couldn't be written. There would be ways around this using VBA to write the link fields. Or maybe it wouldn't let the focus shift to the subform while the mainform was Dirty.

 

[isladogs]

Thanks Greg & GK.
Potentially a very powerful tool but the various warnings are very clear.

 

[debsamguru]

Pat, all I have in my BeforeUpdate is a msgbox to show me when it is called. And sometimes it is called and sometimes it isn't and I can see no rhyme or reason as to when it is called. I can do the same action (open the form, then use the Close button to close it straight away) 3 times - once it will display the BeforeUpdate msg, the other 2 times, it won't. So all I can do is assume that it won't happen and make contingencies to delete the created records.

 

[Mike Krailo]

There has to be more than a msgbox in there or it won't fire. Pat showed you some examples how to do it.

 

[NauticalGent]

Best pay attention when ChrisO has an opinion.

 

[isladogs]

I haven't followed this thread in detail as you've already had a lot of good advice.
However, a couple of things that haven't been mentioned and may just be relevant?
1. In post #6, you showed Currency as " " - a single space. Try an empty string "" if you really need that value. Or omit it?
2. Why are you using variant for CustomerInputID. Use Number or Text as appropriate