I am trying to append records from one table to another and insert an increasing number to each record as it appends records. But it only returns the same value each time.
I have the following function:
Used in the following Queary:
It is like the function is only ever being run once and Access is using that one value for each record. I thought for every record it appended it would run the function wich would go out to that same table and find the last appended number and increase it by one and then append that and then reapeat.
I have the following function:
Code:
Function modNextPartNumber()
Dim strCustAbrev As String
Dim intIncrement As Integer
' P=Pattern
modNextPartNumber = Null
strCustAbrev = "VI"
intIncrement = Nz(DMax("Right([PatternNumber], 4)", _
"T_Patterns", _
"[PatternNumber] Like 'P" & strCustAbrev & "*'"), 0) + 1
'MsgBox "The next number is: P" & strCustAbrev & Format([intIncrement], "0000")
modNextPartNumber = "P" & strCustAbrev & Format([intIncrement], "0000")
'MsgBox modNextPartNumber
End Function
Used in the following Queary:
Code:
INSERT INTO T_Patterns ( PatDescp, PatGraphic, CustID, PatternNumber )
SELECT TblPattern.PatDescp, TblPattern.PatGraphic, TblPattern.CustID, modNextPartNumber() AS Expr1
FROM TblPattern;
It is like the function is only ever being run once and Access is using that one value for each record. I thought for every record it appended it would run the function wich would go out to that same table and find the last appended number and increase it by one and then append that and then reapeat.