Add Next Number Based on Two fields

[dancox via AccessMonster.com]

tblCostCodes
Area Last 3 of Cost Code
12 900
12 901
13 900
13 901
14 900
15 900

Here is what I need - when I select Area 12 in combo box on my form, I want
the last 3 to automatically select the next sequential number 902. There
will be multiple 902's in the column, but only one per Area. In a query, I
concatenate Area&"-"&Last 3 of Cost Code and get 12-902.

 

[Allen Browne]

In the AfterUpdate event procedure of the combo, this is a NewRecord.
If so, use DMax() to get the highest CostCode so far for the area.
Use Nz() in case there are none.
Add 1.
Assign the result.

This kind of thing:
If Me.NewRecord Then
Me.CostCode = Nz(DMax("CostCode", "Table1", _
"Area = " & Nz([Area],0),0) + 1
End If

If these are Text fields (not Number fields), needs extra quotes:
http://allenbrowne.com/casu-17.html
In this case, you may need to use Val() to get the highest value too: 2
sorts as greater than 10 on a character-by-character basis.