Selection criteria on parameter forms

  • Thread starter Thread starter amd via AccessMonster.com
  • Start date Start date
A

amd via AccessMonster.com

Hope someone can help me. . . .

I have set up a parameter form to select criteria from a combo box linked to
a table, and pass that criteria to a report.
The report is based on a query and I have used [Forms]![MyParameterForm]!
[MyTableField] coding in the query successfully.
Now I wish to add several other combo boxes, also linked back to other tables,
where the selection is optional' - that is, I can choose whether to enter
anything in those combo boxes or leave them blank, and if they are blank, the
report will return all records for that field (whilst still selecting records
from the other combo boxes that do have a selection).

I have been looking through the postings for about 2 hours and have printed
down various articles but I'm now a bit confused as to whether the coding
should be in the query or the form.
I have tried using = ([Forms]![MyParameterForm]![MyTableField] OR [Forms]!
[MyParameterForm]![MyTableField] IS NULL) in the query but that returned
nothing at all on my report and, when I went back to check it, found it was
gone from the query (I believe that Access does that sometimes when the
criteria is too complex).

I'm thinking there must be an easier way to do this by coding the fields on
the parameter form or in the report rather than in the query but I can't find
what is, to me, an explanation I can understand.

I'm not an Access expert but can usually manage to muddle through by
following threads and copying and pasting coding into visual basic and a lot
of what I read in the postings now makes sense to me (but not this time).

Could some kind person please explain what I need to do and precisely where I
need to do it.

Many Thanks
Alison
 
See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article explains how to build a filter for a form, but it's exactly the
same for a report.

It is possible to edit the WHERE clause of the query in SQL View for the
kind of thing you are trying to do, but it's less efficient. The article
above shows how to take that approach too if you really want to.
 
Hi Allen,

I'm working away this week and will not be able to get a chance to try this
out until the weekend but a HUGE THANK YOU - it looks to be exactly what I
have been seeking. I have looked through the coding and I'm fairly certain I
understand the principle and can apply it to my own database.

I will give it my best shot and let you know how I get on.

Thanks a million
Alison


Allen said:
See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article explains how to build a filter for a form, but it's exactly the
same for a report.

It is possible to edit the WHERE clause of the query in SQL View for the
kind of thing you are trying to do, but it's less efficient. The article
above shows how to take that approach too if you really want to.
Hope someone can help me. . . .
Click to expand...
[quoted text clipped - 40 lines]
Many Thanks
Alison
Click to expand...
Click to expand...
 
Hi Allen,

And it all looked so straighforward!! (sigh!).
I can't even get the query to run properly, let alone the report. I guess
it's best to start with the query and sort that out before tackling the
report.
However, I'm sure I have missed something simple so will try to explain what
I have done.

I have a 'ParameterForm' with 3 selection fields with the following code in
the 'on click' event of the form's Command Button

Private Sub Command2_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.CboCustomer) Then
strWhere = strWhere & "([LkUpCustomers] = " & Me.CboCustomer & ") AND
"
End If

If Not IsNull(Me.cboClient) Then
strWhere = strWhere & "([LkUpClients] = " & Me.cboClient & ") AND "
End If

If Not IsNull(Me.cboTown) Then
strWhere = strWhere & "([LkUpTown] = " & Me.cboTown & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

Me.Visible = False
End Sub

The user enters criteria in the fields in the Parameter Form using a drop-
down combo box where the row source is a table - all 3 comboboxes call data
from separate tables.

I then have a query which I hoped would return the data as input on the form
but as soon as I try to run the query I get a "Enter Parameter" message box
with Forms!ParameterForm!CboCustomer

Can you lead me out of the sticky stuff please?

Regards
Alison
Hi Allen,

I'm working away this week and will not be able to get a chance to try this
out until the weekend but a HUGE THANK YOU - it looks to be exactly what I
have been seeking. I have looked through the coding and I'm fairly certain I
understand the principle and can apply it to my own database.

I will give it my best shot and let you know how I get on.

Thanks a million
Alison
See:
Search form - Handle many optional criteria
Click to expand...
[quoted text clipped - 13 lines]
Click to expand...
 
Hi Allen,

