Add letters to sequential numbers

[bassplayer79]

washerapp.zip
OK heres the DB. You might get some error messages since you wont be connected to SQL server. The washer number generator will work since its bound to a local DB. Thanks for your help!!!

[RuralGuy]

Great! After C&R and ZIP then go to Advanced posting and scroll down to Manage attachments.

[RuralGuy]

It looks to me like you get the error after you add a WA to the WAS field and then press the button again. I think it is coming from the DMax() line. Still looking.

[bassplayer79]

It looks to me like you get the error after you add a WA to the WAS field and then press the button again. I think it is coming from the DMax() line. Still looking.

I think your right on. When I debug my code, I get the error on the dmax line.

[bassplayer79]

Also I noteiced my spelling mistake. Any way you could adjust my thread title and remove the "es" of Addes?

[RuralGuy]

Will the characters added to the WAS *always* be just 2 characters and will they always be "WA"?

[RuralGuy]

Also I noteiced my spelling mistake. Any way you could adjust my thread title and remove the "es" of Addes?

Taken care of!

[RuralGuy]

Any answer to my post #21?

[June7]

If [WAS] is an alpha-numeric, can't just add 1 to increment, have to parse the text part out. Here is how I generate unique alphanumeric ID:

Public Function NewSample() As String
Dim strLabNum As String
'search for aborted lab number and use that record, else if none then create new record
strLabNum = Nz(DLookup("LabNum", "Submit", "IsNull(DateEnter)"), "")
If strLabNum <> "" Then
    CurrentDb.Execute "UPDATE Submit SET DateEnter=#" & Date & "# WHERE LabNum='" & strLabNum & "'"
Else
    strLabNum = Nz(DMax("LabNum", "Submit"), "")
    If strLabNum = "" Then
        'this accommodates very first generated number of blank database
        strLabNum = Year(Date) & "A-0001"
    Else
        'this accommodates change in year
        If Left(strLabNum, 4) = CStr(Year(Date)) Then
            strLabNum = Left(strLabNum, 6) & Format(Right(strLabNum, 4) + 1, "0000")
        Else
            strLabNum = Year(Date) & "A-0001"
        End If
    End If
    CurrentDb.Execute "INSERT INTO Submit(LabNum, DateEnter, EnterWho) VALUES('" & strLabNum & "', #" & Date & "#, '" & Form_Menu.cbxUser & "')"
End If
Form_SampleManagement.ctrSampleList.Requery
NewSample = strLabNum
End Function

[bassplayer79]

Will the characters added to the WAS *always* be just 2 characters and will they always be "WA"?

Yes the characters will always be "WA", and no deviation from that.

[bassplayer79]

Taken care of!

Thank you very much!!

[RuralGuy]

You could if you wanted, just prepend the "WA" every time it is displayed and not modify the table at all. Just a thought.

[RuralGuy]

Probably a better solution is to add a new field for the "WA" and put the two fields together when displaying them. That will give you the flexibility to have something other then "WA" in the future.

[bassplayer79]

Ok so heres what I did. I took it a slightly different direction then I initially thought I would. I created it as a function, did I need to? No but hey lifes to short. I created a new table that dumps the numerical numbers. I then call that function and add "WA", and dump that combination into a text box on my form, which is bound to a field in the table I use for tracking (Everything gets dumped into)

Private Sub btnwasher_Click()
'Call Generate_Number_Click

wastxt = "WA" & Generate_Number_Click

End Sub

Public Function Generate_Number_Click() As Long
On Error GoTo PROC_ERR
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strNextID As Long


'*** Find highest Serial No in the A0001_Serial_Num table and add 1 *******
strNextID = DMax("[washer_serial_num]", "washer_ser_num") + 1
'*** UPDATE SERIAL NO TABLE
Set rst = New ADODB.Recordset


strSQL = "SELECT washer_serial_num " & _
"FROM [washer_ser_num]"
rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText


With rst
.AddNew
![washer_serial_num] = strNextID
.update
.Close
End With

Set rst = Nothing


'*** Assign function the value of the Next # ******
Generate_Number_Click = strNextID

'Exit function now after successful incrementing or after error message
End Function

[RuralGuy]

It is of course your db but having a table full of just serial numbers does not make a lot of sense to me unless each record also contains useful fields that contain unique information about each unit.