Assigning Alphanumeric "Request ID" Based on Form Drop-down Selection

[GWhelan]

I've done a search of the forum, and while I've found similar issues to the one I'm having, I haven't found a solution in any of those that I've been able to apply to the database I'm working on. Hopefully someone will be able to provide some insight that I'm missing.

Overview:
I'm working on a database to help organize and streamline the process that the manufacturing facilities in my organization use to request removal of defective product and raw materials. Currently all of the facilities send me their requests on individual excel spreadsheets via email, which are difficult to keep track of and report on since nothing is consolidated. What I've done is create a form in access that mirrors the current spreadsheet being used, so all the facilities have to do is enter the info into the access form rather than excel. The use of access is more for my benefit than theirs though, as it will help me keep track of the requests as well as make reporting on the request data much simpler.

Problem:
When the facilities fill out the request form and select their facility number from a drop down list, I'm wanting them to be provided with a "Request ID" so that they may keep track of the progress of the request. The request ID should be a sequential alphanumeric value that includes the facility's three digit facility code, the last two digits of the year, and the number of the request. An example would be PL1-150001. The kicker here is that I want each facility to have it's own set of sequential numbers. So for example let's say I receive four requests in one week, three from facility 1 and one from facility 2. They should be assigned request ID's such as PL1-150001, PL1-150002, PL1-150003, and PL2-150001. I'm stuck, because I have no idea how to make this happen.

Any help on this issue would be greatly appreciated!

 

[pr2-eugin]

Hello GWhelan, Welcome to AWF

Have you looked into DMax + 1 Method? That offers a very similar solution to your question.

 

[GWhelan]

Thanks Paul! First time working with code here, so it's definitely been a learning experience.

I looked into the Dmax + 1 option, but I cannot get it to work. The unbound text box where this is supposed to be populated just remains blank. I'm not sure if it's an issue with the code, if the way my tables are set-up, or in my understanding of the way this function works. My code is below, and any insight would be appreciated.

Private Sub txtPlant_AfterUpdate()

If Len(Me!txtPlant) Then

Me!txtRequestNumber = DMax("[RequestNumber]", "tblRequestID", "[PlantCode] = '" & Me!txtPlant & "'") + 1

Else: MsgBox "A Plant Must be Selected", vbInformation, "Missing Information"
        Screen.PreviousControl.SetFocus
        Me.Undo
    End If

End Sub

 

[Solo712]

Thanks Paul! First time working with code here, so it's definitely been a learning experience.

I looked into the Dmax + 1 option, but I cannot get it to work. The unbound text box where this is supposed to be populated just remains blank. I'm not sure if it's an issue with the code, if the way my tables are set-up, or in my understanding of the way this function works. My code is below, and any insight would be appreciated.

Private Sub txtPlant_AfterUpdate()
 
If Len(Me!txtPlant) Then
 
Me!txtRequestNumber = DMax("[RequestNumber]", "tblRequestID", "[PlantCode] = '" & Me!txtPlant & "'") + 1
 
Else: MsgBox "A Plant Must be Selected", vbInformation, "Missing Information"
        Screen.PreviousControl.SetFocus
        Me.Undo
    End If
 
End Sub

Hi GWhelan,

You can't mix strings and numbers. Using DMax with strings can be very tricky.

The function will look something like this:

Function RequestNumber(PCode as String) as String
 
Dim SCStr as String, counter as Long
 
SCStr = Nz(Dmax("RequestNumber", "tblRequestID", "PlantCode='" & PCode & "'"))
 
