Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search Form (2003)

    Hi,

    I am working a search form of my database. There is a text field (Which named TxtLocation) and list box on the search form. The recordset of the list box is qry_searchChecks. I would like as user enter criteria in the text field, the list box shows every data that match. I have put the following codes on the change event of the TxtLocation:

    Dim strRowsource As String
    If IsNull(TxtLocation) = False Then
    strRowsource = "Select * From qry_SearchChecks Where [Deposit_Date] Like '*" & _
    Trim(TxtLocation) & "*' Or [Check#] Like '*" & Trim(TxtLocation) & _
    "*'"
    Dim db As Database, rs As Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strRowsource, dbOpenDynaset)
    Me.LstLocation.RowSource = strRowsource
    Me.LstLocation.SetFocus
    End If

    However, the list box doesn't changes when I enter the criteria in the TxtLocation.

    Please help.

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2003)

    1) There is no reason to define and open a recordset here. You don't do anything with it.

    2) If you really want to do this in the On Change event of the text box, you must look at the Text property of txtLocation. This property contains the text as it is being typed by the user.
    But if you want to set focus to the list box, I wouldn't use the On Change event, but the After Update event, with the code you have (minus the code for the recordset)

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2003)

    Thanks, Hans.

    I minus the recordset code and put it under After Update event, but still doesn't work. Whatever criteria I put in the text box, the list box won't change. Please see code below:

    Dim strRowsource As String
    If IsNull(TxtLocation) = False Then
    strRowsource = "Select * From qry_SearchChecks Where [Deposit_Date] Like '*" & _
    Trim(TxtLocation) & "*' Or [Check#] Like '*" & Trim(TxtLocation) & _
    "*'"
    Dim db As Database
    Set db = CurrentDb()
    Me.LstLocation.SetFocus

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2003)

    You should remove the code referring to db - like the recordset, it isn't used in any way.
    And you should restore the line that sets the RowSource of the list box:

    Me.LstLocation.RowSource = strRowsource

    Without it, your code doesn't do anything.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2003)

    Thank you so much, Hands. But I tried to remove the code referring to db and add the codes to set the RowSource of the list box, it still doesn't works.

    I have attached the database and maybe it will help you to find out what's wrong of my codes.

    Thank you so much for all of your helps.

    Regards
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2003)

    You have to tab out of the text box to make the list box update. See attached version (I removed the rest, it wasn't relevant to your question).
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2003)

    Thank you so much, Hans.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2003)

    Hi Hans,

    I add a text in the search form. If database doesn't have any data to match the criteria that Users search, the warning text will be appeared instead of list box. So I have edited the codes below:

    Dim strRowsource As String
    If IsNull(TxtLocation) = False Then
    strRowsource = "Select * From qry_SearchChecks Where [Deposit_Date] Like '*" & _
    Trim(TxtLocation) & "*' Or [Check#] Like '*" & Trim(TxtLocation) & "*'"

    End If

    Dim db As Database, rs As Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strRowsource, dbOpenDynaset)
    If rs.EOF Then
    LstLocation.Visible = False
    lblNoRecords.Visible = True
    lblNoRecords.Caption = "No records matching the criteria you chose, please try it again!"
    Me.TxtLocation.SetFocus

    Else
    LstLocation.Visible = True
    lblNoRecords.Visible = False
    Me.LstLocation.RowSource = strRowsource
    Me.LstLocation.SetFocus
    End If
    rs.Close
    Set rs = Nothing

    But as I test it and I get " Run-time error '3061', Too few parameters. Expected 2." error message.

    Please help.

    Thanks

    Regards

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2003)

    Please attach your database again.

  10. #10
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2003)

    Hi Hans,

    Please see the attached file.

    I don't know why sometime is fine without any error message. Could you help me to see if my codes is fine?

    Thanks

    Regards
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2003)

    I cannot reproduce the error you reported, but you should place the code involving the recordset inside the first If ... End If:
    <code>
    Private Sub TxtLocation_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strRowsource As String
    If IsNull(Me.TxtLocation) = False Then
    strRowsource = "Select * From qry_SearchChecks Where <!t>[Deposit_Date]<!/t> Like '*" & _
    Trim(Me.TxtLocation) & "*' Or <!t>[Check#]<!/t> Like '*" & Trim(Me.TxtLocation) & "*'"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strRowsource, dbOpenDynaset)
    If rs.EOF Then
    Me.LstLocation.Visible = False
    Me.lblNoRecords.Visible = True
    Me.lblNoRecords.Caption = _
    "No records matching the criteria you chose, please try it again!"
    Me.TxtLocation.SetFocus
    Else
    Me.LstLocation.Visible = True
    Me.lblNoRecords.Visible = False
    Me.LstLocation.RowSource = strRowsource
    Me.LstLocation.SetFocus
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    End If
    End Sub</code>

  12. #12
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2003)

    Thanks, Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •