Instead of autonumber hell.....

[Scott]

Instead of using the autonumber field type in my table,
how can I make the default value for a field
called "CustomerID" to be set equal to the actual record
number (or equal to the current max value + 1)??? There
was recently some talk about autonumber in posts
to "traci" regarding restarting the autonumber sequence-
this is the same issue I ran into... I tried to use
various iterations of Dlookup and a query (using Max for
group by) but kept getting the oh so explanatory #Name?
error in the form field. Thanks in advance for all
help!!! river at earthlink.net

 

[Gerald Stanley]

Unfortunately, you cannot use any of the Domain functions
(DLookUp etc) in the Default Value property as they are not
recognised by Jet.
One way of achieving your objective would be to code the
Form's current eventhandler to perform the query to
determine the next available CustomerId and populate a text
box that is bound to the customerId column. The downside
of this when used in a multi-user environment is that two
or more users could be allocated the same customerId. This
risk can be reduced by coding the assignment of the
customerId into the form's BeforeUpdate eventHandler. Then
you would probably need to show a message box informing
them of the customerId that has been allocated.

Hope This Helps
Gerald Stanley MCSD

 

[Sandra Daigle]

Hi Scott,

The easiest way to create a custom counter is using the Dmax function where
you grab the highest existing value from the table and then increment it.
This method is fine in some environments but not in a multiuser situation
where 2 or more people are simultaneously entering records into the same
table.

For example: You have a table tblCustomer and you want to increment Custid
without using an Autonum field. The following will get the current max value
of Custid, increment it by 1 and then put the new value into the control
me.custid:

me.custid=nz(dmax("Custid","tblCustomer"),0)+1

Another method that works well for the multiuser situation is create a table
which has only one row. This table holds the next available number for the
counter. Build a function, GetInvoiceNum (or whatever you want to call it).
This function will open the table with the dbDenyRead option, which prevents
other users from opening the table until this instance of the function
closes it. The function gets the next value (the return value of the
function) and the increments it and updates the table. In the error handling
of the function, if the table can't be opened because it is locked, your
function should wait and then try again.

For more info on one method for this see:

ACC2000: How to Create a Multiuser Custom Counter ID: Q210194
http://support.microsoft.com/default.aspx?scid=kb;[LN];210194

Another resource is the Access 20XX Developers Handbook (Volume 2), Litwin,
Getz and Gilbert which has some code for this which you can probably use as
is in your project.

 

[Guest]

Thanks for your help!

I was able to get it to work using
=DMax("CustID","CustDataTable")+1 in the Default Value field, but I also now see the potential problem in a multiuser environment... I will fiddle with the BeforeUpdate event handler as suggested by Gerald, or create a table just for storing the current max record number as suggested by Sandra.

I also have an "Item Table" which is linked to the customers... next problem to make my brain explode will be how to program Default Value to be the max available Item number for the CURRENT CustomerID (the primary key for the Item Table being CustID and ItemID linked together). I have =DMax("ItemID","ItemDataTable")+1 for my Default Value field, but how to constrain it to look only at the current customer... Access will drive me insane yet hahahaha...

thanks again
(e-mail address removed)

 

[Mike Painter]

Thanks for your help!

I was able to get it to work using
=DMax("CustID","CustDataTable")+1 in the Default Value field, but I also

now see the potential problem in a multiuser environment... I will fiddle
with the BeforeUpdate event handler as suggested by Gerald, or create a
table just for storing the current max record number as suggested by Sandra.

I also have an "Item Table" which is linked to the customers... next

problem to make my brain explode will be how to program Default Value to be
the max available Item number for the CURRENT CustomerID (the primary key
for the Item Table being CustID and ItemID linked together). I have
=DMax("ItemID","ItemDataTable")+1 for my Default Value field, but how to
constrain it to look only at the current customer... Access will drive me
insane yet hahahaha...If you fight it, it will. Autonumbers are the most secure way to assign
values in most situations.
Unless you have a specific need to *manually* assign numbers that you know
are unique such as a SSN, there is rarely any advantage to it and usually
disadvantages.

Modern computers (anything over 300Mhz and 256k RAM) and Access allow
looking things up by name ,date or invoice number with blinding speed.
People know their name, they are likely to remember the approximate dates
and they will have the invoice with them if it is a problem. They will not
know their customer number.
If you are keeping different item numbers for the same item depending on
what the person bought this can be displayed in a report with no need to
assign separate real numbers and the nightmare that will cause.

 

[Guest]

Unless you have a specific need to *manually* assign numbers that you know
are unique such as a SSN, there is rarely any advantage to it and usually
disadvantages.

Mike- I guess other than getting a feel for how many items we have sold for a particular customer while inputting data it probably isn't needed, except my boss wants it that way (read Scott Adams "Dilbert" for an explanation on that); I expect it will help reduce confusion among employees. I see there are quite a few posts regarding autonumber- I will continue to look through those also.

thanks

 

[Sandra Daigle]

Hi Scott,

It is easy to put criteria into the DMax function:

=DMax("ItemID","ItemDataTable", "Custid=" & me.custid)

 

[Guest]

Yup... that did it! Thanks so much!!!

Hi Scott,

It is easy to put criteria into the DMax function:

=DMax("ItemID","ItemDataTable", "Custid=" & me.custid)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks for your help!

I was able to get it to work using
=DMax("CustID","CustDataTable")+1 in the Default Value field, but I
also now see the potential problem in a multiuser environment... I
will fiddle with the BeforeUpdate event handler as suggested by
Gerald, or create a table just for storing the current max record
number as suggested by Sandra.

I also have an "Item Table" which is linked to the customers... next
problem to make my brain explode will be how to program Default Value
to be the max available Item number for the CURRENT CustomerID (the
primary key for the Item Table being CustID and ItemID linked
together). I have =DMax("ItemID","ItemDataTable")+1 for my Default
Value field, but how to constrain it to look only at the current
customer... Access will drive me insane yet hahahaha...

thanks again
(e-mail address removed)