Reports in listbox to reside in external db

[tuxalot]

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

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

 

[Dennisk]

So long as you link the external table to you main FE, you can use a UNION query to merge both datasets together to view or as a recordset for a form, combo/list box.

 

[tuxalot]

Thanks Dennisk, I ended up doing this:

Public Function OpenExternalRpt()

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


'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

End Function

And calling the function from this button code:

Dim stDocRemote As String

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

If stDocRemote = "1" Then

Call OpenExternalRpt

Else

    Dim stDocName As String

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

Seems to work well but I'm sure there is a more elegant solution.