How can I open a form using a combo box in the switchboard?

[Mattic]

Hi guys,
I'm fairly new to access, with the help of this site and google I've managed to find my way around (even dabbling with some VB too) but there's something that's got me stuck.
I've got my DB working great, it's got the standard switchboard generated via the switchboard manager. I've got a load of forms and reports on there so I'd like to use a combo box to open up my forms rather than the buttons. For example I've got five forms I'd like to open up in add mode (one combo box) and another five forms I'd like to open in edit mode (second combo box). I'm sure this can be done but I'm struggling to find anything out there that can help me out... any ideas?
Many thanks,
Matt

[Mattic]

Noone got any ideas?

[missinglinq]

Setting the RowSource of a combobox to retrieve the names of all the forms in a database is easy, but opening some in one mode and some in others is a little bit more compicated.

Method A - Using 2 Comboboxes
You'd have to rename your forms, placing a a prefix before each name, i.e. edForm1 for a form you want to open in Edit mode and adForm2 for a form you want to open in Add mode. One combobox would retrieve all forms with the ed prefix and the other would retrieve all forms with the ad prefix. Then in the AfterUpdate event of the combobox the selected form would be opened in the appropriate mode.

Method B-Using 1 Combobox
The combobox would retrieve the names of all forms. In the AfterUpdate event of the combobox you would use a Select Case construct to tell Access which mode to open a particular form in.

There are some drawbacks to both methods. Method A requires that you stick to a strict naming convention in order that the correct forms are retrieved by the appropriate combobox. If you have forms in your database that you don't want retrieved by either combobox, you'd have to be sure not to name them with anything starting with either ed or ad. The SQL statement behind your comboboxes looks at the first two letters of the form's name in deciding whether or not to retrieve it. The advantage to this method is that if you later create new forms, you can include them in one or the other combobox by the simple virtue of the name you assign to them.

The drawback to Method B is that you would have to do the coding for the Select Case statement when you first set this up, and modify it each time you add a new form, to tell Access how to open the forms.

Think about which approach you'd prefer to use and let me know, and I'll post the relevant code for you..

Welcome to TheScripts!

Linq ;0)>

[Mattic]

Thanks Linq it's good to be here :)
Method A sounds like the best for me - thanks again!
Matt

[missinglinq]

From my previous post:
"You'd have to rename your forms, placing a prefix before each name, i.e. edForm1 for a form you want to open in Edit mode and adForm2 for a form you want to open in Add mode. One combobox would retrieve all forms with the ed prefix and the other would retrieve all forms with the ad prefix.

While this doesn’t involve a lot of code, it does involve a bit of setting up of of properties that can be daunting for a newbie, and so I’m going to give you step-by-step instructions on this.

Place two comboboxes on your form. When the Wizard comes up click on “Cancel”

For each combobox

Right click on the combobox
Click on Properties
Click on Other
Enter Names for Combobox (SelectFormsEdit and SelectFormsAdd respectively)
Click on Data
Set Row Source Type to Tables/Query
Set the Row Source by copying and pasting the SQL statements below

Row Source for Forms in Edit Mode

SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],2) = "ed") And ([MSysObjects].[Type])=-32768 ORDER BY [MSysObjects].[Name];

Row Source for Forms in Add Mode

SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],2) = "ad") And ([MSysObjects].[Type])=-32768 ORDER BY [MSysObjects].[Name];

************************************************** *****
Right click on the combobox
Click on Properties
Click on Events
Click to the right of the AfterUpdateProperty box
Click on Code Builder
For the appropriate Comboboxes enter the code below

DoCmd.OpenForm SelectFormsAdd, , , , acFormAdd

DoCmd.OpenForm SelectFormsEdit, , , , acFormEdit

so that the code looks like this

Private Sub SelectFormsAdd_AfterUpdate()
 DoCmd.OpenForm SelectFormsAdd, , , , acFormAdd
End Sub
 

Private Sub SelectFormsEdit_AfterUpdate()
 DoCmd.OpenForm SelectFormsEdit, , , , acFormEdit
End Sub

Good Luck!

Linq ;0)>

[Mattic]

Linq you star! It worked great thanks for your time :) I've now got a great looking switchboard!

The only thing I needed to was allow edits on the form itself (below) and it all worked great!

"The form that the wizard creates sets the "Allow Edits" property to "No." This setting effectively disallows any selection from combo boxes. All you need to do is open the form in Design View, go to the Properties list for the form itself, and change the Allow Edits property to "Yes." Save and close the form. You will now be able to make a selection in the combo box."
http://www.accessmvp.com/JConrad/accessjunkie/switchboardfaq.html#combobox

Thanks again!

[missinglinq]

Didn't realize that! Most of us here don't use the Switchboard Manager, but rather create our own using a form. Forms are much more flexible! It makes sense, though, because in the normal scheme of things you wouldn't be editing records (or doing abything else to them) on a Switchboard. This behaviour is restricted to the Switchboard Wizard; the Form Wizard sets AllowEdits - AllowDeletions - AllowAdditions all to Yes.

Glad you got everything working! It was fun working on this for you!

Linq ;0)>

[mb60]

Sir
The code to open forms in combo box worked very nicely !
Interface of my database changed like a beautiful face
thank you