[AccessD] Re: InputBox sizing...leading into yet another question...using a drop-down for reports

John,

The code below will populate a table with a list of reports.
Note that the Description is used to hold specific information.
An Autonumber column for the Unique ID of the report.
The English title for the report
The type of the report (i.e. Summary, Monthly, Annual, etc.)
The form to open if the report requires criteria.

By doing it this way, you do not have to open each report
in design view to get information about it.  And, since this
code runs on open of the form, you will always get the most
recent list of reports.  You can also reduce the number
of report options on your menu to one...the one that opens
this form.

For those interested, I also do something similar for my
lookup tables. So there is only one menu option for maintaining
that list.

WATCH FOR LINE WRAP

Robert



Private Sub Form_Open(Cancel As Integer)
     Dim db As Database
     Dim cntr As Container
     Dim intI As Integer
     Dim strS As String
     Dim rs As Recordset
     On Error Resume Next

     strS = ""
     Set db = CurrentDb()
     db.Execute "DELETE * FROM tsys_Report;"
     Set rs = db.OpenRecordset("tsys_Report", dbOpenDynaset)
     Set cntr = db.Containers("REPORTS")
     For intI = 0 To cntr.Documents.Count
         If cntr.Documents(intI).Properties("Description") <> "" Then
             rs.AddNew
                 rs!ReportName = cntr.Documents(intI).Name
                 rs!ReportDescription = 
cntr.Documents(intI).Properties("Description")
                 strS = cntr.Documents(intI).Properties("Description")
                 ' Parse the data out to other fields
                 rs!ReportTitle = Mid(strS, 1, InStr(1, strS, ";") - 1)
                 strS = Mid(strS, InStr(1, strS, ";") + 2)
                 rs!ReportType = Mid(strS, 1, InStr(1, strS, ";") - 1)
                 strS = Mid(strS, InStr(1, strS, ";") + 2)
                 rs!FormToOpen = strS
             rs.Update
         End If
     Next
     rs.Close
     db.Close
     Set db = Nothing
End Sub


At 12:00 PM 2/19/2004 -0600, you wrote:
>Date: Thu, 19 Feb 2004 11:55:15 -0500
>From: "John Clark" <John.Clark at niagaracounty.com>
>Subject: Re: [AccessD] InputBox sizing...leading into yet another
>         question...using a drop-down for reports
>To: <accessd at databaseadvisors.com>
>Message-ID: <s034a44f.064 at nebnov3.niagaracounty.com>
>Content-Type: text/plain; charset=US-ASCII
>
>Yeah, that's what I figured. Thanks for the help though. I figured on
>doing something like the form Lambert had suggested, but I hadn't
>thought to use check boxes. But this does give me another idea though.
>
>I originally had this issue because the users quickly outgrew my
>reports menu--they said they wouldn't need more that a couple and I had
>made room for five despite what they said.  Well they have come up with
>a bunch that are very similar, but each has a different specialty--as a
>bunch I am referring to them as 'Program Reports', since they each
>feature a different program that the department runs.
>
>Since I am doing a separate pop-up, I am thinking of using a drop-down
>list with the report names in it--there may be around 12-15 or so of
>these reports. Is there a way of creating a table or query listing
>available reports, or more specifically, but less probable, certain
>types of reports--I could flag them somehow--and have them pipe into a
>drop-down. Or will I need to place these in myself, which I think I will
>probably have to do?
>
>Thanks again!
>
>John