adding & losing records --code attached

[pattyt]

To add a record I use the following code which will go into a table "invnext" which has one field "helpno" .I increment this number by 1 and use that as my new helpno however I keep losing numbers. Does anybody find anything wrong with my code. I am on a file server with multiusers on the network. No BE or FE. Need Help with my code. Already checked archives for help! Will take all help I can get. thanks!

Private Sub AddBtn_Click()
On Error GoTo Err_AddBtn_Click

Dim dbs As Database
Dim rec As Recordset
Dim addDate
Dim addTime
Dim help_Num As Double
Dim intlockretry As Integer
Const Lock_Retry_Max = 5

intlockretry = 0
addDate = Date
addTime = Time

Set dbs = CurrentDb() ' Return reference to current database.
Set rec = dbs.OpenRecordset("INVNEXT", , dbDenyWrite) 'Open the Next Help Number table
'and place an exclusive lock on it
With rec
.EDIT

![NEXTHLPNO] = ![NEXTHLPNO] + 1 'Increment the Help Number

help_Num = rec![NEXTHLPNO] 'Assign new Help Number to a work variable
.UPDATE 'Save changes to record.
End With

rec.Close 'close the table
Set dbs = Nothing 'disassociate the reference

DoCmd.GoToRecord , , acNewRec 'Insert a new record

Me.HLPDATE = addDate
Me.HLPTIME = addTime
Me.HELPNO = help_Num 'Assign the new Help Number in the work variable to the form field
Me.ENTERBY.SetFocus

Exit_AddBtn_Click:
Exit Sub

Err_AddBtn_Click:
Select Case Err
Case 3186, 3260 'Record is locked so add 1 to counter
intlockretry = intlockretry + 1 'indicating how many times this has happened.
If intlockretry < Lock_Retry_Max Then 'Have you already retried too many times?
For i = 0 To intlockretry * 100 'If you haven't, then wait for a short period
Next i
Resume 'Now try again
Else
'But if you have already tried 5 times ask if user wants to retry.
Msg = "Unable to add a record at this time.@@Would you like to try again?" 'Define message.
Style = vbYesNo + vbQuestion 'Define buttons.
Title = "Confirm" 'Define title.
DoCmd.Beep
Response = MsgBox(Msg, Style, Title) 'Display message and Capture user response
If Response = vbYes Then 'If they say Yes then reset counter
intlockretry = 0 'resume execution at point of error
Resume
Else
'If they say No then exit routine
Msg = "No New Record Was Added.@@Press the OK button to continue." 'Define message.
Style = vbOKOnly + vbExclamation 'Define buttons.
Title = "No New Record Added" 'Define title.
DoCmd.Beep
Response = MsgBox(Msg, Style, Title) 'Display message and Capture user response
Resume Exit_AddBtn_Click
End If
End If

Case Else 'If any other error occurs
Msg = "An error has occurred. Please report the error to your program suport staffError #" & Err & "@" & Error ' Define message.
Style = vbOKOnly + vbExclamation 'Define buttons.
Title = "Error" 'Define title.
DoCmd.Beep
Response = MsgBox(Msg, Style, Title) 'Display message and Capture user response
End Select

Resume Exit_AddBtn_Click
End Sub

 

[chenn]

Just by looking at your code I can't see any problems. When you say 'losing records' does that mean that records are being deleted from your table?

This is probably too simple, but why not use an auto number field on the table where you are placing the help no?

Could try to take the variable assignment out of the edit/update area of the code....

Set rec = currentdb.OpenRecordset("INVNEXT", , dbDenyWrite) 'Open the Next Help Number table

With rec
.EDIT
![NEXTHLPNO] = ![NEXTHLPNO] + 1
.UPDATE

help_Num = ![NEXTHLPNO]
End With

With this you have to make sure that your table only has that one record in it.