Update Query Criteria using Form

[Allilue]

Hello,

Is it possible to create a form to select multiple criteria in a query? I want to be able to run many queries (from one query using different criteria) without having to save the query and display the results. And if this can be done, is it quite "simple"?

Help!

 

[KenReay]

Hi

Not sure I have understood you correctly, I think what you want is (Say your form is MyForm, with textBox MyTextBox),

in the query put Forms!MyForm!MyTextBox as the criteria

on the form put a button to run the query, in the OnClick event put:

If Len(Trim(Nz(MyTextBox,"&quot&"&quot) = 0 Then
MsgBox "Enter Criteria"
Else
DoCmd.OPenQuery "YourQueryName",...etc
End If

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Allilue]

Hi. Thanks for your response! I think what you're saying is what I want. So the first part of entering the criteria in the query Forms!MyForm!MyTextBox works.

However, I'm unsure of the other part. I am going to create a form that has about 10 combo boxes. Not all of them have to have a selection, but each combo box should be linked to the query and its corresponding criteria. If there is one selected (or more), than I want those selections to be entered into the query to show specific results. I'm not a great access user, so maybe I'm not getting your explanation. I tried it and then when I selected the combo box, then I got a message box asking me to Enter Criteria.

Let me know if I'm not clear.. I think I confused myself!! )

 

[Allilue]

this isn't as critical, but is it possible to add on the form a box to select/de-select the Show criteria of the query for various fields?

)

 

[KenReay]

Hi

If you want to have several combo boxes as Criteria, I would suggest two things

A) you have an <All> choice in the Combo box, this should be the default

B) you use LIKE Forms!MyForm!MyComboBox, as the criteria

You need to think about if the criteria are AND or OR



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Allilue]

Hi Ken,
You're right. I stumbled on this part...
I do need to have an ALL choice, but am unsure of how to do that.

If I have the Forms!frm_Criteria!MyComboBox in the criteria of the query for several fields (all with different combo boxes obviously) and need an ALL selection, how would I do that? For example, I have the following:

Table - Grouping (field 1); Selection (field 2)
ex. Grouping - NewClientModel
Selection - New, Existing, Other

So I have

NewClientModel New
NewClientModel Existing
NewClientModel Other

If I need an ALL, then would I put another record that had New or Existing or Other? (tried that, didn't work)

And all of criteria in the fields of the query should be AND.

I think I'll be fine once I figure out how to get an ALL selection...

Thanks!

 

[KenReay]

Hi

Probably the easiest way to add an <All> option to a combo box based on a table or query, is to use a UNION query. You cannot create a UNION query via the query design window, you need to do it in the SQL window.

Bring up your existing query in the design window, select the SQL View, say the existing query says something like:

SELECT lngId, strDescription FROM tblMyTable ORDER BY strDescription;

edit this to read

SELECT lngId, strDescription FROM tblMyTable
UNION
SELECT &quot;*&quot; As A, &quot;<All>&quot; As B FROM tblMyTable
ORDER BY strDescription;

To make the Combo box populate with the <All> option by default, in the on load event of the form put

If myCombo.ListCount > 0 Then
MyCombo = MyCombo.Column(0,0)
End If




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jsrothaus]

Here's what I think you are looking for, one at a time.
First, if you want to have a dynamic source (pulling from a table or query) but you want to have an &quot;all&quot; choice, try using an option group, 1 button &quot;all&quot;, one button &quot;Individual&quot; with a combo box of choices.
Or you can dynamically fill the combo box with the additem
method of the combo box. This is a lot cooler.
Example: lstPlan is a combo or list box.
Dim i As Integer
Dim rs As Recordset
Dim db As Database
Dim sql As String
Set db = CurrentDb
sql = &quot;SELECT tblPolicy.policyName FROM tblPolicy;&quot;
Set rs = db.OpenRecordset(&quot;tblPolicy&quot;, dbOpenSnapshot)
lstPlan.RowSource = &quot;&quot;
lstPlan.AddItem Item:=&quot;All&quot;, Index:=0
lstPlan.Selected(0) = True
rs.MoveFirst
Do While Not rs.EOF
If Not IsNull(rs!policyName) Then
i = i + 1
lstPlan.AddItem Item:=rs!policyName, Index:=i
End If
rs.MoveNext
Loop


Second issue is dynamically building a query.
This beats the snot out any other way of pulling info from a table or query (a Union query can be used as a source, it combines two tables, like &quot;tblInvoices union tblInvoices_old&quot.
Three examples show how to use a date, string or number:

dim sql as string
sql = &quot;SELECT tbldealer.* FROM tbldealer&quot;

if not isnull(me.textDate) then
sql = sql & &quot; WHERE tblDealer.signedUpDate < &quot; & &quot;#&quot; & Me.txtDate & & &quot;#&quot; '&quot;#&quot; is for dates
end if

if not comboDealerName = &quot;All&quot; then
sql = sql & &quot; AND tblDealer = &quot; & &quot;'&quot; & Me.comboDealerName & &quot;'&quot; ' &quot;'&quot; is for text
end if

if not isnull(me.textTotalSales) then
sql = sql & &quot; AND tblDealer.totalSales < &quot; cdbl(me.textTotalSales) 'no signs for numbers
end if

'last but not least the sql closing:
sql = sql & &quot;;&quot;

now the string should look like:
sql = &quot;SELECT tbldealer.* FROM tbldealer WHERE tblDealer.signedUpDate < #11/31/03# AND tblDealer = 'Joes Warehouse' tblDealer.totalSales < 4000 ;&quot;

the string is good for a combo or list box rowsource,
or a record source for a form or a report.

like maybe after this you might write:

DoCmd.OpenReport &quot;rptSalesReportDealer&quot;, acViewPreview, , sql

I hope this helps!
-Jerry