How to find and fill in a new number automatically?

[Jack G]

Say I have a table with Parts Numbers that are something like A101, A102,
A103, B101, B104, B105, C101,.... etc. I'd like to be able to hit a button
for a new record, be prompted for the Prefix (A, B or C) and then have
Access figure out what the next available number would be and create a new
record using that number (for example, if I answered "B" to the prompt, it
would create a new record with a part number of B106).



I've thought of making queries that are sorted on the part number for each
prefix using "Like A*", etc., but I can't figure out how to extract the part
number of the last record (which I would then increment and use for the new
record). Nor do I know how to take that number to plug into the new record.



Can anyone suggest how I might approach this??

 

[Guest]

You just need the DMax function:
Dim varNewPartNum as Variant
varNewPartNum = DMax("[PART_NO]", "PartsTable", "Left([PART_NO],1) = '" _
& Me.txtPreFix & "'")
If IsNull(varNewPartNum) Then
Me.txtPartNum = Me.txtPrefix & "001"
Else
Me.txtPartNum = Left(varNewPartNum,1) & _
Format(Right(varNewPartNum, 3) + 1, "000")
End If

 

[Jack G]

That's great. Thanks for your help!!

You just need the DMax function:
Dim varNewPartNum as Variant
varNewPartNum = DMax("[PART_NO]", "PartsTable", "Left([PART_NO],1) = '" _
& Me.txtPreFix & "'")
If IsNull(varNewPartNum) Then
Me.txtPartNum = Me.txtPrefix & "001"
Else
Me.txtPartNum = Left(varNewPartNum,1) & _
Format(Right(varNewPartNum, 3) + 1, "000")
End If

Say I have a table with Parts Numbers that are something like A101, A102,
A103, B101, B104, B105, C101,.... etc. I'd like to be able to hit a
button
for a new record, be prompted for the Prefix (A, B or C) and then have
Access figure out what the next available number would be and create a
new
record using that number (for example, if I answered "B" to the prompt,
it
would create a new record with a part number of B106).



I've thought of making queries that are sorted on the part number for
each
prefix using "Like A*", etc., but I can't figure out how to extract the
part
number of the last record (which I would then increment and use for the
new
record). Nor do I know how to take that number to plug into the new
record.



Can anyone suggest how I might approach this??