Private Sub StoreType_AfterUpdate()
Dim GetStoreType As String
Dim GetStoreCode As String
Dim NextStoreNumber As String
Dim NextStoreCode As String
GetStoreType = Left([StoreType], 1)
GetStoreCode = NZ(DMax("StoreCode", "XOutlets", _
"StoreCode Like " & [StoreType] & "*"),GetStoreType & "000")
NextStoreNumber = GetStoreType & Format(Val(Mid(GetStoreCode,2))+1,"000")
Me.[StoreCode].Value = NextStoreCode
End Sub
So, you will have a problem when you reach "999" for any StoreCode.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi Karl, thank you for your prompt response.
I need some more help (I fear it's too late at night for me to think
further, lol)
I am perhaps trying to be a wee bit too fancy, here's my code to generate
the next Store Code number. I am trying to eliminate the problem where single
and double unit numbers are not zero-filled, so I need to put this in as my
StoreCode field has a length of 4 characters.
Private Sub StoreType_AfterUpdate()
Dim GetStoreType As String
Dim GetStoreCode As String
Dim NextStoreNumber As String
Dim NextStoreCode As String
GetStoreType = Left([StoreType],1)
GetStoreCode = Mid$(DMax("StoreCode", "XOutlets", "StoreType =" &
[StoreType]), 2, 3)
NextStoreNumber = GetStoreCode + 1
If Len(NextStoreNumber) = 1 Then
NextStoreCode = GetStoreType & "00" & NextStoreNumber
ElseIf Len(NextStoreNumber) = 2 Then
NextStoreCode = GetStoreType & "0" & NextStoreNumber
ElseIf Len(NextStoreNumber) = 3 Then
NextStoreCode = GetStoreType & NextStoreNumber
End If
[StoreCode].Value = NextStoreCode
End Sub
My StoreTypes are either S/M; G/T; T/SI; T/SR or W/S. But this is producing
an error saying "The expression you entered as a query parameter produced
this error: G"
This is when I select a StoreType as G/T; it changes with the selection of
any other store type to the first letter of the StoreType.
.