Share via

MS Access 2016: Finding a record using FindFirst

Anonymous
2018-05-16T20:33:23+00:00

I have a combo box on a form in which I want to find a record.  I'm using the following code.  I have a Run Time error on line rsclone.FindFirst sCriteria.  I don't understand the syntax error.  Do I need to use an actual table instead of a query.  Thanks in advance.                                    

Dim rsClone As DAO.Recordset

Dim sCriteria As String

Const sSEARCHFIELD As String = "[CompanyName]"

Const sForm As String = "frmTemp"

Const txtSQL As String = "SELECT [qryJoinContactsandServices].[BDContacts].[CompanyName] FROM qryJoinContactsandServices ORDER BY [BDContacts].[CompanyName];"

Set rsClone = CurrentDb.OpenRecordset(txtSQL, dbOpenDynaset)

    If IsNull(Me.cboCompanyName.Value) Then

      MsgBox "List is null."

    End If

   If Not IsNull(Me.cboCompanyName.Value) Then

        sCriteria = sSEARCHFIELD & " = " & Me.cboCompanyName.Value

        Me.cboCompanyName = Me.cboCompanyName.ItemData(0)

        rsClone.FindFirst sCriteria

     End If

    If Not rsClone.NoMatch Then

        Me.Bookmark = rsClone.Bookmark

     End If

    rsClone.Close

    Set rsClone = Nothing

End If

End Sub

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-05-18T19:48:00+00:00

    HansV,

    Thank you for your reply.  You are right the CompanyName is a text field.  I changed the line, but I still get the Run Time 3077 Error:  Syntax error (missing operator) in expression.  What References should I have checked?  Thanks!

    Was this answer helpful?

    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2018-05-17T20:28:44+00:00

    Hi Linda,

    First your code is more complex than it needs to be. Access actually has a wizard that will create the type of combo you want. If you use the Combobox wizard and select the 3rd choice it will build the type of search combo you want.

    If you want to use your code, please tell us the EXACT error message you are getting. Also, what is the RowSource of the combobox.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-05-16T23:47:11+00:00

    To troubleshoot, can you copy your query text of "SELECT [qryJoinContactsandServices].[BDContacts].[CompanyName] FROM qryJoinContactsandServices ORDER BY [BDContacts].[CompanyName];"

    Into a blank query and see if it works as expected?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2018-05-16T21:53:27+00:00

    You don't need an SQL statement at all.  You can return a reference to a clone of the form's recordset by means of the RecordsetClone property.

    You'll find an example in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In the section on 'retrieving data from the database' in this little demo file you'll find a bound form in which a contact can be selected in an unbound combo box.  The code in the control's AfterUpdate event procedure is as follows:

        Dim ctrl As Control

        Set ctrl = Me.ActiveControl

        If ctrl = 0 Then

            DoCmd.GoToRecord record:=acNewRec

        Else

            ' find matching row in form's RecordsetClone

            With Me.RecordsetClone

                .FindFirst "ContactID = " & ctrl

                If Not .NoMatch Then

                    'move form to selected record

                    ' by synchronizing bookmarks

                    Me.Bookmark = .Bookmark

                End If

            End With

        End If

    The control's RowSource property is:

    SELECT 0 AS ContactID,0 AS SortColumn,NULL AS LastName,

    NULL AS FirstName,"<New Contact>"

    FROM Contacts

    UNION

    SELECT ContactID,1,LastName,FirstName, Lastname & ", " & FirstName

    FROM Contacts

    ORDER BY SortColumn, Lastname,FirstName;

    The first part of the UNION operation adds a <New Contact> item at the top of the combo box's list, to navigate to an empty new record.  Note that the combo box's BoundColumn property references the hidden ContactID column.  The concatenated Lastname & ", " & FirstName column is the visible column by virtue of the ColumnWidths property being set to 0cm;0cm;0cm;0cm;8cm to hide the first four columns.

    Was this answer helpful?

    0 comments
  5. HansV 462.6K Reputation points
    2018-05-16T21:24:16+00:00

    I assume that CompanyName is a text field, so you should enclose the value in single or double quotes:

            sCriteria = sSEARCHFIELD & " = '" & Me.cboCompanyName.Value & "'"

    Was this answer helpful?

    0 comments