Dynamically add a where clause to a query

[neemi]

I have an existing query which a continuous form is based on. On the form I have just added a combo box from which a user can select an agent number.

What I want to be able to do is use the agent number selected to add a where clause to the query from code etc.

For example if the recordsource of the continuous form is set to a query (qselDetails) that reads

Select * from tblMain (The real query is a lot more complicated!)

And then the user selects and agent from the combo box on the form, is there a way I can add this where clause to the query without created the query sql and using the query name and passing the where clause to it so it is something like

Select * from tblMain WHERE agentno = 720

queryDef?
query Filter?

Help appreciated.

 

[jaydeebetoo]

In your query you should set the criteria for the agentno field to be the combo box. Then use the combo's onchange event to refresh the continuous form.

formname.requery

 

[PHV]

You may also play with the Filter and FilterOn properties of the form in the AfterUpdate event procedure of the combo.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[neemi]

I need to also be able to have no filter, hence need to populate the where clause dynamically. if no selection is picked then there will be no where clause therefore cannot place the agentno criteria staight away.

Also the query is a load of summations and the agent number would just be a filter as apposed to a group by field.

Any idea's?

 

[Amiel]

Use the DataSource property of the ComboBox
as in


Dim strSQL as string
Dim strClause as string
strSQL = "SELECT field1, field2 FROM myTable "
strSQL = strSQL & " WHERE id = """ & ListBox1.Value & """"
' for strings


or


Dim strSQL as string
Dim strClause as string
strSQL = "SELECT field1, field2 FROM myTable "
strSQL = strSQL & " WHERE id = " & Listbox1.Value
' for numeric


finally


debug.print strSQL

<form>.DataSource = strSQL
<form>.refresh


Open debugger with Ctrl-G to view and check SQL string.
Strings should be wrapped in quotes, numeric values should not.


Amiel
amielzz@mp4.it

http://amiel.dl.am