Re: MS Access sequence numbers

Why not fire a [Record Select] and then a [Record Save] command (from VBA code) attached perhaps to the Forms!YourFormName.OnCurrent event.

You could do these [<operations>] above through methods on the Form's Recordset Clone or you could do it through calling the commands by referring to their GUI menu locations.

This will tell access to save the record as soon as you get to it.

This should be O.K. for either existing records or new records.

In the case of a new record, Access will then show the trigger generated value because it is in effect looking at an already-saved record.

Now obviously this won't work if there are non null columns to be filled out that don't have defaults set for them (either attached to the Form's text box etc. controls or set through DDL on the server).

Because Access implements optimistic 'locking', it will trap a potential lost update which could occur if someone else started committing changes to this new 'shell' record whilst you are still working on it.

One downside to this is that if your client screens crashed, you might never ultimately save the full info. for a record (leaving gaps in the tables as a result).

This should be weighed up against the desire to let the user see the sequence number (generated by Oracle) at the start of user-editing of new records.

David P.

Oracle Certified DBA.