I use the below module for generating Invoice numbers on-the-fly. It works fine when using 1 column: PONo. It allocates a unique Invoice number per each unique PONo. Great.
Problem: I need to add 2 more columns [ShipDate],[WhsID] to the criteria so that it generates a unique invoice number per each ShipDate, WhsID, PONo. I may have to ship PO's at different times from different warehouses. When I try to add the fields, I get error: The expression you entered has a function containing the wrong number of arguments. What needs to be changed in the below module to accommodate more columns?
Code:
InvNo: RowNumber([PONo]) - works great.
InvNo: RowNumber([ShipDate],[WhsID],[PONo]) - I need to add columns [ShipDate],[WhsID] to the criteria.
Code:
Option Compare Database
Private lngRowNumber As Long
Private colPrimaryKeys As VBA.Collection
Public Function ResetRowNumber() As Boolean
Set colPrimaryKeys = New VBA.Collection
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varInvNo As String
Dim MaxQuery As String
'Specify which query will be the source for varInvNo
MaxQuery = "qry_TempBatchInvNo_Max"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT Distinct [InvNo] FROM " & MaxQuery, dbOpenSnapshot)
varInvNo = rs("InvNo")
'Setup the value for variable lngRowNumber
lngRowNumber = varInvNo
ResetRowNumber = True
End Function
Public Function RowNumber(UniqueKeyVariant As Variant) As Long
Dim lngTemp As Long
On Error Resume Next
lngTemp = colPrimaryKeys(CStr(UniqueKeyVariant))
If Err.Number Then
lngRowNumber = lngRowNumber + 1
colPrimaryKeys.Add lngRowNumber, CStr(UniqueKeyVariant)
lngTemp = lngRowNumber
End If
RowNumber = lngTemp
End Function
Thank you!