Parameter queries using forms with combo boxes, with select all, and defaulting combo boxes to blank

[nmoakeh]

Hello,

I saw some posts on google groups, as I'm new to the scene, and I
have a question along the lines of a post related to the discussed
topic of Parameter query using combo box in a form - option to select
all

Basically, I have a form based on a query with combo boxes and check
boxes so the users can sort based on predefined constructs to minimize
errors. I need to make it work in a way that the combo boxes display a
blank as a default (there aren't any blanks in the tables containing
the data for the combo sources) to make use of criteria in the query
similar to:

[Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName]
Is Null

Currently, my form defaults to the last set of criteria used. Any
input would be greatly appreciated.

Thanks!

 

[Albert D. Kallal]

There is a number of approaches.

The following is just a suggestion you might try
(this suggestion does take a bit of code writing on your part).

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above shold give you some ideas

So, the solution I use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

In addition, note the screen shots show some examples where "blank = all".
The above code simply ignores the control on the screen if it is null...and
this if you leave the control blank...you get all of that particular
condition...

 

[nmoakeh]

Albert,

Thanks for your help. I tried what you suggested - I created a new
unbound form with the combo-boxes (based on the tables, not the query?
not sure) and followed your code specific to the names of the combo
boxes, however, when I click my "OK" (print) button, I get a "Compile
Error: Syntax Error" and reverts back to the code. I'm pretty new to
vba altogether... I put the code for the "On Click" event for the OK
button. It highlights in yellow:
Private Sub OK_Click ( )
and further down in the code it also highlights (but not in yellow):
strWhere = strWhere " and "

all instances where the last highlighted code are, are colored in red.

If I move the code to it's own section then I get an error looking for
a macro

Where am I going wrong? Thanks again for your help and patience.

Nadeem

There is a number of approaches.

The following is just a suggestion you might try
(this suggestion does take a bit of code writing on your part).

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above shold give you some ideas

So, the solution I use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

In addition, note the screen shots show some examples where "blank = all".
The above code simply ignores the control on the screen if it is null...and
this if you leave the control blank...you get all of that particular
condition...



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D. Kallal]

Try making just ONE of the combo boxes work

I created a new
unbound form with the combo-boxes (based on the tables, not the query?
not sure)

Well, a query is often used for a combo box because it lets you more easier
set the sort order.

Where am I going wrong? Thanks again for your help and patience.

Hard to know without seeing your code. I going to suggest that you try and
make JUST ONE of the on-screen controls work.

Once you get ONE working, then you can start the task of making more
controls on that form work (but, start out simple, and JUST filter to one of
the controls).

So, cut out all of the code except for ONE of the controls on the screen.
What does that code look like?

 

[nmoakeh]

I think perhaps it would be easiest if I sent you that part of the db
if you're alright with that then maybe you can see my dilemma a bit
easier. When I set the combo box to take the data from the query, I
get ?Name? in the combo box, if I set it to the table storing the data,
I only get the data that is stored there, so if there is more than one
kind of value for that column,, and it's not in that table yet, it
doesn't show.
I'm probably missing something super obvious but I'm totally blind to
it for some reason.

Would it be possible for me to send it to you so you can see?

Much appreciated,
Nadeem

 

[nmoakeh]

Ok not sure what happened for the moment... limiting the code to just
the first part of the code works fine now... Adding the second code
gets me back to the Syntax Error message when I run the event. Here's
my code:

Private Sub OK_Click()

Dim strWhere As String

' select gender combo

If IsNull(cboGender) = False Then

strWhere = "Gender = '" & cboGender & "'"
End If

' select gender race

