Reset autonumber in a concatenate field

[Fish218]

I would like to build a concatenate field using Year, ID Number, and a 3-digit autonumber. Year is a combo box in a form (YYYY), NumID is the two or three digit ID Number for a person, and then the three digit autonumber. I would also like the three digit autonumber to follow in sequence based on Year and NumID. For example, when starting a new year I would like the autonumber to reset to 000. When entering data for multiple people, I would like the autonumber to advanced based on the last autonumber for that person, not the next autonumber in the list. Any suggestions?

[orange]

I don't think autonumber is what you need or want. Autonumbers are guaranteed to be unique, but not necessarily in sequence.
You may wish to build a function to create the numbers you want

YYYY from current year a number that you control and adjust by NewValue = Dmax(value) +1

However, you may want to tell us why exactly you want/need/have selected this scheme. What is the purpose of this that can't/shouldn't/couldn't be achieved by some other means. Your rationale may be perfect and justified, but it isn't necessarily an obvious choice.


Autonumbers are usually used as primary keys for tables, and handled exclusively by Access (or other DBMS) for system usage. The autonumbers are not intended for human consumption/use iiiin most (if not all) situations.

[jzwp11]

First, the word year is a reserved word in Access, so it should not be used as a field name (I used myYear as the field name in the example below). With that said, you will need some Visual Basic for Application (VBA) code to do what you want. The code will look something like this (air code, not tested)

IF DCount("*","yourtablename", "myYear=" & YourYearCombo & " AND NumID=" & controlname)=0 THEN
  myincrementcontol=0
ELSE
  myincrementcontrol=1+DMax("myincrement","yourtablename", "myYear=" & YourYearCombo & " AND numID=" & controlname)
END IF

I believe the code needs to be in the before insert event of the form, but i may be wrong. You will probably want some code prior to the above to check to make sure that a year was selected from the combo box and that the NumID was entered and is valid.

[Fish218]

What is the "*" after DCount( ?

[jzwp11]

It is a wild card to represent any field since you are just counting records. I believe that you could substitute one of the fieldnames from the table if you want. The general form of all the domain aggregate functions is : functionname("fieldname", "query or table name", "criteria")

[Fish218]

I figured this one out by using a query to lookup the number of fish caught (varFishCount), by year and collector. Thanks for pointing me in the right direction!

Private Sub Length_AfterUpdate()
Dim varYear As Integer
Dim varCollector As Integer
Dim varFishCount
varYear = [Forms]![frmCommercial2]![Form1]![Text161]
varCollector = [Forms]![frmCommercial2]![Form1]![CollectorID]
varFishCount = DLookup("[CountofIndividualFishID]", "qry2012", "[CollectionYear]=" & varYear & " And [CollectorID]=" & varCollector)
If (IsNull(varFishCount)) Then
[Text51] = 0
[Text53] = 1
ElseIf Not (IsNull(varFishCount)) Then
[Text51] = varFishCount
[Text53] = varFishCount + 1
End If
[FishNum] = [Forms]![frmCommercial2]![Form1]![Text161] & [Forms]![frmCommercial2]![Form1]![CollectorID] & (Format([Text53], "0000"))
End Sub

[jzwp11]

Glad you worked out a solution!