Function in Append Queary (1 Viewer)

[mmitchell]

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:

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:

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.

 

[ajetrumpet]

so what does the function do? it looks like it's taking criteria from a table or something. if you need data in a table to start the part numbers going, why not just set a global var to that value (or more than one var) and then increment it everytime the function is run?? thus the function will read it in succession from record to record without needing a lookup everytime

 

[boblarson]

And just a note -

intIncrment should NOT have square brackets. It is a variable and not a field.

modNextPartNumber = "P" & strCustAbrev & Format(intIncrement, "0000")

 

[mmitchell]

I thought that I could use a function that would run at each specific record insert. The code works elsewhere on a form to generate the next sequentioal number for each customer. It thought I could use the same code in the queary and that it would go out and find the last number used then increment it and return the next on every record appended, but I guess I was wrong.That is why it only runs once.

Here is what I ended up using:

INSERT INTO T_Patterns ( PatDescp, PatGraphic, CustID, PatternNumber )
SELECT TblPattern.PatDescp, TblPattern.PatGraphic, 23 AS Expr1, "PVI" & Format([PatNumID],"0000") AS Exp2
FROM TblPattern;

I added an Autonumber field PatNumID to the table and then used it as the bases for the new numbers in the queary, it was a quick solution that I should have used first since this instance there was only one customer so I only had to generate incrementing numbers for one. But still not sure hwo to have done it if I had to do it for more then one and also if the numbers did not start at 1.

I guess I would do it in pure code using looping recordsets, but I shure thought I have run code in queries to produce different returns before!