Updating a Table from a form

[Darcjohnson01]

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]

 
 
Private Sub cmdSearch_Click()
Dim bkmk As Variant
Dim strField As String
Me.RecordsetClone.MoveFirst
'Find the first record that matches what
'is in the search text box.
Me.RecordsetClone.FindFirst "Appointment Like " _
& Chr(34) & Me.txtSearch & "*" & Chr(34)
 
If Me.RecordsetClone.NoMatch Then
MsgBox "No Match"
Else
bkmk = Me.RecordsetClone.Bookmark
Me.Recordset.Bookmark = bkmk
End If
End Sub
 
 
 
Private Sub Form_Load()
DoCmd.GoToRecord , "", acNewRec
End Sub
 
 
Private Sub Save_Driver_Info_Click()
RetValue = MsgBox("Entry Saved. Thank you", vbOK)
DoCmd.GoToRecord , "", acNewRec
End Sub
 
 
 

It works perfectly find when I use it with another table.

[twinnyfo]

Darcjohnson01,

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:

Dim rstClone as DAO.Recordset
Set rstClone = Me.RecordsetClone
If Not rstClone.RecordCount = 0 Then
    With rstClone
        .MoveLast
        .MoveFirst
    End With
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:

Me.RecordsetClone.FindFirst "Appointment Like '" & _
    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!

[Darcjohnson01]

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.

[twinnyfo]

Have you tried this:

Private Sub cmdSearch_Click()
    Dim rstClone As DAO.Recordset
    Dim strSearch As String
 
    strSearch = "Appointment Like '" & Me.txtSearch & "*'"
    Set rstClone = Me.RecordsetClone
    rstClone.FindFirst strSearch
 
    If rstClone.NoMatch Then
        MsgBox "No Match"
    Else
        Me.Recordset.Bookmark = rstClone.Bookmark
    End If
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.

[Brilstern]

twinnyfo,

Would it benefit the function at all by using this?

Me.txtSearch.Value
'vs
Me.txtSearch

[twinnyfo]

@Stevan,

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.

Example:

TempVars("StringName") = Me.txtString
    'MS Access want to assign the Control itself to TempVars("StringName")
 
vs.
 
TempVars("StringName") = Me.txtString.Value
    'MS Access will assign the value of txtString

Hope this clarifies.

[Brilstern]

It does indeed. Thanks for the clarification twinnyfo!