R
Richard S.
How do I assign the value of a "WHERE" parameter with VBA, rather than the
user's entering a value in a form?
user's entering a value in a form?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Albert D. Kallal
Are you talkng about launching a form, or report?
You can build a query, and for a condtion, you can put in a forms refernece.
So, in the query buidler you could have for the city field
= forms!frmPrompt!txtCity
I don't actually like using forms refences directly in the sql..but, it does
get you out of having to write some code.
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 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
You can build a query, and for a condtion, you can put in a forms refernece.
So, in the query buidler you could have for the city field
= forms!frmPrompt!txtCity
I don't actually like using forms refences directly in the sql..but, it does
get you out of having to write some code.
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 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
A
Aaron Kempf
why do you use unbound forms?
you don't need unbound forms, you need SQL Server, kids
you don't need unbound forms, you need SQL Server, kids
Richard S. said:Thank you for your prompt reply.
I am using an unbound form as you suggest. The form enables the entry or
selection of criteria for 8 different fields. Some criteria pertain to data
in the principle table, others in related tables. My final result will be a
reference list of qualifying records displayed in a separate form, from which
the user can select a record(s) to display in detail. All but 1 of the
criteria are optional.
The querying involves possible sub-queries dependent on the user's
criteria. VBA code will determine which queries need to be run.
I can build the WHERE clauses programmatically. I have created stored
parameter queries to meet the need. My question is how do I actuate a stored
query by DoCmd.OpenQuery and assign the parameters at runtime via the VBA
WHERE clauses, rather than have the query pop up a box asking for the
parameter value.
I appreciate your help.
Albert D. Kallal said:Are you talkng about launching a form, or report?
You can build a query, and for a condtion, you can put in a forms refernece.
So, in the query buidler you could have for the city field
= forms!frmPrompt!txtCity
I don't actually like using forms refences directly in the sql..but, it does
get you out of having to write some code.
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 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
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
A
Albert D. Kallal
I can build the WHERE clauses programmatically. I have created stored
My 2nd half of the post says to actually remove, and dump the use of
actually parameters. If you just append the where clause to the sql (or
simply use the "where" clause feature of the open form (or open report),
then you DO NOT need any parameters in the sql. Beside, you might not know
which parameters you want to ignore..or actually have a few more parameters
for some prompt forms. so, my suggesting here is to actually NOT use
parameters, but build that where clause as sagged, and have NO parameters in
the sql....
It not clear if you trying to open a form, or report.
If the sql in code, then we must be talking about a export routine, or
perhaps some update code???
You only need create the "WHERE" clause for a report, or form, and there is
little use or advantage to placing actual parameters in the sql, since you
might not know which parameters you DO NOT want in that sql. Building your
own where clause simply gives you freedom here, and you keep your sql clear,
and free of parameters.
parameter queries to meet the need. My question is how do I actuate a
stored
query by DoCmd.OpenQuery and assign the parameters at runtime via the VBA
WHERE clauses, rather than have the query pop up a box asking for the
parameter value.
My 2nd half of the post says to actually remove, and dump the use of
actually parameters. If you just append the where clause to the sql (or
simply use the "where" clause feature of the open form (or open report),
then you DO NOT need any parameters in the sql. Beside, you might not know
which parameters you want to ignore..or actually have a few more parameters
for some prompt forms. so, my suggesting here is to actually NOT use
parameters, but build that where clause as sagged, and have NO parameters in
the sql....
It not clear if you trying to open a form, or report.
If the sql in code, then we must be talking about a export routine, or
perhaps some update code???
You only need create the "WHERE" clause for a report, or form, and there is
little use or advantage to placing actual parameters in the sql, since you
might not know which parameters you DO NOT want in that sql. Building your
own where clause simply gives you freedom here, and you keep your sql clear,
and free of parameters.
A
Aaron Kempf
with Access Data Projects it is as easy as this:
Docmd.Runsql "MySproc 12, 'Aaron', 'Hello World'"
In MDB it takes about 10 lines of code
Docmd.Runsql "MySproc 12, 'Aaron', 'Hello World'"
In MDB it takes about 10 lines of code