parameters with a twist

[gwigton]

Ok folks, I am creating a dynamic report generator so that I don't have to recompile the .exe everytime I create a new report. I have created a form that has aproximately 15 fields (combo boxes) that are all disabled until after the user selects a report title from the first combo box (this one is dynamically loaded from a table that holds report info). After the user selects a report title the program queries the table again and, based on a series of yes/no fields in the report table for that report, makes the rest of the combo boxes enabled or disabled. Then the user selects the values they want from the enabled combo boxes and clicks a button to get veiw the report in snapshot viewer. What actually happens behind the scene is a access report is already created and just uses the users entries as part of the where condition. (This is where the problem begins) Since there are so many posibilities for where conditions because there would be one for each report, I would like to store the where condition code in a field within the report info table. The following is the module that performs this action.

Dim dbRecap As Object
Dim strSQL As String
Dim strReportName As String
Set conData = New ADODB.Connection
Set rsData = New ADODB.Recordset

conData.Open strConn
With rsData
.CursorLocation = adUseClient
.Open "Select * FROM tblTradesReports WHERE tblTradesReports.reportDisplayName='" & strCourseName & "'", strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
If .RecordCount = 1 Then
strReportName = ![ReportName] 'Gets the true access report name
strSQL = ![reportQuery] 'Gets the code for the where condition. Example- strSQL="tblCourses.courseCode='" & Trim(Left(cboSelect.Text, 9)) & "'"
End If
.Close
End With
conData.Close
Set rsData = Nothing
Set conData = Nothing

Dim objRecap As Access.Application
Set objRecap = New Access.Application
Set dbRecap = objRecap.DBEngine.OpenDatabase(strAppPath & "industry.mdb", True, True, ";PWD=" & pwd)

objRecap.OpenCurrentDatabase strAppPath & "industry.mdb"
With objRecap
.Visible = False
.DoCmd.OpenReport strReportName, acViewPreview, , strSQL
.DoCmd.OutputTo acOutputReport, "rptTempCourseRoster", ".snp", App.Path & "\report.snp"
.DoCmd.Close acReport, strReportName, acSaveNo
End With
objRecap.CloseCurrentDatabase
objRecap.Application.Quit
dbRecap.Close
Set dbRecap = Nothing
Set objRecap = Nothing
View_Report 'this opens the report.snp file
Exit Sub
HandleError:
gatherErrInfo

I am thinking that the quotes cause the problem, but when I run the program it returns nothing, but I do have the correct number of records and the proper info in both the strSQL and strReportName variables. If I cut and paste the same line that is in the strSQL variable to the code itself the report works, but if I pass it using the variable the report shows no results. Any ideas???

 

[zemp]

I think that this would be better received in an Access forum. Although similar VB and Access VBA are different. Check the 'Related Forums' box on the right of this page for some links to Access forums.

zemp

 

[gwigton]

I don't know why I would put this in a access forum when it is written in VB and access is just the database. The access portion isn't giving the problems it is the code in the recordset being passed through a variable that is giving the problem.

 

[gwigton]

To simplify what I am trying to do, this is no different than if I were storing "Now()" in a recordset field and wanted to bring it into my vb code for a message box. Example - rsField=fieldValue ' Now()
msgbox rsField
The variable rsField would contain "Now()" and the msgbox would display the present time.

 

[zemp]

You are using the Access application object to run your report.

If I cut and paste the same line that is in the strSQL variable to the code itself the report works, but if I pass it using the variable the report shows no results.

Here you say that the problem is with the Access code and not with the VB part of the program. You also say that the report works with the parameters so it is just a case of getting the correct syntax in the VBA code that runs the Access application object. Thus an Access VBA forum.

There would likely be more people familiar with its syntax and potentail problems in an Access forum than in this forum. It is not wrong of you to post here, I just think you would get better results, for what I interpret as your problem, in an Access forum.

zemp

 

[zemp]

If my interpretation is incorrect, I apologize and please clarify.

zemp

 

[gwigton]

Well Zemp, I think we are both right. I am using access code in the VB program so I can see your point, but I get the same results when I try the Now() function in a msgbox example I posted above, so from that prospective it could be looked at as a vb question. I guess the whole issue boils down to how do I get VB to perform the code that I store in any string variable? or any type of variable??

 

[gwigton]

Just to clarify I mean rsField="Now()" not rsField=now().

 

[ArtieChoke]

how do I get VB to perform the code that I store in any string variable? or any type of variable??

You can't do it directly. Search this site for similar topics - strongm put forth a solution that might work for you...

"I think we're all Bozos on this bus!" - Firesign Theatre