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

Updating a Table from a form

P: 11
I have form in access what I am trying to do is search by a specific field and fill in most of the filed based on the search. Then update some other fields in that records based on the information that was populated from the exiting table.

I have an unbouded field called txtSearch. I want it to search the table in a field called [Appointment]. Once it locates the appointment field I want it to populate the other field in my form.

I have the following code but it errors out at [code] "Me.RecordsetClone.MoveFirst" [code]

Expand|Select|Wrap|Line Numbers
  3. Private Sub cmdSearch_Click()
  4. Dim bkmk As Variant
  5. Dim strField As String
  6. Me.RecordsetClone.MoveFirst
  7. 'Find the first record that matches what
  8. 'is in the search text box.
  9. Me.RecordsetClone.FindFirst "Appointment Like " _
  10. & Chr(34) & Me.txtSearch & "*" & Chr(34)
  12. If Me.RecordsetClone.NoMatch Then
  13. MsgBox "No Match"
  14. Else
  15. bkmk = Me.RecordsetClone.Bookmark
  16. Me.Recordset.Bookmark = bkmk
  17. End If
  18. End Sub
  22. Private Sub Form_Load()
  23. DoCmd.GoToRecord , "", acNewRec
  24. End Sub
  27. Private Sub Save_Driver_Info_Click()
  28. RetValue = MsgBox("Entry Saved. Thank you", vbOK)
  29. DoCmd.GoToRecord , "", acNewRec
  30. End Sub
It works perfectly find when I use it with another table.
Attached Files
File Type: docx Access Run-time Error.docx (444.4 KB, 219 views)
Mar 3 '15 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 2.5K+
P: 3,484

My initial thought on your error is that your form is not bound to any table, which results in your error, because there is no error for the Recordset to move to.

It is also better programming to declare a Variable to hold your recordsetclone and then navigate within that recordset:

Expand|Select|Wrap|Line Numbers
  1. Dim rstClone as DAO.Recordset
  2. Set rstClone = Me.RecordsetClone
  3. If Not rstClone.RecordCount = 0 Then
  4.     With rstClone
  5.         .MoveLast
  6.         .MoveFirst
  7.     End With
  8. End If
Of course, the above code doesn't "do" anything, but is merely provided as an example.

Additionally, in your Search (lines 9-10), you are better off using syntax like this:

Expand|Select|Wrap|Line Numbers
  1. Me.RecordsetClone.FindFirst "Appointment Like '" & _
  2.     Me.txtSearch & "*'"
Although a String is delimited by Double Quotes ("Text"), when using strings within your search criteria, as you have in your example, the single quotes are required.

Hope this hepps!
Mar 3 '15 #2

P: 11
I thank you for taking the time out to respond. It is connected a table. This same code works well in another table search without a problem. I am not sure if it is the field name or something specific to the field. I also appreciate your comment about the syntax. I do hope to see other response.
Mar 4 '15 #3

Expert Mod 2.5K+
P: 3,484
Have you tried this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.     Dim rstClone As DAO.Recordset
  3.     Dim strSearch As String
  5.     strSearch = "Appointment Like '" & Me.txtSearch & "*'"
  6.     Set rstClone = Me.RecordsetClone
  7.     rstClone.FindFirst strSearch
  9.     If rstClone.NoMatch Then
  10.         MsgBox "No Match"
  11.     Else
  12.         Me.Recordset.Bookmark = rstClone.Bookmark
  13.     End If
  14. End Sub
There is no need to Go to the first record, as the code will automatically find the first record that matches. This code works perfectly for me.
Mar 4 '15 #4

P: 208

Would it benefit the function at all by using this?
Expand|Select|Wrap|Line Numbers
  1. Me.txtSearch.Value
  2. 'vs
  3. Me.txtSearch
Mar 6 '15 #5

Expert Mod 2.5K+
P: 3,484

In VBA, the default property of a Control is ".Value", so in one sense, it is redundant. It also cuts down on the number of keystrokes, but sometimes it does not hurt to be explicit.

The only exception I have found is when using the TempVars() variables. When one assigns a value to the TempVars() variables using a control on a Form, one must explicitly use the .Value property or else (for whatever reason) the TempVars variable thinks you are trying to assign the Object itself to the variable, vice just assigning the variable (which is counter to every other usage within VBA.


Expand|Select|Wrap|Line Numbers
  1. TempVars("StringName") = Me.txtString
  2.     'MS Access want to assign the Control itself to TempVars("StringName")
  4. vs.
  6. TempVars("StringName") = Me.txtString.Value
  7.     'MS Access will assign the value of txtString
Hope this clarifies.
Mar 6 '15 #6

P: 208
It does indeed. Thanks for the clarification twinnyfo!
Mar 6 '15 #7

Post your reply

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