Duane,
I have the sam problem like Kelly. multiple parameters from the combo box
(about 10 parameter) and user want to be able to choose the fields
dynamically. I've followed your instrutions and able to build the sql the
way I want it like below example but in the form "frmYourForm", , ,
strWhere
"DoCmd.OpenForm "frmYourForm", , , strWhere"
What setting for the "frmYourForm"
- recordsource in the form "frmYourForm"
- each fields on the form the (control source)
Your suggestion is great but I need to know the syntax and what setting
for
the form or if I were to use the query itself what is the exact syntax to
execute it. Thanks in advance.
Private Sub cmdOK_Click()
Dim strWhere As String
strWhere = "Select [LastName], [City] from tblTESTS WHERE "
If Not IsNull(Me.cboLastName) Then
'strWhere = strWhere & " And [LastName] = """ &
strWhere = strWhere & " [LastName] = """ & _
Me.cboLastName & """"
End If
If Not IsNull(Me.cboCity) Then
strWhere = strWhere & " AND [City] = """ & _
Me.cboCity & """"
End If
'other control code
'DoCmd.OpenForm "frmTESTpara2", , , strWhere
End Sub
:
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
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
:
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
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
