Use input from Form to make up part of custom incrementing number

[asevans81]

Okay, first off, just want to say thanks for the assist. Here is what I need to figure out

The way I have this setup is the user inputs a number into a control on a form, the control is called LotNumber (its a text box) and it is a separate column in the table bound to the form. But I don't know how to actually take this number and place it in code so it can be used to create the first 6 digits of another number I call KESerialNumber. I need to add an auto incrementing number to the end of this 6 digit number to make it a 9 digit number. Here is the process we would like to happen in plain English, because I do not know how to code it :/ ---> Step 1. User types in a LotNumber in its own control (text box) in a form Step 2. Access Takes that LotNumber and combines it with an autoincrementing sequence number starting at 001 and going up by one for each new record. Step 3. The sequence number resets to 001 once a new LotNumber is entered OR goes to the highest LotNumber and KESerialNumber avail in case previous records were entered using an older or previous LotNumber. The user needs to be able to go back and enter records based on previous LotNumber(s). So if the the LotNumber is 042313 and the auto incrementing number is 030 (which would make the KESerialNumber 042313030) because 30 records were created using LotNumbrer 042313 then if a new lot number is entered, say 042413, the incrementing number starts at 001, but if the old lot number is entered then the next incrementing number would be 031, which would make the KESerialNumber 042313031). I have fields in the bound table for LotNumber and KESerialNumber, I do not need to store the autoincrementing (i.e. 001,002,003 etc.) number.

I have put together some code that uses the date to create the first 6 digits, but I need the first 6 digits to be derived from the user input typed into the form. I don't know if it would be easier to modify this code or start from scratch, but this code works great if the date is used to generate the first 6 digits. here's the code

Public Function Autonum(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
  dv = Format(Now(), "mmddyy") * 1000 + 1
  Autonum = dv
  Exit Function
End If

'format the number as an 9 digit number
dv = Format(dmval, "000000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 6)
'get today's date
dt2 = Format(Now(), "mmddyy")
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
  Seq = Seq + 1 'increment the sequence number
  'add the sequence number to the date and return
  Autonum = Format(Val(dt1) * 1000 + Seq)
  Exit Function
Else 'the dates are different
  'take today's date and start the sequence with 1
  Autonum = Format(Val(dt2) * 1000 + 1)
End If

End Function

What do you think?

 

[nschroeder]

If I understand correctly, and to condense it down, you have a single form bound to a table containing LotNumber and KESerialNumber. When they create a new record and enter a lot number, if it's a new number, you want KESerialNumber to be plugged with the lot number and 001 appended to it. If it's an existing number, you want KESerialNumber plugged with the lot number and nnn appended to it, where nnn is the highest existing last three characters of KESerialNumber for that lot number, plus 1. Am I correct?

Before I suggest some code, would it work to have a the incrementing number (IncrNum) stored in the table instead of KESerialNumber? If KESerialNumber includes LotNumber, then you're storing redundant data in each record, but if you store IncrNum, then just have your queries and/or reports calculate KESerialNumber as LotNumber & IncrNum. It would be more efficient, and also easier to work with since you don't have to extract the incrementing number from the last three characters. Does that make sense?

 

[asevans81]

You are absolutely correct and I have through struggle and turmoil worked it out using DMax and a separate field with the Seq Number. Thanks for the help!

 

[nschroeder]

Excellent! So I assume you deleted KESerialNumber from your table, and added a new numeric SeqNum field. Then on the form in the existing control that was bound to KESerialNumber, you changed the control source to:
=[LotNumber] & Format([SeqNum],"000")

I don't know where you ended up putting the DMax code, but I would suggest AfterUpdate of LotNumber:

Private Sub LotNumber_AfterUpdate()
    With Me.SeqNum
        .Value = DMax("[SeqNum]", "your table name", "[LotNumber] = '" & Me.LotNumber.Value & "'") + 1
        If IsNull(.Value) Then
            .Value = 1
        End If
    End With
End Sub

You may also want to add some editing to the LotNumber_BeforeUpdate event to make sure they've entered a 6-digit number, that it's numeric, etc.
Also, this may or may not be an issue but if they can go back to existing records and change the lot number, this code would update SeqNum in a way you don't want. For example:
A record contains LotNumber 112233, SeqNum 7
Another contains LotNumber 112233, SeqNum 8
If the go back to the first record and retype 112233, the SeqNum will be updated to 9.

Another thing to consider is if you want SeqNum to be a value the user can update. If so, they could change it back to 7 in the above case, but it might be better to make it a locked field so they don't mess with it, and you may even want to make it not visible.

 

[asevans81]

Yes you are right on with that! I got it working wonderfully, thanks for all the help!