Problem passing SQL string to Record Source property of a Report

[Manuel]

I have a form where the user can set the criteria for a report by selecting
values in certain controls (combo boxes). When the user finishes setting the
criteria/conditions for the report, he/she selects a command button. The
command button builds the SQL string based on the values selected in the
combo boxes, stores the string in a global variable (called ReportSQL_GLOBAL)
and then opens the report.

In the reports On Open event I have a function which calls the value in the
global variable and assigns it to the reports' Record Source property. See
below:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = GetReportSQL
End Sub

Public Function GetReportSQL()
GetReportSQL = ReportSQL_GLOBAL
End Function

The problem is that each time I run the report I receive an error message
stating: "The record source "SELECT * FROM ...." specified on this form or
report does not exist.

The string being passed is:
SELECT *FROM (tbl_ForeData INNER JOIN tbl_ForeDataME ON tbl_ForeData.LnNum =
tbl_ForeDataME.LnNum) INNER JOIN tbl_SaleDateMonth ON tbl_ForeDataME.LnNum =
tbl_SaleDateMonth.LnNum

This is the basic string returning all records with no slicing of the data
via a WHERE clause, i.e., this is the result of the user not selecting any
report conditions on the form.

The strange thing is that if I paste this exact string in the SQL view of a
query and then run the query I don’t receive the error message; the query
runs fine and return results.

Why am I getting the error message? Is there a limitation on how long the
string can be? I’m taking this same approach when filtering data on another
form (i.e., building a SQL string based on user-selected criteria and then
passing said string to the Forms’ Record Source property) and do not have
this issue.

Any assistance that you all could provide would be greatly appreciated.

Thanks!
Manuel

 

[Allen Browne]

Where have you declared ReportSQL_GLOBAL?
And where have you assigned a value to it?

Try declaring a public variable in the General Declarations section of a
*standard* module (not the module of another form or report):

1. In the code window, choose Module on Insert Menu.
Access opens a new module window.

2. Just below the Option statements, enter:
Public ReportSQL_GLOBAL As String

3. Save the module with a name such as Module1.

4. In the code before you OpenReport, assign a value to the string, e.g.:
ReportSQL_GLOBAL = "SELECT ...
DoCmd.OpenReport "...

5. Refer to the string in Report_Open. You don't need the function:
Me.RecordSource = ReportSQL_GLOBAL

You may also want to clear the string if you want to reuse it in other
contexts.

 

[Albert D. Kallal]

use the "where" clause, and make the reports sql *without* any
parameters..and you not need to change the params..

So, you can 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

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 controls 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

 

[Manuel]

That worked! Thanks a bunch

Manuel

Where have you declared ReportSQL_GLOBAL?
And where have you assigned a value to it?

Try declaring a public variable in the General Declarations section of a
*standard* module (not the module of another form or report):

1. In the code window, choose Module on Insert Menu.
Access opens a new module window.

2. Just below the Option statements, enter:
Public ReportSQL_GLOBAL As String

3. Save the module with a name such as Module1.

4. In the code before you OpenReport, assign a value to the string, e.g.:
ReportSQL_GLOBAL = "SELECT ...
DoCmd.OpenReport "...

5. Refer to the string in Report_Open. You don't need the function:
Me.RecordSource = ReportSQL_GLOBAL

You may also want to clear the string if you want to reuse it in other
contexts.