Control Source and Prompt

  • Thread starter Thread starter THINKINGWAY
  • Start date Start date
T

THINKINGWAY

I have a form that displays information from a query (the control source) and
this works fine. The query prompts for a fiscal period from date to date
that it uses to generate query results. What I would like to happen is to
have these two date field inputs inside the form and not as prompts that are
the result of the queries execution. I hope this makes sense.

Thanx
 
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 typically designed for
user interface face stuff like prompts, print buttons etc).

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

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

The above should give you some ideas

In your date condition, you simply type is:

= forms!NameOfForm!NameOfContorl

Another way is 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

So, keep in mind you can use ZERO code if you just put the forms expression
in the query condition. (and, if you only have a date, or something, then
don't bother with all of my above coding examples....just place the forms
value right where you have the parameter prompts now in your query.
 
Exactly what I was looking for. Thank you very much.

Albert D. Kallal said:
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 typically designed for
user interface face stuff like prompts, print buttons etc).

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

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

The above should give you some ideas

In your date condition, you simply type is:

= forms!NameOfForm!NameOfContorl

Another way is 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

So, keep in mind you can use ZERO code if you just put the forms expression
in the query condition. (and, if you only have a date, or something, then
don't bother with all of my above coding examples....just place the forms
value right where you have the parameter prompts now in your query.

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