Auto Increment ID field of Linked table when entering on Form

[Catalyst159]

Anyone have any Ideas on how to accomplish this.

[Rabbit]

Yes, basically what I laid out before. Use the BeforeInsert event of the form to populate the ProblemID. Use the DMax() function to get the last ID and add one to it. The DMax() syntax is in post #10.

[Catalyst159]

The problem is that I only want to use the DMax() when I use the 'cmdNewItem' button to open the "Problem Records Detail" form for adding a new record.

[Rabbit]

Adding a new record triggers the BeforeInsert event.

[Catalyst159]

So it should work then right ?

[Catalyst159]

But when I click the ID in the "Problem Records List" Form, it will also bring up that record in the "Problem Records Detail" form. Will this be problematic in that case?

[Rabbit]

Yes it will work. No it won't be a problem.

[Catalyst159]

I am still a little confused with the syntax. Is something like the following ok:

DMax("ID", "dbo_problems", "ProblemID = ID +1")

[Rabbit]

More like

DMax("ID", "dbo_problems") + 1

There's no need to specify which ID because it doesn't matter. You just want the max.

[Catalyst159]

Ok, let me try that. This should then populate the ID textbox on the "Problem Records Detail" form?

[Rabbit]

By itself? No. That only gets the value, you have to assign it to the control you want that value to go into.

[Catalyst159]

How can I test that

DMax("ID", "dbo_problems") + 1

is getting the correct value using the Immediate Pane to test it?

[Catalyst159]

How can I assign it to the control you want that value to go into?

[Catalyst159]

To Test in immediate window I used:

? DMax("[ID]", "dbo_problems") + 1

This does work.

How can I assign this to the control on the form that I want the value to go into though?

[ChrisPadgham]

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

If you are in a multiuser environment you may like to do a save of the record immediately after this to prevent two users obtaining the same record id