One Last Combo box Question

[btrede]

I appreciate all who have helped me, but here is an interesting question.

In my data base, I have created multiple forms that have data for queries, and then execute / launch a report.

I would like to know if rather than having 20 buttons on a switchboard, is there a way to create a combo box or list box, where lets say you select Form1, and then it opens the form. hence, a way to create a combo or list box to open the form from a list of forms.

I was thinking perhaps a field list or a hyperlink to the form, but not sure.

Any suggestions from anyone.

hopefully i won't have any more access projects for awhile. Just a novice here.



Bradley

 

[Andrew Fergus]

Hi Bradley


There may be a better way of doing this, but the following method works for me.

Create a new table, call it 'tblMyForms' and include a single field in that table called 'MyFormName'. View the table in data mode and manually add the names of your forms (use copy > paste). Create a combo box on your form that points to this new table and attach the following code to the 'After Update' event of the combo box :

Private Sub MyComboBox_AfterUpdate()

On Error GoTo Err_Handler:

Dim strFormSelected As String

strFormSelected = Nz(Me.MyComboBox, "")

If strFormSelected = "" Then
    Exit Sub
Else
    DoCmd.OpenForm strFormSelected
End If

Exit_Here:
    Exit Sub

Err_Handler:
    MsgBox Err.Description, vbCritical, "Error : " & Err.Number
    Resume Exit_Here

End Sub

Use your actual combo box name where I have used 'MyComboBox'.

HTH, Andrew

 

[SydneyGeek]

Hi Bradley, another suggestion. I use it to launch forms or reports.

1. A table with 2 fields: FormName and DisplayName. Populate it with the names of all forms that you wish to open. DisplayName can be a more user-friendly description that shows in the listbox.
Note: if you want to customise the display order, create a Number field called SortOrder. Adjust the numbering to suit the desired order.

2. A listbox based on the table. Hide the first column so users only see the DisplayName, and make Column 1 the bound column. The Wizard will taek you through it -- just follow the prompts and take defaults, but opt to use the result rather than storing it. Call the listbox LstForms.
Note: If you added SortOrder, set its width to 0 so users don't see it but make the listbox sort ascending on this field.

3. In the Click event of the listbox,
DoCmd.OpenForm Me.LstForms

Denis

 

[VicRauch]

My report "menu" is also a form that lists each report, and as Denis does, I have a user friendly name or description of the report, along with the sort order field. But, for my key, I use the internal name of the report, or it could be the name of a function if things need to be done before the report runs. And I have two Yes/No fields in my tblReports, one to allow the menu to provide a Print function and the other to provide a View function. So, all the user sees is the Name (Description) of the report, and a Print check box and a View check box. Here is the code I use behind the Reports "Menu".

Option Compare Database
Option Explicit

Private Sub Print_AfterUpdate()
  Me!Print = False
  SaveRecord
  ViewPrint = acViewNormal
  ExecuteViewPrint Me!ReportName
End Sub

Private Sub View_AfterUpdate()
  Me!View = False
  SaveRecord
  ViewPrint = acViewPreview
  ExecuteViewPrint Me!ReportName
End Sub

Private Sub ExecuteViewPrint(rptName As String)
On Error Resume Next
  U.RequestedReport = rptName
  RetVal = Eval(rptName & "()")
  If Err = 2425 Then
    DoCmd.OpenReport rptName, ViewPrint
  End If
End Sub

Which ever check box is clicked to run the report, I set it to False and save the record. This way the click had the feel of a button because the check box never shows the check. After saving the record, I set a variable "ViewPrint" to acViewNormal (for Print) or acViewPreview (for View), then call the routine "ExecuteViewPrint" passing in the report name from the table.
The routine "ExecuteViewPrint" does an Eval of the report name, which means it tries to run the report name as if it were a Function. If it is a function, which many of my report names are, the function runs. If it is not a function, error 2425 is raised, and the routine then just opens the report with the DoCmd.OpenReport command.
This way, I can write a function to run the report when needed. If there is no function, then the report is just run.
My users have loved this model because they have only one place to go to run a report, and the form never changes. A new line is just added when a new report is written. I don't have to worry about running out of room for new buttons, and I can have fairly long, descriptive names for the reports. And, I actually have some functions that are run from here that are not even reports.
HTH,