Desperate - Can't find specified record (1 Viewer)

I am using Access 2002 w/ win XP..I have a table called Calls..I Have it set to no locks...I have an add button with the following code. Please do not recommend using autonumber not an option at this time. I have multiusers all accessing the form. It is split database on a file server. If someone adds a call, it automatically puts my new help# and the current date and time immediately but I think it's preventing users from scrolling to another record..they keep getting can't go to specified record then later it will move to any record they want...please help me...maybe my code needs to somehow release the rest of the records..if a person does an add he can be on that record a minute or 5 minutes. I don't think the code is wrong about the part of using invnext cause it happens in a snap...I think it's in the last part..do I need to do a recordest close or a set db as nothing ..need help right away..indians on the warpath..I submitted this b4
Private Sub AddBtn_Click()
On Error GoTo Err_AddBtn_Click

Dim db As Database
Dim rec As Recordset
Dim addDate
Dim addTime
Dim help_Num As Double
Dim intlockretry As Integer
Const Lock_Retry_Max = 3

intlockretry = 0
addDate = Date
addTime = Time

Set db = CurrentDb() ' Return reference to current database.
Set rec = db.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 db = Nothing 'disassociate the reference

DoCmd.GoToRecord , , A_NEWREC '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
_____________________
it was suggested I do the following change:
with rec
.edit
![nexthlpno] = ![nexthelpno] + 1
.update
help_Num = ![nexthlpno]
_____________________________________
that didn't help it kept skipping helpno's every other one...Help!

Most likely a typo:

DoCmd.GoToRecord , , A_NEWREC 'Insert a new record

should read:

DoCmd.GoToRecord, , acNewRec 'Insert a new record

I found that out on the net or a bookand it said for access 2002 to use that
DoCmd.GoToRecord , , A_NEWREC
...I did originlly have
DoCmd.GoToRecord, , acNewRec 'Insert a new record
I still get the same error..please don't give up on me...help some more

Hello Pattyt,
I'm a newbie but how about "requery" since the record is saved and the multiple users want to access the record but possibly the "timing" is the issue. Just a thought. I had a similar issue. Good Luck!

Benny Wong

I don't know what your problem with autonumbers is but if it is a concern over gaps, your code does NOT solve the problem. The key assignment is attached to an Add button. It assigns a new key value and updates the key table and then creates a NEW record in the form's recordsource and then adds a couple of pieces of data but does NOT save the record. All the user has to do is to escape out of the update at this point and a gap will occur because the record for a key was not saved.

If you insist on rolling your own (so to speak), it is best to do it as the LAST thing prior to the record's being saved rather than before any data is entered. That means the code needs to be in the form's BeforeUpdate event and if it turns out that the record cannot be saved, you need to cancel the event. You should get the last used key, increment it, store it in the call record, save the call record, and only then save the key record. And to be very safe, you should wrap the whole process in a transaction so that the updates to both tables can be rolled back if necessary.