Display query in list box by using parameters from 2 text boxes

Question

All,

I am trying to create a search form to have users search for data using 2 parameters. I have a user form with a list box, three text boxes & a find button.

1st text box is txt_sname (Combobox)

2nd text box is txt_sdate

3rd text box is txt_sdate1

list box is lst_main

What I am trying to do here is have the user select a name from the combo box drop down & enter dates for example 12/11/2015 - 01/01/2016. Then I want the query to display in the list box.

The table I have set up which is "Main" has the following fields:

t_Name

t_Date

t_ContactID

t_Score

t_Comments

The query will look at t_Date and and t_Name, if the data matches the parameters from the text boxes it will then display the info on the list box. Can anyone point me in the right direction?

I am using the following to pass data to the table:

Private Sub c_Submit_Click()
Dim db As Database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from Main")

If IsNull(Me.txt_Name.Value) Or IsNull(Me.txt_Date.Value) Or           IsNull(Me.txt_Contact.Value) Then
MsgBox ("Error! Fill out all the fields!"), vbExclamation
Exit Sub
End If


rec.AddNew
rec("t_Name") = Me.txt_Name.Value
rec("t_Date") = Me.txt_Date
rec("t_ContactID") = Me.txt_Contact
rec("t_Score") = Me.txt_Score
rec("t_Comments") = Me.txt_Comments
rec.Update

Set rec = Nothing
Set db = Nothing

Me.txt_Name = Null
Me.txt_Date = Null
Me.txt_Contact = Null
Me.txt_Score = Null
Me.txt_Comments = Null

Me.Text32.Requery



MsgBox ("Record Added Successfully!")
End Sub

Thank you everyone for the help! Cheers!

Parfait · Accepted Answer · 2016-01-07 04:54:17Z

1

Listboxes have the RowSource property and so can use a table or query. Simply set the RowSource according to values from other textboxes:

Private Sub c_Submit_Click()

    Me.lst_main.RowSource = "SELECT t_Name, t_Date, t_ContactID, t_Score, t_Comments" _
                             & " FROM Main" _ 
                             & " WHERE t_Name = '" & Me.txt_sname & "'" _
                             & " AND t_Date >= #" & Me.txt_sdate & "#" _
                             & " AND t_Date <= #" & Me.txt_sdate1 & "#;"
    Me.lst_main.RowSourceType = "Table/Query"
    Me.lst_main.Requery

End Sub

answered Jan 7, 2016 at 4:54

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Display query in list box by using parameters from 2 text boxes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related