"Transactional" record updates

[Montez659]

Can anyone point me in the direction of a "transactional" record update?

What I mean is that I have 4 different SQL statements in vba executing on a command button, and if any one of those fail, I want the whole thing to fail. Otherwise, junk records will be created.

I would google it but I really don't even know the correct terminology to search for.

Thanks in advance.

 

[xenou]

Okay. Simplest is if you have a form. When you begin a new record the autonumber value is assigned (populated) right then. So I think you can take it right from the form field. Is that possible?

I can think of at least three other ways to do this (and I'm sure I could come up with three more). But of course, simplest is best in most cases.

 

[Montez659]

That's how a typical bound form would work wouldn't it? But I am trying to avoid the creation or saving of the record until all information is filled out.

Ok, come to find out, I am not sure that it is the fact that the record isn't being committed that is the problem. I moved the Dlookup outside of the transaction and it is still retrieving the previous records id. I am wondering if there is a lag between the committing process and the actual populating of that record to the table.

I definitely need something more consistent. So, are you suggesting that the form be bound to the autonumber field only and create the id as soon as data is entered? If this is the case how would you go about canceling the record if you don't want it - a delete query?

 

[Norie]

Montez

You can cancel a record by hitting Esc.

Then it won't get saved to the table.

The only thing is, and it shouldn't really matter, the autonumbering will skip one.

eg if you start a new record and it's auto number is 20 but you cancel it the next record will have the auton number 22, not 21

Of course if you are using a random autonumber...

 

[Montez659]

You can cancel a record by hitting Esc.

I've never even heard of this before. Is it a default property on a bound form or is there something that I need to set to enable it?

 

[Norie]

I think it's default, not only on most forms but tables too.

Of course if you have code that's saving the record rather than Access saving it like it usually does things might be different.

 

[xenou]

^^ Right. ESC once cancels the current control if you are in a textbox or combobox (or what have you). ESC twice and you cancel the entire form. Nothing is committed. Same as Me.Cancel when in form code.

A lot depends on if you are working with a form or purely in code. Of course, you might answer "both" - but if you have a form to hold your code (even a hidden one) it can be bound to the table with your autonumber field in the form. In that case, it would be populated by Access as soon as you start a new record.

-----

About "lags" - the main thing is that in most databases (Access included) data that is uncommitted may not be visible outside of its transaction.

 

[Norie]

Montez

Are you not including the autonumber field in your forms?

 

[Montez659]

About "lags" - the main thing is that in most databases (Access included) data that is uncommitted may not be visible outside of its transaction

Yeah, but I had actually comitted the transaction and it seems that there was still a lag after that. When I stepped through the code, it seemed to pick it up. Who knows, not the best way to do it anyways.

Are you not including the autonumber field in your forms?

No, I was doing it all via CurrentDB.Execute in VBA so there was no need to include it.

Same as Me.Cancel when in form code.

So technically, if I made all the controls of the form bound and then had a "Cancel" command button with Me.Cancel in it, the record would never be (technically) be created (although the next autonumber would be skipped)?

 

[Norie]

Sorry I don't understand.

What were/are you using CurrentDB.Execute for?

To update/append to the junction table?

 

[Montez659]

I had three different tables that were being updated. Two were having new records added to them, one of those was a Junction table. The third was an update query, which will still need to be run.

The reason I did this all in code was to keep the record from saving until after all of then information was correctly input.