If SCStr = "" Then 
    RequestNumber = Pcode & "-" & Right(Cstr(Year(Date),2) + "0001"
    Exit Function
ElseIf  Year(Date) <> Cint(Mid(SCStr, 5,2)) + 2000 Then 
    RequestNumber = Pcode & "-" & Right(Cstr(Year(Date),2)) + "0001"
    ExitFunction
endif
 
counter = Clng(Right(SCStr,4)) + 1
 
RequestNumber = Pcode & "-" & Mid(Cstr(Year(Date)),3,2)  & Right(Cstr(counter + 10000),4)
 
End Function

This is rough work and not tested code but in principle it should work.

Best,
Jiri

 

[pr2-eugin]

Would it be possible to post a stripped DB?

Please refer to How to Upload a Stripped DB.

 

[GWhelan]

Hi GWhelan,

You can't mix strings and numbers. Using DMax with strings can be very tricky.

The function will look something like this:

Function RequestNumber(PCode as String) as String
 
Dim SCStr as String, counter as Long
 
SCStr = Nz(Dmax("RequestNumber", "tblRequestID", "PlantCode='" & PCode & "'"))
 
If SCStr = "" Then 
    RequestNumber = Pcode & "-" & Right(Cstr(Year(Date),2) + "0001"
    Exit Function
ElseIf  Year(Date) <> Cint(Mid(SCStr, 5,2)) + 2000 Then 
    RequestNumber = Pcode & "-" & Right(Cstr(Year(Date),2)) + "0001"
    ExitFunction
endif
 
counter = Clng(Right(SCStr,4)) + 1
 
RequestNumber = Pcode & "-" & Mid(Cstr(Year(Date)),3,2)  & Right(Cstr(counter + 10000),4)
 
End Function

This is rough work and not tested code but in principle it should work.

Best,
Jiri

Thanks Jiri, I'll try playing around with that and see if it works.

Would it be possible to post a stripped DB?

Please refer to

Sure thing, see attached.

 

[Solo712]

I have tested it and found a couple of minor things. There was a duplicate in the function name , a bracket problem and 'Exit Function' was not separated. When you are sending the plant code, it has to be a three letter code. Here is the real deal :

Private Function GenRequestNumber(PCode As String) As String
 
Dim SCStr As String, counter As Long
 
SCStr = Nz(DMax("RequestNumber", "tblRequestID", "PlantCode='" & PCode & "'"))
 
If SCStr = "" Then
    GenRequestNumber = PCode & "-" & Right(CStr(Year(Date)), 2) + "0001"
    Exit Function
ElseIf Year(Date) <> CInt(Mid(SCStr, 5, 2)) + 2000 Then
    GenRequestNumber = PCode & "-" & Right(CStr(Year(Date)), 2) + "0001"
    Exit Function
End If
 
counter = CLng(Right(SCStr, 4)) + 1
 
GenRequestNumber = PCode & "-" & Mid(CStr(Year(Date)), 3, 2) & Right(CStr(counter + 10000), 4)
 
End Function

Best,
Jiri

 

[GWhelan]

I have tested it and found a couple of minor things. There was a duplicate in the function name , a bracket problem and 'Exit Function' was not separated. When you are sending the plant code, it has to be a three letter code. Here is the real deal :

Private Function GenRequestNumber(PCode As String) As String
 
Dim SCStr As String, counter As Long
 
SCStr = Nz(DMax("RequestNumber", "tblRequestID", "PlantCode='" & PCode & "'"))
 
If SCStr = "" Then
    GenRequestNumber = PCode & "-" & Right(CStr(Year(Date)), 2) + "0001"
    Exit Function
ElseIf Year(Date) <> CInt(Mid(SCStr, 5, 2)) + 2000 Then
    GenRequestNumber = PCode & "-" & Right(CStr(Year(Date)), 2) + "0001"
    Exit Function
End If
 
counter = CLng(Right(SCStr, 4)) + 1
 
GenRequestNumber = PCode & "-" & Mid(CStr(Year(Date)), 3, 2) & Right(CStr(counter + 10000), 4)
 
End Function

Best,
Jiri

Thanks Jiri. I hate to ask since it seems silly, but where would I put this code?

 

[Solo712]

Thanks Jiri. I hate to ask since it seems silly, but where would I put this code?

You are welcome. I fixed the code and inserted it based on the db you sent. Hope this works for you. You need to save the request ids when you save the request records.

Best,
Jiri

 

[GWhelan]

You are welcome. I fixed the code and inserted it based on the db you sent. Hope this works for you. You need to save the request ids when you save the request records.

Best,
Jiri

I greatly appreciate the help!

 

[Solo712]

No problemo !
Jiri