VBA SQL when record created - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

VBA SQL when record created

Bruce McCormick

2 years ago

There's that caveman language again...

I wish to create a record for something i call an Agreement ID (IDAgreement) at the earliest practical moment, which by my thought train is as soon as i have a customer ID and an Order ID. Given both of those ID's are in tblAgreement, I am running an INSERT INTO statement simply to place those two items into a hopefully new record. I thought this in itself would create the new record, but i ain't seeing it. The statement is not throwing any errors, but i suspect like last time, i am missing something...

CurrentDb.Execute "INSERT INTO tblAgreement (IDCustomer, IDOrder)" & _
"VALUES (" & IDCustomer & ", " & NewIDOrder & ")"

After that line executes i have a "STOP" in place, open tblAgreement but nothing new is there.

Is it my learning SQL syntax or something else that has to be done?

As always, thank you very much. I love what can be done with SQL, but having the hardest time driving it into my brain-

	Kevin Yip @Reply 2 years ago
CurrentDb.Execute doesn't give certain error messages such as index key violation, validation rule violation, etc. You need to use DoCmd.RunSQL to see those errors: DoCmd.RunSQL "INSERT INTO ..."

	Bruce McCormick OP @Reply 2 years ago
Kevin, you sure hit that nail on the head! A type conversion... Thank you-

Thomas Gonder @Reply

2 years ago

@ Bruce, Access by default grabs an autoID when a record goes dirty. And then saves the record when told to do so. This is another one of those questionable design flaws in Access. Since I create my own autoIDs, I use them when the save is actually done, not when a record goes dirty. The main reason is to avoid missing IDs which may then have to be investigated at a later date. As in, where did that dang record go?!!!

You didn't say where you are creating this ID. I suppose in a form? You may end up with the reverse problem from above. A record that exists without the rest of the corresponding data. Do you want to open a hole in your application like that?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View