DMax Question

[The Professor]

Hi,

I'm trying to use DMax to auto increment a field (CustID, a longInt) and enter the value automatically when a new record is created in Customers Table (can't use AutoNumber in this particular situation) so the code I've written is this:

Me.CustID = DMax("CustID", "tblCustomers") + 1

I've stumbled across two problems. One, the code doesn't seem to be doing what I hoped it would and two, I'm unsure where to insert this code.


Should it go into the controls events, or the forms events, and which event should I use? Apologies if I seem dim, but I appreciate some help.

Thanks.

[Missinglinq]

If this is being used in a Multi-User environment, it needs to be the last code in the Form_BeforeUpdate event, so that it is assigned in the last nanosecond before the Record is actually saved. Placed elsewhere, in a M/U environment, and you run the chance that two users will have the same number assigned to the Record they're entering. In over a decade of doing this kind of thing, in the Form_BeforeUpdate event, I've never had a 'duplication' problem.

If this is a Single-User environment , it could be done in the Form_Current event.

Done in the Form_Current event, it will appear on the Record immediately.

Done in the Form_BeforeUpdate event, it will not be assigned and therefore will not be displayed until the Record is saved, i.e after you leave the Record and then return to it, in one way or the other, or explicitly save the Record.

How, exactly, are you 'expecting' this to work?

Linq ;0)>

[The Professor]

Hi Linq,

Many thanks for your helpful reply - much apprecaited.

What I meant when I said the code wasn't doing what I expected (or hoped), is that it was altering the all CustIDs in whatever record was being shown. At first I thought this was because I'd placed the code in the wrong event, but your reply clarified things for me. I got round the problem by enclosing the code in a If Me.NewRecord Then... End If.

Many thanks again.

Prof.

[Missinglinq]

Glad we could help!

Linq ;0)>