Custom Record Numbers

[jr_00_22]

I am in need of a custom record number in a form to be stored in my table. I would like it to act like an autonumber howerver I need it to read "00-000" or in order would be "08-001","08-002","08-003" and so on, where "08" represents the year and "003" represents the record number. Futhermore I need the "08" to change as the year changes and the "003" to start back at "001" when the year changes. I have been coding with vba for a about a month now and have learned alot, but I have know idea what is the best way to do this. Any help would be great and thank you in advance.

 

[Rabbie]

Try reading Access Help about the DMAX function. You should be able to create what you want using this

 

[WayneRyan]

JR,

This is very common here. I'd suggest that you maintain two fields:

TheYear - Integer
TheSequence - Integer

Don't let the user edit them.

Use the Form's BeforeInsert event to assign:

Me.TheYear = DatePart("yy",Date)

Me.TheSequence = Nz(DMax("[TheSequence]", "YourTable", "[TheYear] = " & Me.TheYear), 0) + 1

When you display, on forms/reports, use:

Format(TheYear, "00") & "-" & Format(TheSequence, "000")

Wayne

 

[jr_00_22]

Sorry for the late reply it took me this long to get this. This is what I've got.

Private Sub Text1_AfterUpdate()
Me.Text2 = Format([Text1], "yy")
Me.Text3 = DMax("[Rec# part 2]", "Table1", "[Rec# part 1]= '" & Forms!Form1!Text2 & "'") + 1
If IsNull(Me.Text3) = True Then
Me.Text3.Value = "1"
Else
End If
Me.Text8 = ([Text2]) & "-" & Format([Text3], "000")
End Sub

This works nicely, however by using this the record number can be deleted and changed if [Text1] (the date) is updated (not what I need). I want to use something like what you suggested (Private Sub Form_BeforeInsert) But part of my record number comes from the [Text1] that needs to be complete before it calculates the record number. Do you have any suggestions?

P.S. Feel free to spruce up my code and thanks very much for the help.

 

[gemma-the-husky]

personally i would use an autonumber key, and store this information in two fields as wayne suggested

 

[jr_00_22]

Ok I got something that works, used a simple IF statement.

Private Sub Text1_AfterUpdate()
If IsNull(Me.Text8) = True Then
Me.Text2 = Format([Text1], "yy")
Me.Text3 = DMax("[Rec# part 2]", "Table1", "[Rec# part 1]= '" & Forms!Form1!Text2 & "'") + 1
If IsNull(Me.Text3) = True Then
Me.Text3.Value = "1"
Else
End If
Me.Text8 = ([Text2]) & "-" & Format([Text3], "000")
Else
Exit Sub
End If
End Sub

Thanks for all the help.

 

[jr_00_22]

I need more help. I fixed my last problem but my boss would like to see something different. He has asked me to make the record number dependant of the date value from low to high. This means that if you have one record on 1/1/2008 @ record number 08-001 and the next on 1/9/2008 @ record number 08-002 then your next record you enter is on 1/2/2008 it needs to now be record number 08-002 and your record on 1/9/2008 needs to move to record number 08-003 to keep the dates in order. Keep in mind the hole time this happening the first part of the record number needs to be the last two digits of the year, and the last part of the record number has to start back at one when the year changes.

Does anyone have any ideas? My boss is a pain in my as*.

 

[gemma-the-husky]

its important to understand that the record number is immaterial - it SHOULD just provide a mechanism for linking records together. If your boss is interested in seeing specifically formatted information, then these are attributes of the record and NOT INTRINSICALLY OR NECESSARILY anything to do with the record number.

in this instance, neither you nor your boss should even need to see the record number, once the app is running (although you may need it for debugging purposes). You shouldnt enter data directly into tables, and forms CAN handle record numbers invisibly.

Do it this way, and your issues with record numbers will be easily resolved

 

[Rabbie]

If you are using the Record number as the PK/FK it is going to cause you mega problems every time you have to change it.

I would suggest that since you have to keep your boss happy you have a hidden autonumber field for linking purposes and only use the Record Number for sorting the data. That way everyone should be happy.

Of course if you don't use the record number for linking to other records then things will be simpler (but still quite complicated).

 

[jr_00_22]

its important to understand that the record number is immaterial - it SHOULD just provide a mechanism for linking records together. If your boss is interested in seeing specifically formatted information, then these are attributes of the record and NOT INTRINSICALLY OR NECESSARILY anything to do with the record number.

in this instance, neither you nor your boss should even need to see the record number, once the app is running (although you may need it for debugging purposes). You shouldnt enter data directly into tables, and forms CAN handle record numbers invisibly.

Do it this way, and your issues with record numbers will be easily resolved

Yes this record number is not necessary and is used only for an attribute of the form. However this is how we refer to records, as record number 08-007 and so on. This number may/may not be used for a query in a report but it does need to be seen on the form. If my boss tells me to get incident number 08-004 I need to know which on to get and no I don't see us using this number to link other records.

 

[gemma-the-husky]

your reply to my last comment

Yes this record number is not necessary and is used only for an attribute of the form. However this is how we refer to records, as record number 08-007 and so on. This number may/may not be used for a query in a report but it does need to be seen on the form. If my boss tells me to get incident number 08-004 I need to know which on to get and no I don't see us using this number to link other records.

in access, each record needs a unique identifier, in order to be able to manage the degree of relatedness - so in this table you NEED such an idenitifer - a PRIMARY KEY. Now, the primary key can be numeric - an autonumber if you like which means you dont even have to input the number - access finds a new one for you - OR the primary key can be a string you define

BUT trying to have primary keys like 001-008 and 002-013 etc is asking for trouble.

You should have a numeric key, and store these bits as attributes. You can still index them, search on them, and have no duplicates to make sure the strings are unique, but you really dont want to try and manage primary keys looking like these.