Searching a Form

[GraemeG]

RESOLVED! Can't seem to change thread to resolved so If any admin comes across please change. Thanks



Hello.

I have the following code which I use one my forms to search and bring up a record. It works great. However it requires the user to know the actually unique property reference number to search.

Private Sub cmdSearchPropref_Click()
    Dim Rs As DAO.Recordset
    If Not IsNull(Me.cboMoveTo) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set Rs = Me.RecordsetClone
        Rs.FindFirst "[propref] = """ & Me.cboMoveTo & """"
        If Rs.NoMatch Then
            MsgBox "Record not found please check number and try again?", vbOKOnly, "Search Error"
        Else
            'Display the found record in the form.
            Me.Bookmark = Rs.Bookmark
        End If
        Set Rs = Nothing
    End If
End Sub

I was wondering if I could use two different fields hsno and address 1 for a search I.e. the user can enter a house number in the textbox search for hsno and then enter address 1 in textbox search for address1. To search any ideas much appreciated.

I thought maybe something like the following but it does not work.

Private Sub cmdSearchAddress_Click()
    Dim Rs As DAO.Recordset
    If Not IsNull(Me.cboMoveTo) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set Rs = Me.RecordsetClone
        Rs.FindFirst "[hsno] = """ And "[address1] = """ & Me.cboMoveTo & """"
        If Rs.NoMatch Then
            MsgBox "Record not found please Address and try again?", vbOKOnly, "Search Error"
        Else
            'Display the found record in the form.
            Me.Bookmark = Rs.Bookmark
        End If
        Set Rs = Nothing
    End If
End Sub

[kennejd]

It might work if you include the field that [hsno] is searching for... Like this:

Rs.FindFirst "[hsno] = "'" & Me.cboHouseNo & "' And "[address1] = "'" & Me.cboMoveTo & "'"

You may want to add some conditions when either or both combos have data selected.

[GraemeG]

It might work if you include the field that [hsno] is searching for... Like this:

Rs.FindFirst "[hsno] = "'" & Me.cboHouseNo & "' And "[address1] = "'" & Me.cboMoveTo & "'"

You may want to add some conditions when either or both combos have data selected.

Sorry the code was old. I have got the code looking at each of the comboboxes.
But it doesnt work saying mismatch type error.

Rs.FindFirst "[hsno] = """ & me.cbohsno & """ And "[address1] = """ & me.cboAddress1 & """

[kennejd]

I'm not sure how you set up your combo boxes....but they are often set up with a wizard and the actual value of the combo box is an id field. So you may need to either change the source of your combo boxes....or change your search string to look at the correct column. This is just conjecture on my part, but if you can post the sql for each combo box?

me.cbohsno.column(1)

[GraemeG]

I'm not sure how you set up your combo boxes....but they are often set up with a wizard and the actual value of the combo box is an id field. So you may need to either change the source of your combo boxes....or change your search string to look at the correct column. This is just conjecture on my part, but if you can post the sql for each combo box?

me.cbohsno.column(1)

I just inserted combointo form then used wizard to select what to look at.
All the hsno and addresses are in the combo box. but when i type a hsno number in and then a address 1 and hit search it give s mismatch error.

[kennejd]

I see an error in your code...looks like an extra quotation mark:
Rs.FindFirst "[hsno] = """ & me.cbohsno & """ And "[address1] = """ & me.cboAddress1 & """

I personally prefer single quotes when I set this up:
Rs.FindFirst "[hsno] = "'" & me.cbohsno & "'" And [address1] = "'" & me.cboAddress1 & "'"

If this doesn't work, it would be helpful to see the rowsource of your combo boxes.

[GraemeG]

I see an error in your code...looks like an extra quotation mark:
Rs.FindFirst "[hsno] = """ & me.cbohsno & """ And "[address1] = """ & me.cboAddress1 & """

I personally prefer single quotes when I set this up:
Rs.FindFirst "[hsno] = "'" & me.cbohsno & "'" And [address1] = "'" & me.cboAddress1 & "'"

If this doesn't work, it would be helpful to see the rowsource of your combo boxes.

Hsno row source: SELECT [Internal Survey Data].hsno FROM [Internal Survey Data];

Address 1 row source: SELECT [Internal Survey Data].address1 FROM [Internal Survey Data];

Also removing the quotations gives a santax error.

[GraemeG]

Private Sub cmdSearchAddress_Click()
    Dim Rs As DAO.Recordset
    If Not IsNull(Me.cbohsno) And Not IsNull(Me.cboAddress1) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set Rs = Me.RecordsetClone
        Rs.FindFirst "[hsno] = "" & Me.cbohsno & "" And [address1] = "" & Me.cboAddress1 & """
        If Rs.NoMatch Then
            MsgBox "Record not found please Address and try again?", vbOKOnly, "Search Error"
        Else
            'Display the found record in the form.
            Me.Bookmark = Rs.Bookmark
        End If
        Set Rs = Nothing
    End If
End Sub

This has got me a little further. No error. just msgbox saying address cannot be found. But I know the address is in there.

[kennejd]

I had an extra qoutation...sorry abou that. Try this:

Rs.FindFirst "[hsno] = "'" & me.cbohsno & "' And [address1] = "'" & me.cboAddress1 & "'"

[kennejd]

Is hsno a number field? If so, you'll want to remove the quote like this:

Rs.FindFirst "[hsno] = " & me.cbohsno & " And [address1] = "'" & me.cboAddress1 & "'"

If that doesn't work, try it in stages....first with the hsno.....then with the address1. See if you can pinpoint the problem.

[GraemeG]

Is hsno a number field? If so, you'll want to remove the quote like this:

Rs.FindFirst "[hsno] = " & me.cbohsno & " And [address1] = "'" & me.cboAddress1 & "'"

If that doesn't work, try it in stages....first with the hsno.....then with the address1. See if you can pinpoint the problem.

I do not understand the use of ' it just makes it a green code explanation?

[kennejd]

I'm going to have to start double-checking my syntax better in vba....I had an extra quote...try this:

rs.FindFirst "[hsno] = " & Me.cbohsno & " And [address1] = '" & Me.cboAddress1 & "'"

[GraemeG]

I'm going to have to start double-checking my syntax better in vba....I had an extra quote...try this:

rs.FindFirst "[hsno] = " & Me.cbohsno & " And [address1] = '" & Me.cboAddress1 & "'"

This gives a data type mismatch in criteria expression

[kennejd]

Does this work?

rs.FindFirst "[hsno] = " & Me.cbohsno

Maybe we can isolate the code portion giving the problem.

[GraemeG]

Does this work?

rs.FindFirst "[hsno] = " & Me.cbohsno

Maybe we can isolate the code portion giving the problem.

This gives the message box for when an address cant be found, however I know that the number in hsno is there.