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!

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/AlbertKal.../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 Removed)
http://www.members.shaw.ca/AlbertKallal

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



Albert D. Kallal wrote:
> 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/AlbertKal.../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 Removed)
> http://www.members.shaw.ca/AlbertKallal

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?


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

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

Albert D. Kallal wrote:
> 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?
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)

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.


(E-Mail Removed) wrote:
> 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
>
> Albert D. Kallal wrote:
> > 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?
> >
> >
> > --
> > Albert D. Kallal (Access MVP)
> > Edmonton, Alberta Canada
> > (E-Mail Removed)

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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

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 wrote:
> 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.
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)

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?)



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

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 wrote:
> 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?)
>
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)