Append Query - Increment Number with Function?

I have a table where I use ClaimNumber as a key. I've avoided using the
AutoNumber feature, because it can't be adequately controlled, and I
have been able to get the numbering to work fine in a form. I have a
table that only has one purpose- to hold the last ClaimNumber used.
When the user double-clicks a text box, VBA code runs that looks in
table where the last number is stored, it increments the number, and
fills in the text box with the new ClaimNumber.

The problem starts when I try to add several new claims at once via an
append query. I moved the code to a public function, and it will run-
but every record in the append query ends up with the same ClaimNumber!
How can I get the records in the append query to each have their own
ClaimNumber?

Here is the function:
----------------------------------------------------------------

Public Function AssignClaimNum(Optional strClaimNum As String) As
String

On Error GoTo Err_AssignClaimNum

'This function generates a claim number.
'The value is pulled from tblClaimNumber, 1 is added to that to make
the NEW claim number, that new
'Claim number is then stored back in the tblClaimNumber so the next
claim number can be generated

Dim CLNumber As Recordset
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT * FROM tblClaimNumber"
Set CLNumber = dbs.OpenRecordset(strSQL)
With CLNumber
.MoveLast
.Edit
strClaimNum = ![ClaimNumber]
strClaimNum = strClaimNum + 1
![ClaimNumber] = strClaimNum
.Update
.Close
End With

'MsgBox strClaimNum

AssignClaimNum = strClaimNum


Exit_AssignClaimNum:
Exit Function

Err_AssignClaimNum:
MsgBox Err.Description
Resume Exit_AssignClaimNum
End Function
----------------------------------------------------------------

Any ideas?

Thanks.

jmengel

I think JET tries to optimise and calls the function only once and uses the
same return value for all Records.

Try to include an argument to the function (you don't actually have to use
the argument in the function) and when you use the function in the Append
Query, use a Field (preferably PK or uniquely indexed Field) from the
DataSource as the argument value in the function. I am fairly sure (but not
100%) that this will trick JET to call your function once for each row from
your DataSource.

--
HTH
Van T. Dinh
MVP (Access)



wrote in message
oups.com...
I have a table where I use ClaimNumber as a key. I've avoided using the
AutoNumber feature, because it can't be adequately controlled, and I
have been able to get the numbering to work fine in a form. I have a
table that only has one purpose- to hold the last ClaimNumber used.
When the user double-clicks a text box, VBA code runs that looks in
table where the last number is stored, it increments the number, and
fills in the text box with the new ClaimNumber.

The problem starts when I try to add several new claims at once via an
append query. I moved the code to a public function, and it will run-
but every record in the append query ends up with the same ClaimNumber!
How can I get the records in the append query to each have their own
ClaimNumber?

Here is the function:
----------------------------------------------------------------

Public Function AssignClaimNum(Optional strClaimNum As String) As
String

On Error GoTo Err_AssignClaimNum

'This function generates a claim number.
'The value is pulled from tblClaimNumber, 1 is added to that to make
the NEW claim number, that new
'Claim number is then stored back in the tblClaimNumber so the next
claim number can be generated

Dim CLNumber As Recordset
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT * FROM tblClaimNumber"
Set CLNumber = dbs.OpenRecordset(strSQL)
With CLNumber
.MoveLast
.Edit
strClaimNum = ![ClaimNumber]
strClaimNum = strClaimNum + 1
![ClaimNumber] = strClaimNum
.Update
.Close
End With

'MsgBox strClaimNum

AssignClaimNum = strClaimNum


Exit_AssignClaimNum:
Exit Function

Err_AssignClaimNum:
MsgBox Err.Description
Resume Exit_AssignClaimNum
End Function
----------------------------------------------------------------

Any ideas?

Thanks.

jmengel

Wow-you're good. :-)

All I had to do was call it like this: NewClaimNum:
AssignClaimNum([Policy])

instead of this: NewClaimNum: AssignClaimNum([Policy])

Thank you!

jmengel

The new call is exactly the same as the old call???

(copy & paste too quickly??? g)

Glad to help.

--
HTH
Van T. Dinh
MVP (Access)


wrote in message
oups.com...
Wow-you're good. :-)

All I had to do was call it like this: NewClaimNum:
AssignClaimNum([Policy])

instead of this: NewClaimNum: AssignClaimNum([Policy])

Thank you!

jmengel