Auto Increment ID field of Linked table when entering on Form

[Catalyst159]

Hey ChrisPadgham,

Thanks for the input. Looks good. It is a multiuser environment. How could I accomplish doing a save of the record immediately after. Also where would you put the following code:

Me.ID = DMax("[ID]","dbo_problems") + 1

Would you just put it behind the form or the control?

[Catalyst159]

I have a form called "Problem Records List" with a button on it called "cmdNewItem ". The On Click event for "cmdNewItem " is a macro called "Detail list".

The "Detail list" macro contains the following:

Action: OpenForm  
Arguments: Problem Records Details, Form, , 1=0, , Dialog

Action: OnError 
Arguments: Next,

Action: Requery

Action: SearchForRecord 
Arguments: , , First, ="[ID]=" & Nz(DMax("[ID]",[Form].[RecordSource]),0)

Is there a way to include the following code in the "Detail list" macro :

Me.ID = DMax("[ID]","dbo_problems") + 1

Would some thing like this work?:

Action: OpenForm 
Arguments: Problem Records Details, Form, , 1=0 And Me.ID = DMax("[ID]","dbo_problems") + 1 , , Dialog

[Rabbit]

This will be the third time I've said it. Put the code in the BeforeInsert event of the form.

[Catalyst159]

Can I accomplish that in a macro?

I understand what you are saying but, then anytime that form is opened it will assign that value. There is instances where the form is opened to view details of an existing record from another form by click the "ID" which is a hyperlink.

[Rabbit]

Don't do it in a macro, use VBA code. It's the same as using any other event but instead of choosing which macro to run, you paste in the code in the VBA editor.

You don't understand what I'm saying. The code doesn't run every time the form is open. It only runs when a new record is inserted. It doesn't change existing records. That's why you use the BeforeInsert event, because it only fires before a new record is inserted. Hence the name BeforeInsert.

[Catalyst159]

Rabbit, thanks for all your help. The following seemed to work as you indicated previously:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ID = DMax("ID", "dbo_problems") + 1
End Sub

I think I have a better understanding now. Thanks for being patient.

[Rabbit]

No problem, good luck.