Basic search form

[robertoathome]

Hello, I successully adapted a search form from a microsoft example into my own db.

MS Example

I type search parameters in 2 boxes and the results are returned in a new, basic query window.

I created:
2 text boxes
1 Command button ( search )
1 Macro to open the query
1 query

All works fine but there are 2 problems I can't resolve.

1) The users can mis-type the parameters and think there are no records. So I need to create a pull down list possibly combo box.
2) The results are returned in a plain query table. I need to direct them into a subform.

I tried many things; linking the texboxes to table/fields, raw data, expression building as well as using a combo box. I also changed the query's criteria to reflect the combo box.
Nothing works. It acts like the combo box isn't there at all.

I created a subform but I don't know how to send the resuls in it.
I also got the following suggestion which I don't understand.
"In the After Update event of either or both combo boxes, requery the subform. "
How do I "requery a subform" ?
Any suggestion? When giving details please keep in mind I'm very new at this .
Thanks!

[Corster]

For your combo box, you'll need to set the following properties:

Private Sub cbxControlName_Click()
    cbxControlName.RowSource = "SELECT DISTINCT Table.Column FROM Table ORDER BY Table.Column"
End Sub

As for requerying a subform, try:

Me.SubFormName.Requery

Depending on which point you wish to requery, you will have to place your code in the control's Sub that you're changing to force the requery. If it's upon navigating records, you will have to put it in your Form_Current Subroutine.

As for the other part, I'm afraid I will not be able to assist...

[robertoathome]

For your combo box, you'll need to set the following properties:

Private Sub cbxControlName_Click()
    cbxControlName.RowSource = "SELECT DISTINCT Table.Column FROM Table ORDER BY Table.Column"
End Sub

As for requerying a subform, try:

Me.SubFormName.Requery

Depending on which point you wish to requery, you will have to place your code in the control's Sub that you're changing to force the requery. If it's upon navigating records, you will have to put it in your Form_Current Subroutine.

As for the other part, I'm afraid I will not be able to assist...

1) For the combo box I believe you wanted me to put the code in the OnClick property. I replaced all the instances where you had "Table" with the name of my table:

Private Sub Combo20_Click()
    Combo20.RowSource = "SELECT DISTINCT table1.Column FROM table1 ORDER BY table1.Column"
End Sub
 

I'm afraid it didn't change the results.
How should I modify the Control Source and Raw Source properties? Or should I touch em at all?

2)
For the subform issue.
I want the data to go in the subform when I press the search button so I put the
Me.QBF_Query_Subform.Requery
in the OnClick event of the button. I also tried to put it in the BeforeUpdate Sub. Now I keep getting the following:
"You didn't specify search criteria with a FindRecornd action."

I'm clearly doing something wrong.
Thanks!

[Corster]

1) ...I replaced all the instances where you had "Table" with the name of my table:

Private Sub Combo20_Click()
    Combo20.RowSource = "SELECT DISTINCT table1.Column FROM table1 ORDER BY table1.Column"
End Sub
 

I'm afraid it didn't change the results.
How should I modify the Control Source and Raw Source properties? Or should I touch em at all?

It is always best practise to name your controls accordingly; something relating to what type it is and what it does - like cbx for ComboBoX and SrchList for Search List. You will need to change the column name aswell - the things you should be changing have been placed within parentheses: (These things.()()())

Private Sub (ControlName)_(EventProcedure)
    (ControlName).RowSource = "SELECT DISTINCT (TableName).(ColumnName) FROM (TableName) ORDER BY (TableName).(ColumnName)"
 

2)
For the subform issue.
I want the data to go in the subform when I press the search button so I put the
Me.QBF_Query_Subform.Requery
in the OnClick event of the button. I also tried to put it in the BeforeUpdate Sub. Now I keep getting the following:
"You didn't specify search criteria with a FindRecornd action."

I'm clearly doing something wrong.
Thanks!

Yeah, I've tried making a search tool and I didn't get anywhere, I was trying to use FindFirst after setting focus on my txtSearch control. There may be another post here somewhere that might help with that.