If IsNull(cboRace) = False Then
If strWhere <> "" Then
strWhere = strWhere " and " (<--- this line is always
in red; "Expected end of

Statement"?)
End If
strWhere = strWhere & "Race = '" & cboRace & "'"
End If


DoCmd.OpenReport "rptSurveyees", acViewPreview, , strWhere
End Sub

I have four additional, possibly a fifth as well I need to add to this.

Thanks again, sorry for any headaches I'm causing.

 

[Albert D. Kallal]

strWhere = strWhere " and " (<--- this line is always
in red; "Expected end of


Ah, ok..that is why we "compile" the code, as posting in a newsgroup message
don't check our code...does it!!

That should read:

strWhere = strWhere & " and "

The "&" sign is used to build (concataenate) the text.

 

[nmoakeh]

Albert,

Thanks a lot, that helped tremendously.

However, 2 out of my 6 categories when selecting either of any of
their values, then click OK, I get a Run-time Error '3464': Data type
mismatch in critera expression."

I checked the spellings of the two fields on the report and on form, in
fact just copy/pasted to make sure everything was the way it is
supposed to be, and it didn't change the outcome. I also verified the
spelling against the code, copy/pasted as well, still same results.
The fields are called Marital Status [MaritalStatus] and Gross
Household Income [GrossHouseholdIncome] any clue as to why else i'd
get such an error?

 

[Albert D. Kallal]

Great...ok..

So, the 1st one works....good.....

Now, lets start on the 2nd one.....


does the 2nd one work?

(in fact, remove all of the code (paste it into a temp text (notepad) doc
for later use).

The trick here is not to type in a zillion things, and hope it works. The
track is ONE THING at a time.

so, does the 2nd one work?

(remember, you can paste your code in your next resonse. - remember, lets
ONLY have the code for the first 2....does it work?)

 

[nmoakeh]

Hello again Albert,

Unfortunately the two fields in question do not work even when either
of them are the only code present. I still get the same error
mentioned previously. The other 4 work beautifully. Will the code
also work with a calculated "Age" field or is there something special
for that being that it's a calculated field on the query?

You've been a great help and I really appreciate all your guidance on
this.

 

[Albert D. Kallal]

So, ok, so, 0, 1, 2, 3, or 4 of them work great....

So, post the code for the 5th one that don't work....

as I said, approach this one at a time...

and, if I am wrong about the above 4 working, then we need to even back
track on that...

So, assuming those 4 fields work in your code, what does the code for the 5
one that don't work look like?

 

[nmoakeh]

Ok, so codes for the first 5 work absolutely perfect together on the
form, the other two do not work at all, even when no other code is
present at all.

' select household income

If IsNull(cboHouseholdIncome) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "HouseholdIncome = '" & cboHousehold Income &
"'"
End If

' select education

If IsNull(cboHighestEd) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "HighestEd= '" & cboHighestEd & "'"
End If

Those are the codes for the other two fields that, when inserted either
in combination with the existing codes, or simply either of the two by
itself, do not work and it errors out with:
Run-time Erorr '3464'
Data type mismatch in criteria expression

Also, what if I want to add "Between age X and age Y" I have an Age
calculation on the query, and they want to add a sorting between age
groups. I am not sure how to go about between ages.

Thanks again for everything!

 

[Albert D. Kallal]

' select household income

If IsNull(cboHouseholdIncome) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "HouseholdIncome = '" & cboHousehold Income &
" ' "
End If

The above is likey not even to comple, let a lone run? Can you compile the
above?


strWhere = strWhere & "HouseholdIncome = '" & cboHousehold Income &

you have "cboHousehold Income"

There is a space in the above, and that is NOT allowwed. Perhaps this is a
type-o

Also, for number type fields, you do NOT need to add the quotes.

Use

me.cboHouseholdIncome

(let the inteli-sense pop up a list). As a future lesson, you might want to
avoid spaces in your form names, field names, and table names. (your above
syntax with space between "cobHouseHold" and "income" suggests a serious
syntax problem here.

use the format me. for the foreseeable future, and the above problem would
not have occurred. regardless, you can't have that stray extra space in the
code...it will not work.

Also, since it is a number type field, you do NOT to to surround the value
with quotes.

If IsNull(cboHighestEd) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "HighestEd= '" & cboHighestEd & "'"
End If

The above looks ok. If HighestEd is a number type (in the table desing),
then you would not need the quotes part to surround the text

Also, what if I want to add "Between age X and age Y" I have an Age
calculation on the query, and they want to add a sorting between age
groups. I am not sure how to go about between ages.

covered in one of my pervous srespnse in this thread. It is the same as the
date exmaple, but without needing to surround with any delimoers.

remember
for number type fields, no quotes needed
eg:
"InvoiceNumber = 123"
or, if a value from a form
"InvoiceNumber = ' & me.WhatInvoiceNum

for text type fields, we need quotes
" City = 'Edmonton' "
or, if a vlaue from a form
"City = '" & me.WhatCity & "'"

for date type fields, we need #
"InvoiceDate = #10/15,2006#"
or, if a value from a form
"InvoiceDate = #" & format(me.whatDate,"mm/dd/yyyy") & "#"

Note for dates, you MUST force the format to USA date format, regardless of
your date format, and the above shows how this is done. For your age calc,
it is just a number, so, you would use the first format as above.

The rules for how the text is setup is the SAME as it is in sql.