And it all looked so straighforward!! (sigh!).
I can't even get the query to run properly, let alone the report. I guess
it's best to start with the query and sort that out before tackling the
report.
However, I'm sure I have missed something simple so will try to explain what
I have done.

I have a 'ParameterForm' with 3 selection fields with the following code in
the 'on click' event of the form's Command Button

Private Sub Command2_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.CboCustomer) Then
strWhere = strWhere & "([LkUpCustomers] = " & Me.CboCustomer & ") AND
"
End If

If Not IsNull(Me.cboClient) Then
strWhere = strWhere & "([LkUpClients] = " & Me.cboClient & ") AND "
End If

If Not IsNull(Me.cboTown) Then
strWhere = strWhere & "([LkUpTown] = " & Me.cboTown & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

Me.Visible = False
End Sub

The user enters criteria in the fields in the Parameter Form using a drop-
down combo box where the row source is a table - all 3 comboboxes call data
from separate tables.

I then have a query which I hoped would return the data as input on the form
but as soon as I try to run the query I get a "Enter Parameter" message box
with Forms!ParameterForm!CboCustomer

Can you lead me out of the sticky stuff please?

Regards
Alison
Hi Allen,

I'm working away this week and will not be able to get a chance to try this
out until the weekend but a HUGE THANK YOU - it looks to be exactly what I
have been seeking. I have looked through the coding and I'm fairly certain I
understand the principle and can apply it to my own database.

I will give it my best shot and let you know how I get on.

Thanks a million
Alison
See:
Search form - Handle many optional criteria
Click to expand...
[quoted text clipped - 13 lines]
Click to expand...
 
Work with the form first.

The search form is bound to a table/query.
The fields you wish to filter on need to be in that table/query.
Does your table have a field named LkUpCustomers?

You have a combo named CboCustomer on your form.
Is this an unbound combo (nothing in its ControlSource)?
Is its RowSource set up so that its Bound Column contains the data that will
match the LkUpCustomers field?

Temporarily comment out the other 2 If ... End If blocks, until you get the
first one working. They look right, but once you get one working you can
apply the same technique to get the others working.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

amd via AccessMonster.com said:
Hi Allen,

And it all looked so straighforward!! (sigh!).
I can't even get the query to run properly, let alone the report. I guess
it's best to start with the query and sort that out before tackling the
report.
However, I'm sure I have missed something simple so will try to explain
what
I have done.

I have a 'ParameterForm' with 3 selection fields with the following code
in
the 'on click' event of the form's Command Button

Private Sub Command2_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.CboCustomer) Then
strWhere = strWhere & "([LkUpCustomers] = " & Me.CboCustomer & ")
AND
"
End If

If Not IsNull(Me.cboClient) Then
strWhere = strWhere & "([LkUpClients] = " & Me.cboClient & ") AND "
End If

If Not IsNull(Me.cboTown) Then
strWhere = strWhere & "([LkUpTown] = " & Me.cboTown & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

Me.Visible = False
End Sub

The user enters criteria in the fields in the Parameter Form using a drop-
down combo box where the row source is a table - all 3 comboboxes call
data
from separate tables.

I then have a query which I hoped would return the data as input on the
form
but as soon as I try to run the query I get a "Enter Parameter" message
box
with Forms!ParameterForm!CboCustomer

Can you lead me out of the sticky stuff please?

Regards
Alison
Hi Allen,

I'm working away this week and will not be able to get a chance to try
this
out until the weekend but a HUGE THANK YOU - it looks to be exactly what I
have been seeking. I have looked through the coding and I'm fairly
certain I
understand the principle and can apply it to my own database.

I will give it my best shot and let you know how I get on.

Thanks a million
Alison
See:
Search form - Handle many optional criteria
Click to expand...
[quoted text clipped - 13 lines]
Many Thanks
Alison
Click to expand...
Click to expand...
Click to expand...
 
See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Download the application, and play with it.
See how the form works.
It applies a filter. There are no criterin the query.

When you follow how that works, you can do exactly the same thing by opening
the report form the form where you have the criteria boxes. You pass the
filter string as the WhereCondition for OpenReport. Details in the article.
 
Back
Top