Thread: Reports in listbox to reside in external db

I have reports visible in a listbox on a tabbed form. The Row Source for the listbox is:

Code:

SELECT [TblReports].ReportID, [TblReports].ReportName, [TblReports].ReportCaption, [TblReports].ReportCriteriaFlags FROM [TblReports] ORDER BY [TblReports].ReportCaption;

ReportCriteriaFlags is used to make date, employee fields etc. visible/invisible based on what is required of the report. A single button on the form opens the report selected in the listbox. This works fine.

QUESTION:

Some of the reports currently presented in the listbox are State-specific (i.e. California and Arizona have different forms for example). I would like to have these State-specific reports exist in a separate external database, and deploy the "State" database only to businesses in the appropriate State. The listbox then, in the main database would need to include reports from (2) locations, one, the main database, and two, the State-specific reports which reside in the external (but local) database.

Further explanation on why (I think) I need to do this: I foresee updating the reports as an issue I wish to avoid if possible. Each U.S. State updates their forms (which are the basis of my reports) annually. Using an external db to house the reports would allow me to simply email the updated reports to the 150+ locations, and eliminate any data migration that would be necessary if I updated the State-specific reports housed in their main db.

Does this make any sense? Being very new to Access, maybe I'm missing something.

I really appreciate the help you have all provided thus far.

So here's how I did it (for anyone interested). I created a link to the external data source TblReportsState from my main db, then created a union query

Code:

TABLE [TblReports] UNION SELECT * FROM [TblReportsState];

which added a linked table to my main database containing data from both sources i.e. TblReports (local) and TblReportsState (External). Then, for the listbox where I wanted the reports from both databases to show, I set the listbox Row Source like

Code:

SELECT [QryUnionReports].ReportID, [QryUnionReports].ReportName, [QryUnionReports].ReportCaption, [QryUnionReports].ReportCriteriaFlags FROM [QryUnionReports] ORDER BY [QryUnionReports].ReportCaption;

using the union query QryUnionReports.

but now the real fun begins....

Now I'm stuck. Using the union query, the listbox properly shows reports from the local and external databases. The button on the reports tab of the main db has as it's code:

Code:

On Error GoTo Err_cmdGlobalReportOpen_Click

    Dim stDocName As String
    
    stDocName = [Forms]![FrmMain]![lstReportName].Column(1)
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdGlobalReportOpen_Click:
    Exit Sub

Err_cmdGlobalReportOpen_Click:
    MsgBox Err.Description
    Resume Exit_cmdGlobalReportOpen_Click

Of course, this code does not allow me to open remote reports. The external reports have as their record source a query which resides in my main db. So the task at hand is to modify the code above to retrieve local and external reports, and somehow have the local query pass data to the external reports.

Other options or possible alternatives:

• If this is too tricky to accomplish I could have all the reports stored in the external db. This would result in only about 15 reports total stored externally.
• Each external report only require as their record source data from a single record (one employee). These data are stored in (2) tables, TblEmployeeInjury and TblHotelInformation. Based on this, could I create links to these local tables from the external db, and use a filter on the external reports? I would still need a mechanism to open them from within the local db.

Any help is very much appreciated as I've no idea where to begin with this one.

I would create the reports on the local database. Then, in VBA, use a connection string with DAO or ADO to connect to the external database and execute some SQl to set as the report's datasource.

Hi JMH, I got it sorted now. This is the current solution.

Button code:

Code:

Dim stDocRemote As String
Dim stDocName As String

stDocRemote = [Forms]![FrmMain]![lstReportName].Column(4)

If stDocRemote = "1" Then

    Call OpenExternalRpt

Else

stDocName = [Forms]![FrmMain]![lstReportName].Column(1)

    DoCmd.OpenReport stDocName, acPreview

Exit_cmdGlobalReportOpen_Click:
    Exit Sub

Err_cmdGlobalReportOpen_Click:
    MsgBox Err.Description
    Resume Exit_cmdGlobalReportOpen_Click

End If

This is OpenExternalRpt

Code:

Public Function OpenExternalRpt()
On Error GoTo Err_OpenExternalRpt_Click

Dim rptCounter As Integer
Dim conPATH_TO_EXTERNAL_DB As String
Dim conREPORT_NAME As String
Dim conEXTERNAL_DB_NAME As String

' get path to external db from hidden subform located on the utilities tab of FrmMain
' need to figure out a better way to do this bit
conPATH_TO_EXTERNAL_DB = Forms!FrmMain!fsubPathToExternalDb.Controls!txtPathToExternalDb
conREPORT_NAME = [Forms]![FrmMain]![lstReportName].Column(1)
conEXTERNAL_DB_NAME = ExternalDbName

' Delete the local copy of the report if it exists
For rptCounter = 0 To CurrentDb.Containers("Reports").Documents.Count - 1

If CurrentDb.Containers("Reports").Documents(rptCounter).Name = conREPORT_NAME Then

   DoCmd.DeleteObject acReport, conREPORT_NAME

Exit For

End If

Next

'message to tell users to accept macro security message if it appears
MsgBox ("Please accept security message by clicking open on the next screen.")

'Import the external report based on the report selected in the listbox of FrmMain
DoCmd.TransferDatabase acImport, "Microsoft Access", conPATH_TO_EXTERNAL_DB, acReport, conREPORT_NAME, conREPORT_NAME, False

'Print preview the external/now Internal Report
DoCmd.OpenReport conREPORT_NAME, acPreview

Exit_OpenExternalRpt_Click:
    Exit Function

Err_OpenExternalRpt_Click:
    MsgBox Err.Description
    Resume Exit_OpenExternalRpt_Click

End Function

Done with a little help from just about everyone

Thanks all