Report selection utility, help please

[DreewToo]

Hi all,<br><br>I've been stuggling with combining these 3 functions in one form.&nbsp;&nbsp;It would be great to get them to work together, but it doesn't seem to be happening yet.<br><br>On open, search the available drives and populate combo1's list<br><br>Combo 1:<br>all MS Access associated databases.&nbsp;&nbsp;(maybe a listbox?)<br><br>Combo 2:<br>Search the chosen database for it's MSysObjects table to show reports, displaying their names in the combo box.<br><br>Combo 3:<br>Choose which format to publish the choice in Combo 2 in.&nbsp;&nbsp;Choices include Preview, Print, HTML, and Email Attachment.<br><br>Command 1:<br>Check all the entries and complete chosen path.<br><br>Where I'm having difficulty is passing the database chosen to Combo 2 for the Select From In statement.&nbsp;&nbsp;This set of items seem entirely possible tho, and would make a very handy utility.<br><br>Any advice?<br>TYIA,<br><br>Drew<br>

 

[Lightning]

Drew<br><br>Here is the entire module code for a generic form that seems to be what you are after.&nbsp;&nbsp;This searches the database for all reports THAT HAVE A DESCRIPTION IN THE REPORT'S &quot;PROPERTIES&quot; DIALOG BOX.<br><br>It is a form consisting of an unbound list box and three command buttons - Preview, Print and Close<br><br>I hope this helps<br><br>Lightning<br><br>Option Compare Database<br>Option Explicit<br>'Form = frmMainReports<br>'Form and Function logic designed by Brad Darragh and<br>'published in Access-Office-VB Advisor magazine - July 1998.<br><br>Private Sub lstReports_DblClick(Cancel As Integer)<br>'Call the Preview action from the Preview button control.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;cmdPreview_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br>Private Sub cmdClose_Click()<br><br>'Close the current form and return to the Main Menu.<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br>Private Sub cmdPreview_Click()<br><br>'Display the selected report in preview mode.<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Minimize<br>&nbsp;&nbsp;&nbsp;&nbsp;OutputReport acPreview<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br>Private Sub cmdPrint_Click()<br><br>'Print the selected report.<br>&nbsp;&nbsp;&nbsp;&nbsp;OutputReport acNormal<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br>Private Sub Form_Load()<br><br>'Populate the listbox with the available reports names.<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rc As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;rc = FillReportList(Me!lstReports)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br>Private Sub OutputReport(intView As Integer)<br><br>On Error GoTo OutputReport_err<br><br>'Decide whether to open the report in preview or print mode.<br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case intView<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case acPreview<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'If no report selected, show error message.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If IsNull(Me!lstReports) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Please select a report.&quot;, vbOKOnly, &quot;No Report Selected&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenReport Me!lstReports, acPreview<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case acNormal<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'If no report selected, show error message.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If IsNull(Me!lstReports) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Please select a report.&quot;, vbOKOnly, &quot;No Report Selected&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenReport Me!lstReports, acNormal<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Invalid case statement&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GoTo OutputReport_exit<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;End Select<br><br>OutputReport_exit:<br>Exit Sub<br><br>OutputReport_err:<br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case Err<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 2501&nbsp;&nbsp;&nbsp;'Cancel a docmd event<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Resume Next<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err & Error<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Resume Next<br>&nbsp;&nbsp;&nbsp;&nbsp;End Select<br><br>End Sub<br>Function FillReportList(ctlTarget As Control) As Integer<br>'Function to populate the listbox with only those reports required.<br>'Filters out any &quot;USYS&quot; (hidden) objects and any report objects<br>'which do not have a &quot;Description&quot; title field.<br><br>Dim db As Database<br>Dim con As Container<br>Dim doc As Document<br>Dim intHasDescription As Integer<br>Dim temp As Variant<br><br>On Error GoTo FillReportList_err<br><br>Set db = CurrentDb<br><br>ctlTarget.RowSource = &quot;&quot;<br><br>'Loop through reports container to check to see if<br>'the object is not hidden and has a description.<br>For Each doc In db.Containers!Reports.Documents<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If Mid$(doc.Name, 1, 4) &lt;&gt; &quot;usys&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;intHasDescription = True<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;temp = doc.Properties!Description<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If intHasDescription Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;temp = doc.Name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ctlTarget.RowSource = ctlTarget.RowSource & temp & &quot;;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;temp = doc.Properties!Description<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ctlTarget.RowSource = ctlTarget.RowSource & temp & &quot;;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br><br>Next doc<br><br><br>FillReportList_exit:<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Function<br><br>FillReportList_err:<br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case Err<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 3270<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;intHasDescription = False<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Resume Next<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Error<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GoTo FillReportList_exit<br>&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>End Function<br>

 

[DreewToo]

Excellent, Lightening, thank you for your input.&nbsp;&nbsp;I'll put it to good use <br><br>Drew