Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152

    Searching a Form

    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.

    Code:
    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.

    Code:
    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
    Last edited by GraemeG; 04-10-2011 at 11:06 AM.

  2. #2
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    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.

  3. #3
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Quote Originally Posted by kennejd View Post
    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.
    Code:
    Rs.FindFirst "[hsno] = """ & me.cbohsno & """ And "[address1] = """ & me.cboAddress1 & """

  4. #4
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    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)

  5. #5
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Quote Originally Posted by kennejd View Post
    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.

  6. #6
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    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.

  7. #7
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Quote Originally Posted by kennejd View Post
    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.

  8. #8
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Code:
    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.

  9. #9
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    I had an extra qoutation...sorry abou that. Try this:

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

  10. #10
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    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.

  11. #11
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Quote Originally Posted by kennejd View Post
    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?

  12. #12
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    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 & "'"

  13. #13
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Quote Originally Posted by kennejd View Post
    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

  14. #14
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    Does this work?

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

    Maybe we can isolate the code portion giving the problem.

  15. #15
    GraemeG is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2011
    Posts
    152
    Quote Originally Posted by kennejd View Post
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form Searching
    By Bike in forum Forms
    Replies: 2
    Last Post: 03-29-2011, 06:42 PM
  2. Replies: 3
    Last Post: 02-08-2011, 10:25 AM
  3. Replies: 1
    Last Post: 11-07-2010, 11:04 AM
  4. Searching in the form
    By seeter in forum Forms
    Replies: 11
    Last Post: 08-10-2010, 08:37 AM
  5. Replies: 0
    Last Post: 11-10-2009, 12:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums