I have two combo boxes tied to tables Language and Category to look up lID and cID
I want the format of the serial number to look like this: EG.FF.XX-nnnn . I have two letters associated to identify each item's Language and two letters to indentify its category. These two items come from tables Language and Category. In this sample, I can tell that the item belongs to English and it’s a Feature Film.
XX refers to the first two letters of the Title of the Item and nnnn refers to the actual ID of the item.
So for Guess Who’s Coming to Dinner, I will have: EG.FF.GU-0827 I would know that this item is #827 in my DB.
I was able to work out part of the automation, i.e., EG.FF. But I don’t know how to work out the remainder of the part. Also, the serial number (last four digits) are coming out ast 0001 for all new items added. I want it to pick it up from the actual PK.
Can someone please look at my Codes and help me out? Please be specific to mention about what to insert and where, if possible. I am, indeed a novice.
Thanks and regards.
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Current()
- If IsNull(Me![ProgramID]) Then
- DoCmd.GoToControl "iTitle"
- End If
- Me![sfItemsJoin Label].Caption = "Casting: '" & [iTitle] & "'"
- With Me
- If .NewRecord Then Call .iTitle.SetFocus
- .lID.Locked = Not .NewRecord
- .cID.Locked = Not .NewRecord
- End With
- End Sub
- Private Function GetCDKey() As String
- Dim StrLng As String, StrCat As String
- Dim intVal As Integer
- GetCDKey = ""
- If IsNull(Me.lID) _
- Or IsNull(Me.cID) Then Exit Function
- StrLng = Me.lID.Column(2)
- StrCat = Me.cID.Column(2)
- GetCDKey = "%L.%C.-%4"
- GetCDKey = Replace(GetCDKey, "%L", StrLng)
- GetCDKey = Replace(GetCDKey, "%C", StrCat)
- StrCat = StrLng & "." & StrCat
- intVal = Val(Nz(DMax(Expr:="Mid([iNo],8,2)", _
- Domain:="[Items]", _
- Criteria:="[iNo] Like " & _
- "'" & StrCat & ".*'"), "0"))
- GetCDKey = Replace(GetCDKey, "%4", Fmt(intVal + 1, 4))
- End Function
- Private Function Fmt(intVal As Integer, intDigits As String) As String
- Fmt = Right(10000 + intVal, intDigits)
- End Function
- Private Sub cID_AfterUpdate()
- Me.iNo = GetCDKey()
- End Sub