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

Making existing record active on a form

P: 25
I've created several linked tables to store information related to medical visits with the visit numbers as key fields. On the form I've created, the user can free-text in the visit number and the system performs a check on the database to see if it already exists (completed on the AfterUpdate Event). I've created a check field (varCount) to verify if the record already exists so I know it's finding the information, but it won't change focus to the existing record. Here's the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Account_AfterUpdate()
  2. Dim rst As Recordset
  3.     Set rst = Me.RecordsetClone
  4.     Me.varCount = DCount("Account", "PatientData", "Account = '" & Me.Account & "'")
  5.     If Me.varCount > 0 Then
  6.         rst.FindFirst "[Account] = '" & Me![Account] & "'"
  7.     End If
  8. End Sub
May 9 '12 #1
Share this Question
Share on Google+
10 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Thats because you are using the recordsetCLONE, a clone of the original recordset.

A few small modifications to your code is in order, since we are looking at it anyway:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Account_AfterUpdate()
  2.   'Attempt to navigate to record matching Account
  3.  
  4.   With Me.RecordsetClone
  5.     .FindFirst "[Account] = '" & Me![Account] & "'"
  6.     if .NoMatch Then
  7.       MsgBox "Account not found"
  8.     Else
  9.       rst.Bookmark=.bookmark
  10.     End If
  11.   End With
  12. End Sub
May 9 '12 #2

P: 25
Thanks for the reply Smiley! I tried applying the changes suggested, but the record doesn't display. I still get the indicator showing that there's an existing record, but the contents of the record do not show on the form. I made a few modifications to the code because it didn't recognize some of the objects.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Account_AfterUpdate()
  2.       'Attempt to navigate to record matching Account
  3.   Dim rst As DAO.Recordset
  4.   Set rst = Me.RecordsetClone
  5.   Me.varCount = DCount("Account", "PatientData", "Account = '" & Me.Account & "'")
  6.   With Me.RecordsetClone
  7.     .FindFirst "[Account] = '" & Me![Account] & "'"
  8.     If .NoMatch Then
  9.       MsgBox "Account not found"
  10.     Else
  11.       rst.Bookmark = .Bookmark
  12.     End If
  13.   End With
  14. End Sub
May 10 '12 #3

P: 25
Maybe if I clarify my intentions on this form it might be clearer. The database is to be used for data entry purposees, so users will be entering information directly from paper forms. On this particular form, they enter the patient identifiers and I have the system set to examine the table PatientData after the key field Account is entered to determine if it already exists. If it does, then the system should route to the existing record; if not, then it shouldn't do anything and allow the user to continue with the data entry on the new record. I think part of the problem is that I create a new record for the user to enter the identifier information, and when I try to redirect focus from this newly created record the system tries to save it and errors because it's creating a duplicate. Can focus be redirected without saving? It seems that the code above isn't working (as far as redirecting the form to the existing record) because the form indicates that it's remaining on the new record.
May 10 '12 #4

NeoPa
Expert Mod 15k+
P: 31,398
@MLEBL.
Please refer to Before Posting (VBA or SQL) Code.
May 10 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Sorry, My bad. I made a mistake near the end. Should be fixed now
Expand|Select|Wrap|Line Numbers
  1. Private Sub Account_AfterUpdate()
  2. 'Attempt to navigate to record matching Account
  3.  
  4.   With Me.RecordsetClone
  5.     .FindFirst "[Account] = '" & Me![Account] & "'"
  6.     if .NoMatch Then
  7.       MsgBox "Account not found"
  8.     Else
  9.       Me.recordset.Bookmark=.bookmark
  10.     End If
  11.   End With
  12. End Sub
May 10 '12 #6

P: 25
Now I'm getting a runtime error 3426 ("This action was cancelled by an associated object.") on
Expand|Select|Wrap|Line Numbers
  1. Me.Recordset.Bookmark = .Bookmark
May 10 '12 #7

P: 25
I've modified the structure of the forms so that it doesn't automatically create a new record when the form is opened. I'm pushing the values and evaluating the database before the record is created to determine if a value already exists and opening the form to the existing record or a blank record if it doesn't exist. Thanks for your assistance! I've actually used your code in the revised schema and it's working great! Thanks again!!!
May 10 '12 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
Without being 100% sure I would guess you were getting the error message because the textbox you are using as a searchbox is bound to a field. When creating a search textbox its important that you use an unbound field.
May 10 '12 #9

P: 25
It was bound so you're probably right.
May 10 '12 #10

NeoPa
Expert Mod 15k+
P: 31,398
Yes indeed. He was ;-)

You may find Example Filtering on a Form helpful.
May 10 '12 #11

Post your reply

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