generating special ID for new record

[YZF]

Ok here is the next step i need to figure out

I need to have unique ID for each of my record in my form, and it should be created automatically.It should look like this:

AB060106-1
AB060106-2
AB060107-1
AB060107-2
AB060107-3
AB060108-1
and so on.

Idea is to have two or three letters at the front that never change, then 6 numbers that represent current date (year,month,day), and then incrementing numbers for that day.Whenever day changes, this last number starts from 1 up to 999 and next day it resets to 1 again.


Any ideas where should i start from? Thanks

 

[neileg]

I would hold this as three separate fields and conctatenate them for display purposes. Clearly the fixed part is fixed, the date part can be built using Date() and formatting the results. You can use DMax() to return the highest value already used on that day, and increment this by one.

In a multiuser environment there is always the possibility that you might end up with non-contiguous numbers. You can minimise this risk by calculating the ID immediately before you save the record.

 

[YZF]

Hi,

How do i make Dmax to check for highest number for "that" day? Maybe you have any such egzamples?


Thanks!

 

[neileg]

DMax([MyNumberField],[MyTable],[MyDateField]=Date())

This assumes that you have a table called [MyTable], that you are holding three fields as I suggested and that the one holding the number is called [MyNumberField] and that you are using today's date.

 

[ScottGem]

To add to Neil's answer since the prefix is fixed, you don't need to store that. To DISPLAY the full ID you would use:

="AB" & Format([MyDate],"yymmdd") & "-" & [MyNumber]

 

[neileg]

Thanks, Scott.

I did wonder if the 'fixed' element might be variable, sooner or later!

 

[ScottGem]

Agreed, However, if that occurs, the field can be added at that time and existing records filled with the original string.

 

[YZF]

O tried but something is wrong....

This is the code i wrote in vba in the afterupdate event:

Mydatefield = Date()
Mynumberfield = DMax([Mynumberfield], [tblMytable], [Mydatefield] = Date()) + 1
codefield = "AB" & Format([Mydatefield], "yymmdd") & "-" & [Mynumberfield]


And i get error "access can't find the field "forms" refered to in your expression"...


Also the value of datefield is 38726 (date is 2006.01.09), and i need it to be 20060109...right?

 

[ScottGem]

Try:

Mynumberfield = Nz(DMax("[Mynumberfield]", "tblMytable", "[Mydatefield] = #" & Date() & "#"),0) + 1

Also eliminate the MyDatefield = Date(). The above expression will add 1 to the highest value of MyNumberfield in your table for the current day.

 

[YZF]

I did change everything, but now i get error:

"Syntax error in date in query expression "[mydatefield] = #2006.01.10#"

What could be wrong?


The whole code:

Private Sub Customer_Afterupdate()
mynumberfield = Nz(DMax("[mynumberfield]", "tblmytable", "[mydatefield] = #" & Date & "#"), 0) + 1
codefield = "AB" & Format([mydatefield], "yyyymmdd") & "-" & [mynumberfield]

End Sub


mydatefield = new field that i created in tblmytable and set properties to "date/time"

mynumberfield = new field that i created in tblmytable and set properties to "number"

codefield = new field that i created in tblmytable and set properties to number

In my form: codefield textbox is created (mynumberfield and mydatefield textbox aren't )

 

[YZF]

Now i tried to change mynumberfield to:

mynumberfield = Nz(DMax("[mynumberfield]", "tblmytable", "[mydatefield] = Date()"), 0) + 1

And it generates code, but only "AB-1" "AB-2" and so on, the date part is missing. If i fill myself mydatefield in my table, then it generates full code, but when i go to new record it again shows only part of code....can't find "mydatefield" value or smth like that.....in this case.

 

[neileg]

You seem to have given the controls on the form the same name as the fields. Access does this by default which is really stupid and can screw up your VBA. You would do well to adopt a convention like calling text boxes something like txtMyBox, combos cboMyCombo, etc.

 

[ScottGem]

First, codefield cannot be a number field since you are including text. Second, I agree with Neil about naming conventions. Third, The MyNumberfield expression only increments THAT field. To displa the WHOLE code you need to include the Date formatting. Please review the full instructions gave you.