Querying from multiple form parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a query that I think is a little above my head... I
have a form that I have created with close to 10 combo boxes on it that pulls
information from one specific query. I want the user to choose the
parameters that he/she wants from the form (using all or only a few of the
combo boxes) and then those are used to generate another query. I have the
query set up with the criteria pulling from the form ([Form]![Focused
Worksheet Selection Form]![cmbSLName]), my problem is that I do not know how
to make it so that the combo boxes can also be left blank. I have tried
putting OR IS NULL in each of the criteria fields after the form criteria (I
read that from another post) but when I run the form, I still get parameters
prompts. Can somebody help me with this? What am I doing wrong?
Thanks in advance!
Kelly
 
Are you using the query as the record source for a form or report? If so, I
would remove all of your "form!control" references from your query and use
code to build a where clause.
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.cmbSLName) Then
strWhere = strWhere & " And [SLName] = """ & _
Me.cmbSLName & """"
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND HireDate >=#" & _
Me.txtStartDate & "# "
End If
'other control code
DoCmd.OpenForm "frmYourForm", , , strWhere
 
I'm not sure I understand this (I also don't think I'm this advanced!). Do I
create this code for each control? Where do I do this? Sorry - I haven't
done this before.
Thanks again.

Duane Hookom said:
Are you using the query as the record source for a form or report? If so, I
would remove all of your "form!control" references from your query and use
code to build a where clause.
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.cmbSLName) Then
strWhere = strWhere & " And [SLName] = """ & _
Me.cmbSLName & """"
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND HireDate >=#" & _
Me.txtStartDate & "# "
End If
'other control code
DoCmd.OpenForm "frmYourForm", , , strWhere

--
Duane Hookom
MS Access MVP


Kelly said:
I am trying to create a query that I think is a little above my head... I
have a form that I have created with close to 10 combo boxes on it that
pulls
information from one specific query. I want the user to choose the
parameters that he/she wants from the form (using all or only a few of the
combo boxes) and then those are used to generate another query. I have
the
query set up with the criteria pulling from the form ([Form]![Focused
Worksheet Selection Form]![cmbSLName]), my problem is that I do not know
how
to make it so that the combo boxes can also be left blank. I have tried
putting OR IS NULL in each of the criteria fields after the form criteria
(I
read that from another post) but when I run the form, I still get
parameters
prompts. Can somebody help me with this? What am I doing wrong?
Thanks in advance!
Kelly
Click to expand...
Click to expand...
 
Each control would create a section of code like
If IsNull(...) Then
'
End If
This code might be run when the user clicks a button to open the report.

--
Duane Hookom
MS Access MVP


Kelly said:
I'm not sure I understand this (I also don't think I'm this advanced!).
Do I
create this code for each control? Where do I do this? Sorry - I haven't
done this before.
Thanks again.

Duane Hookom said:
Are you using the query as the record source for a form or report? If so,
I
would remove all of your "form!control" references from your query and
use
code to build a where clause.
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.cmbSLName) Then
strWhere = strWhere & " And [SLName] = """ & _
Me.cmbSLName & """"
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND HireDate >=#" & _
Me.txtStartDate & "# "
End If
'other control code
DoCmd.OpenForm "frmYourForm", , , strWhere

--
Duane Hookom
MS Access MVP


Kelly said:
I am trying to create a query that I think is a little above my head...
I
have a form that I have created with close to 10 combo boxes on it that
pulls
information from one specific query. I want the user to choose the
parameters that he/she wants from the form (using all or only a few of
the
combo boxes) and then those are used to generate another query. I have
the
query set up with the criteria pulling from the form ([Form]![Focused
Worksheet Selection Form]![cmbSLName]), my problem is that I do not
know
how
to make it so that the combo boxes can also be left blank. I have
tried
putting OR IS NULL in each of the criteria fields after the form
criteria
(I
read that from another post) but when I run the form, I still get
parameters
prompts. Can somebody help me with this? What am I doing wrong?
Thanks in advance!
Kelly
Click to expand...
Click to expand...
Click to expand...
 
I'm sorry, I appreciate your help and hope that you will be patient with me :)
I guess I am not familiar enough with Access to understand how to do this.
Where do I write this code? Do I write it under the form properties? How do
I get to the place to write the code? Do I write it for each control (it
loks like you have written it for two below) or do I write it in one place as
a continuous list? Where do I put the DoCmd.openform text? Does that follow
all of the control commands or does it get written in a separate area?
Thanks,
Kelly

Duane Hookom said:
Each control would create a section of code like
If IsNull(...) Then
'
End If
This code might be run when the user clicks a button to open the report.

--
Duane Hookom
MS Access MVP


Kelly said:
I'm not sure I understand this (I also don't think I'm this advanced!).
Do I
create this code for each control? Where do I do this? Sorry - I haven't
done this before.
Thanks again.

Duane Hookom said:
Are you using the query as the record source for a form or report? If so,
I
would remove all of your "form!control" references from your query and
use
code to build a where clause.
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.cmbSLName) Then
strWhere = strWhere & " And [SLName] = """ & _
Me.cmbSLName & """"
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND HireDate >=#" & _
Me.txtStartDate & "# "
End If
'other control code
DoCmd.OpenForm "frmYourForm", , , strWhere

--
Duane Hookom
MS Access MVP


I am trying to create a query that I think is a little above my head...
I
have a form that I have created with close to 10 combo boxes on it that
pulls
information from one specific query. I want the user to choose the
parameters that he/she wants from the form (using all or only a few of
the
combo boxes) and then those are used to generate another query. I have
the
query set up with the criteria pulling from the form ([Form]![Focused
Worksheet Selection Form]![cmbSLName]), my problem is that I do not
know
how
to make it so that the combo boxes can also be left blank. I have
tried
putting OR IS NULL in each of the criteria fields after the form
criteria
(I
read that from another post) but when I run the form, I still get
parameters
prompts. Can somebody help me with this? What am I doing wrong?
Thanks in advance!
Kelly
Click to expand...
Click to expand...
Click to expand...
 
When you use the wizard to add a command button that opens a form or report,
code will be created in the form's module. You get to the code window by
finding the On Click event property of the command button and clicking on
the [...] button.

--
Duane Hookom
MS Access MVP


Kelly said:
I'm sorry, I appreciate your help and hope that you will be patient with
me :)
I guess I am not familiar enough with Access to understand how to do this.
Where do I write this code? Do I write it under the form properties? How
do
I get to the place to write the code? Do I write it for each control (it
loks like you have written it for two below) or do I write it in one place
as
a continuous list? Where do I put the DoCmd.openform text? Does that
follow
all of the control commands or does it get written in a separate area?
Thanks,
Kelly

Duane Hookom said:
Each control would create a section of code like
If IsNull(...) Then
'
End If
This code might be run when the user clicks a button to open the report.

--
Duane Hookom
MS Access MVP


Kelly said:
I'm not sure I understand this (I also don't think I'm this advanced!).
Do I
create this code for each control? Where do I do this? Sorry - I
haven't
done this before.
Thanks again.

:

Are you using the query as the record source for a form or report? If
so,
I
would remove all of your "form!control" references from your query and
use
code to build a where clause.
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.cmbSLName) Then
strWhere = strWhere & " And [SLName] = """ & _
Me.cmbSLName & """"
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND HireDate >=#" & _
Me.txtStartDate & "# "
End If
'other control code
DoCmd.OpenForm "frmYourForm", , , strWhere

--
Duane Hookom
MS Access MVP


I am trying to create a query that I think is a little above my
head...
I
have a form that I have created with close to 10 combo boxes on it
that
pulls
information from one specific query. I want the user to choose the
parameters that he/she wants from the form (using all or only a few
of
the
combo boxes) and then those are used to generate another query. I
have
the
query set up with the criteria pulling from the form
([Form]![Focused
Worksheet Selection Form]![cmbSLName]), my problem is that I do not
know
how
to make it so that the combo boxes can also be left blank. I have
tried
putting OR IS NULL in each of the criteria fields after the form
criteria
(I
read that from another post) but when I run the form, I still get
parameters
prompts. Can somebody help me with this? What am I doing wrong?
Thanks in advance!
Kelly
Click to expand...
Click to expand...
Click to expand...
 
Back
Top