Open a report

[Acropolis]

Hi


I have a list box which contains different types of log entries. Each different log entry type has a different report that goes with it.


What I want to do is select an entry in the list box, and click on print, email, pdf or view report buttons, and the appropriate happens, selecting the associated report for the log type automatically.


I have a table with the logtype in it, and against that I have a DefaultReport field, which contains the name of the report that needs to be opened for that logtype.


I am getting the correct name for the report no problem at all, I have made sure that the name is right by going to rename report and copy and pasting it.


When it goes to open the report I get an error message - Run-Time error 3071, the expression is typed incorrectly or is to complex to be evaluated.


I have a debug line running to make sure I am pulling back the correct report name and I am.


I have tried putting the dlookup where the report name would normally go in the docmd.openreport and I have also tried using ReportToView as a String and putting that in the docmd.openreport, but to no joy.


I have tried it with and without " around the report name and still no joy, either in the table or by concatenating the dlookup with Chr(34) at either end of it, then it comes up saying the report cannot be found.


Some help please... something so simple and yet so hard arghhh




Thanks

 

[Uncle Gizmo]

Please post the VBA code.

 

[Acropolis]

Private Sub cmdReport_Click()
Dim Report As String
If IsNull(Me.lstResults) Then
    MsgBox "Select transaction to view.", vbCritical
Else
    Report = DLookup("[DefaultReport]", "tblLogTypes", "[ID] = " & DLookup("[LogTypeID]", "tblGeneralStockLog", "[LogID] = " & Me.lstResults & "") & "")
    Debug.Print Report
    DoCmd.OpenReport Report, acViewPreview
    
End If
End Sub

 

[JHB]

If the report is based on a query, then it could be something wrong with the query.
By the way, you are using a reserved words - "Report".
Rename it to "ReportName".
Here is a link to "Reserved words and symbols" in MS-Access.
http://office.microsoft.com/en-us/a...7-reserved-words-and-symbols-HA010030643.aspx

 

[Acropolis]

The report is based on a query, but there's nothing wrong with the query. The report opens elsewhere and is absolutely fine, I can't even get the report to open, it doesn't get as far as opening the report to error on a problem with the query, it throws an error saying the report cannot be found or similar.


I have tried it with different name in case it was and it made no difference and still wouldn't work.

 

[Thales750]

I would be inclined to use a string public variable that gets its value from the click action in the list box.

Then your command code would be docmd,openReport PubOpenReport,

I might also get in the habit of using prefixes on variables in the same way as list boxes and forms etc. So a string variable would be something like strReport