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

I prefer to use a custom property so that the description property can
be used for other things.

Charlotte Foust

-----Original Message-----
From: Robert L. Stewart [mailto:rl_stewart at highstream.net] 
Sent: Thursday, February 19, 2004 11:05 AM
To: accessd at databaseadvisors.com
Cc: John.Clark at niagaracounty.com
Subject: [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


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com