DoCmd.FindRecord not working

[lmcc007]

I created a command button that will perform a search on the text selected in a combo box, but it is not working. I'm trying to answer the below questions:

Find What: cboTypeofAddressID (the text from the Combo Box)

Look in: sfrmAddress

Match: Whole Field

Search: All

Match Case and Search Fields As Formatted
​

Below is the code I typed:

Private Sub cmdAddType_Click()
DoCmd.FindRecord "Me.cboTypeofAddressID", , True, , True
End Sub
​

Any suggestions on how to get this to work?

Thanks!

 

[Uncle Gizmo]

If you replace "Me.cboTypeofAddressID" with a known value does it work?

 

[lmcc007]

If you replace "Me.cboTypeofAddressID" with a known value does it work?

Yes, it does.

 

[Uncle Gizmo]

Private Sub cmdAddType_Click()
'comment out the line below as shown and add the message box line
'DoCmd.FindRecord "Me.cboTypeofAddressID", , True, , True
MsgBox " >>> " & Me.cboTypeofAddressID
End Sub

what do you get...?

And I think I see the problem,,, "Me.cboTypeofAddressID" should not be in quotes...
Try removing them

 

[lmcc007]

I rewrote it, hopes this is better.​

My form has a combo box called cboTypeofAddressID (Row Source = SELECT tlkpTypeofAddresses.TypeofAddressID, tlkpTypeofAddresses.TypeofAddress FROM tlkpTypeofAddresses ORDER BY tlkpTypeofAddresses.TypeofAddress​

Then I have an unbound combo box named cboAddType (Row Source = SELECT tlkpTypeofAddresses.TypeofAddressID, tlkpTypeofAddresses.TypeofAddress FROM tlkpTypeofAddresses ORDER BY tlkpTypeofAddresses.TypeofAddress

I created a command button named cmdAddType, which is a search button. I am trying to write code to do the following when the cmdAddType is clicked:


Find What: cboAddType (the text from the unbound combo box)

Look in: sfrmAddress.cboTypeofAddressID

Match: Whole Field

Search: All (from top to bottom each time)



I have tried many different codes (DLookup, Select Case…) and it didn’t work, so I decided the Find Method may be what I need. Below is the code I typed:


Private Sub cmdAddType_Click()

DoCmd.FindRecord Me.cboAddTypeID, , True, , True


End Sub


It doesn’t nothing. I have added fields to it and so on but I get nothing. Any suggestions on how to get this Find method to work?

Thanks!

 

[boblarson]

Stop putting the combo box in quotes. It doesn't go in quotes.

DoCmd.FindRecord "Me.cboAddType", , True, , True


should be

DoCmd.FindRecord Me.cboAddType, , True, , True

or perhaps

DoCmd.FindRecord Chr(34) & Me.cboAddType & Chr(34), , True, , True

 

[lmcc007]

Stop putting the combo box in quotes. It doesn't go in quotes.

DoCmd.FindRecord "Me.cboAddType", , True, , True


should be

DoCmd.FindRecord Me.cboAddType, , True, , True

or perhaps

DoCmd.FindRecord Chr(34) & Me.cboAddType & Chr(34), , True, , True

It's not in quotes. Here it is:

DoCmd.FindRecord Me.cboAddTypeID, , True, , True

 

[boblarson]

It's not in quotes. Here it is:

DoCmd.FindRecord Me.cboAddTypeID, , True, , True

Perhaps you should go to this instead:

    Dim rs As DAO.Recordset

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[TypeOfAddressID] = " & Me![cboAddTypeID]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[lmcc007]

Perhaps you should go to this instead:

    Dim rs As DAO.Recordset

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[TypeOfAddressID] = " & Me![cboAddTypeID]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Wonderful! That's it! Thank a lot!

 

[boblarson]

Wonderful! That's it! Thank a lot!

Sure thing, glad to be able to help

 

[lmcc007]

Perhaps you should go to this instead:

    Dim rs As DAO.Recordset

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[TypeOfAddressID] = " & Me![cboAddTypeID]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

One problem, when I select data from cboAddType and it is not in cboTypeofAddressID, it displays the first record instead of saying something like: The search item was not found.

How do I add when not found to display "The search item was not found"?

 

[boblarson]

One problem, when I select data from cboAddType and it is not in cboTypeofAddressID, it displays the first record instead of saying something like: The search item was not found.

How do I add when not found to display "The search item was not found"?

Try changing this line

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

to this:

If rs.NoMatch Then 
   MsgBox "The search item was not found"
Else
   If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If

 

[lmcc007]

Try changing this line

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

to this:

If rs.NoMatch Then 
   MsgBox "The search item was not found"
Else
   If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If

That did it! Gosh, I've been working on this Find Method for two days. Thanks!!!