Reference: Automatically assign next available number to field 1

[GelC]

Following this thread
thread702-1282693
In previous thread, that person want

For instance, when a user clicks "ADD" and selects system 001, the item number is automatically assigned "6".

I wish it to be done a little different.
User selects system 001 first, then click Add/Edit, the next available item number is assigned to system 001.
How this should be done? Can any one help?

GelC

 

[lameid]

So you want the maximum value in the table not for a particular item.

The key difference in your example is that you want it over the table and not using criteria for a particular item. The third parameter of DMAX is optional and if you leave it blank you should get what you want.

Although, you could forego all that by simply using an Autonumber field in the table. Then each new record would get the next value. Catch here is that if you make a record then delete it, you can not reuse the same number. Generally, that difference is desirable.

 

[GelC]

Thank you for take a look at my problem.
What you suggested must relate to coding, I'm not familiar with VB code, could you please be more specific with your suggestion. I'm truly appreaciate it.

 

[ProgramError]

take a look at dlookup in the vba editors help (F11 when you have access open)

regard

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[englundn]

Make a query with this as the SQL syntax. This creates a table with 1 record and 2 columns. One column with the current max number and a other column with the next number that you need.

SELECT Max(Table1.number) AS MaxOfnumber, Max([number]+1) AS Expr1
FROM Table1;

 

[lameid]

Parameters to functions or procedures follow the call (dmax is a function and therefore uses parenthesis) and are separated by commas. So to not use an optional parameter (which in VBA have to be at the end of the list) you leave it and its preceding comma off (unless you want a different optional parameter after it in sequence).

But as mentioned before you should be able to find help on Dmax by looking in the offline help while in the editor which you should know how to get in order to use the other example.

I still think you just want to design the tabel and change the field you want to increment to have a datatype of autonumber.

 

[GelC]

Thanks for all responses.
However, I have no idea how to put together all your suggestions to make it works...(so sad)
Let's me explain more
Step 1. I have a form where user select their names, then select an available system
Step 2. They can add, edit, or view all items in that selected system in another form.
For example: user select system 002 (there are already 5 items in system 002), when user click add, the item number will automatically assign to 6, and so on)
I'm hoping to explain it clear and someone might help me with this. Million thanks

GelC

 

[PHV]

Why not simply use the DCount function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

You want to do EXACTLY the same thing as the referenced thread says.... as written by PaulF. I have not seen the FAQ the other poster referenced but I have seen his posts in the past and trust it would be useful.

 

[GelC]

I had trouble connecting to Tek-Tips website in pass few days. Thanks for all responses.

lameid,
I already tried the code by PaulF. It gave me all zero for all new records that I attempted to enter in database.
PHV, thanks for your suggestion but I don't know how to write the code. Could you please post few lines of coding for this DCount function?
I'm really appreciate it.

GelC

 

[MichaelRed]

If the value is used to identify unique recoeds in any manner, they will fail in a multiuser environment, The ONLY way to assure uniquness is to lock the recordsource during the provess of generating the new value. The "domain aggregate" function (DSum, DMax, DCount, ... ) do not do this. Further, if you lock the recordset during the retrieval and modification of the new value, you must first obtain exclusive access to it (not pratical in the multiuser environment) and then deny access to it for the duration (which can really agravate the users ... ) and finally, if the recordset (or the data base is large), the time to retrieve a domain aggregate can (will?) continue to increase with the number of records 0 further exciting the angst of the user community.




MichaelRed

 

[JohnPatrickPH]

Hello GelC

try this:

http://www.techonthenet.com/access/modules/sequential_nbr2.php

'Hope it helps

 

[GelC]

Great sample!!!
Thanks a lot, JohnPatrickPH