J
jmengel
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
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