Issue with RecordsetClone and updating in Access 2003

[WannabePrgmr]

I am working on a DB that 5 users input data coming in daily. The data is simple and is filled out in a form and submitted. If a user needs to reference a previously entered record (found by a reference number automatically generated for each call). I copied and pasted code early on in this project to accomplish the task of bringing up a record:

Dim rs As DAO.Recordset
 
    If Not IsNull(Me.cboMoveTo) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[Reference #] = " & Me.cboMoveTo
        If rs.NoMatch Then
            MsgBox "Reference # not found. Please re-enter."
        Else
                    'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
 
            Forms!Master!Frame147.Value = 4
            Forms!Master!txtHidden.Value = "Locked"
            Me.Text229 = ""
            DoCmd.RunCommand acCmdSaveRecord
        End If
        End If
 
 
        Set rs = Nothing

I added a few things I wanted to happen in there but here is my problem.

If one user is creating new records and say another user goes on break and comes back 20 minutes later and tries to call up one of the records that the user inputed, it comes up with "Reference # not found. Please re-enter.", which is the message I have for if the reference number is not found.

EVERYTHING about this code works excellent except this one small hickup! The only way I've found to solve it is to completely exit that form and reopen it, so it has to be some type of update issue.

I've tried:

DoCmd.RunCommand acCmdSaveRecord

I've also tried refresh and repaint code in the form and click events???

Nothing other than completely exiting and reopening has worked!

Any ideas???

[ADezii]

Try adding Line #9 and see what happens.

Dim rs As DAO.Recordset
 
If Not IsNull(Me.cboMoveTo) Then
  'Save before move.
  If Me.Dirty Then
    Me.Dirty = False
  End If
 
  Me.Recordset.Requery           'ADD
 
  'Search in the clone set.
  Set rs = Me.RecordsetClone
  rs.FindFirst "[Reference #] = " & Me.cboMoveTo
    If rs.NoMatch Then
      MsgBox "Reference # not found. Please re-enter."
    Else
      'Display the found record in the form.
      Me.Bookmark = rs.Bookmark
 
      Forms!Master!Frame147.Value = 4
      Forms!Master!txtHidden.Value = "Locked"
      Me.Text229 = ""
      DoCmd.RunCommand acCmdSaveRecord
    End If
End If
 
Set rs = Nothing

[WannabePrgmr]

Adezii,

Thanks for the reply! I will insert that immediately and let you know!

Thanks again!

[WannabePrgmr]

Adezii,

It definitely took care of that issue, but something else has come up as a result that doesn't happen all the time.

What happens, when it happens is that if you type in a reference number, it will bring up a different reference number (seems to be random) and an error comes up:

Run-Time error '3077':
Syntax error (missing operator) in expression

If I click on "Debug", it highlights the following line/code:

rs.FindFirst "[Reference #] = " & Me.cboMoveTo

If you go back and re-click enter with the same ref number, it will work perfect that time (the 2nd time).

Being that it doesn't happen all the time and it always works if you do it twice (after clicking "End" on the error popup) it sounds like a timing and/or multiple user issue???

Any ideas?