By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,202 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

Issue with RecordsetClone and updating in Access 2003

P: 78
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:

Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  3.     If Not IsNull(Me.cboMoveTo) Then
  4.         'Save before move.
  5.         If Me.Dirty Then
  6.             Me.Dirty = False
  7.         End If
  8.         'Search in the clone set.
  9.         Set rs = Me.RecordsetClone
  10.         rs.FindFirst "[Reference #] = " & Me.cboMoveTo
  11.         If rs.NoMatch Then
  12.             MsgBox "Reference # not found. Please re-enter."
  13.         Else
  14.                     'Display the found record in the form.
  15.             Me.Bookmark = rs.Bookmark
  17.             Forms!Master!Frame147.Value = 4
  18.             Forms!Master!txtHidden.Value = "Locked"
  19.             Me.Text229 = ""
  20.             DoCmd.RunCommand acCmdSaveRecord
  21.         End If
  22.         End If
  25.         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:

Expand|Select|Wrap|Line Numbers
  1. 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???
Mar 31 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 5K+
P: 8,701
Try adding Line #9 and see what happens.
Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  3. If Not IsNull(Me.cboMoveTo) Then
  4.   'Save before move.
  5.   If Me.Dirty Then
  6.     Me.Dirty = False
  7.   End If
  9.   Me.Recordset.Requery           'ADD
  11.   'Search in the clone set.
  12.   Set rs = Me.RecordsetClone
  13.   rs.FindFirst "[Reference #] = " & Me.cboMoveTo
  14.     If rs.NoMatch Then
  15.       MsgBox "Reference # not found. Please re-enter."
  16.     Else
  17.       'Display the found record in the form.
  18.       Me.Bookmark = rs.Bookmark
  20.       Forms!Master!Frame147.Value = 4
  21.       Forms!Master!txtHidden.Value = "Locked"
  22.       Me.Text229 = ""
  23.       DoCmd.RunCommand acCmdSaveRecord
  24.     End If
  25. End If
  27. Set rs = Nothing
Mar 31 '10 #2

P: 78

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

Thanks again!
Mar 31 '10 #3

P: 78

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:
Expand|Select|Wrap|Line Numbers
  1. 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?
Apr 14 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.