Action on record before PK is created (record saved)

[chris.williams]

Hi All:

I have a situation where I have a "Click to add" function on a subform that lists records in another table. As the last record in the subform, it's listed as "New" instead of "Edit".

When I click on "New", I have an open form command with a where "[ID]=" & Nz([ID],0) statement.

This works well. The problem is that when the subform opens, and data is entered, I have a command button to start a procedure based on the PK ([ID]). Since no ID exists yet (assigned on save), the function requiring the PK reports no valid PK.

I have attempted to insert an

if me.dirty then me.dirty=false

but then it replaces the data in the form with #deleted and give me a write conflict error when I attempt to save the record again (close form).

Question:



How do I save the record to assign the PK in [ID] and then pass that value to the functions that require it?

Thanks...

[John_G]

Hi -

You could define the PK as soon as the record is created in the sub-form, using the BeforeInsert event. This event fires as soon as you type the first character in a new record.

John

[chris.williams]

The PK is an auto-increment field. It only get's written when the record is saved the first time. If I open the subform and then save the record (and close the subform), then go back into it, everything works (because there is a value in the PK). What I want to do is avoid the closing and reopening.

[John_G]

What do you mean by "Auto-increment"? There is no such data type in MS Access. If you are using an autonumber field, the autonumber is assigned as soon as a character is typed in a new record. If you are not using autonumber data type, how is the number generated?

John

[chris.williams]

It's a MySQL back-end and it's AI. The AI PK is assigned as soon as the record is saved.