Choose form to open from drop down list

[maGemme]

Hello again,

Sorry about what is probably a very basic question, I search the web and the forums but could not find the proper keywords to get the answer I want.

What I need seems very simple to me. I have several forms which are variations of the original. I need for the user to chose which form he wants to open from a drop down list and then hit a button that will open said form.

I'll explain in another way as to make sure this is clear:
I have 5 forms, for instance : form1, form2, form3, form4, form5.

I want a dropdown list to show the 5 forms available. The user selects one and pushes a button. The button then opens the proper form.

I'm sure it's very easy but I am still a beginer at this.
I want a

 

[maGemme]

The most probable thing that I thought of was :

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "[Forms!subfrmPMM!cboPMMForms]"
DoCmd.OpenForm stDocName, , , stLinkCriteria

but that did not work...

 

[vbaInet]

Set the following properties of your combo/list box:
Column Count - 2
Column Head - No
Row Source Type - Value List

Here's how you get the list of all the Forms in your database:

Dim db as DAO.Database, obj as Object

set db = CurrentDb

For Each obj In db.AllForms
    If obj.Type = acForm Then
[B]          ' Add the name of the form from obj.Name to the list/combo box  here[/B]
    End If
Next

I would imagine you've got a command button so that when clicked would open the form that is highlighted in the list/combo box. If so, do something like:

if combobox1.listindex > -1 then
     docmd.openform combobox1.value
end if

 

[boblarson]

Check out my sample here which does something similar with reports. You just need to modify the query to be looking for forms instead of reports.

 

[maGemme]

Dim db as DAO.Database, obj as Object
 
set db = CurrentDb
 
For Each obj In db.AllForms
    If obj.Type = acForm Then
[B]         ' Add the name of the form from obj.Name to the list/combo box  here[/B]
    End If
Next

Well I'm pretty sure that's what I'm looking for but where in my code do I put this? Don't I need to place it under a "Private Sub"

 

[maGemme]

Check out my sample here which does something similar with reports. You just need to modify the query to be looking for forms instead of reports.

That's not exactly what I'm looking for but it will also be usefull for another project of mine so thank you for sharing!

 

[boblarson]

Dim db as DAO.Database, obj as Object
 
set db = CurrentDb
 
For Each obj In db.AllForms
    If obj.Type = acForm Then
[B]         ' Add the name of the form from obj.Name to the list/combo box  here[/B]
    End If
Next

Well I'm pretty sure that's what I'm looking for but where in my code do I put this? Don't I need to place it under a "Private Sub"

No need to use code. Use a query for the combo box's record source.

I'll post the SQL in just a second.

 

[boblarson]

Here you go:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Type)=-32768))
ORDER BY MsysObjects.Name;

 

[vbaInet]

I keep forgetting you can run queries on system objects. Nice one Bob!!

 

[maGemme]

Here you go:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Type)=-32768))
ORDER BY MsysObjects.Name;

Sorry again for my newbiness but if I get this right I just paste this code in the Row Source Type of my combo box and that should populate the box with the forms?

Now I also guess that I have to SELECT every form that I want included? so Would I modify MsysObjects or ".name" to my form name?

Ex:
SELECT MsysObjects.form1
FROM MsysObjects

and I also guess that "(MsysObjects.Type)=-32768" tells the query that the type is a form (32768)?

 

[vbaInet]

1. Create a query, right click and select SQL VIEW
2. Copy and paste what Bob gave you
3. Run it or go to DATASHEET VIEW to see the results
4. Save and close the query
5. Use that query as the ROW SOURCE of your combo box.

 

[boblarson]

Sorry again for my newbiness but if I get this right I just paste this code in the Row Source Type of my combo box and that should populate the box with the forms?

Not Row Source TYPE but ROW SOURCE. The Row Source Type should be set to TABLE/QUERY. You get to the row source by clicking in the ROW SOURCE Property and then an ellipsis (...) should appear. You click on that and then it will likely bring up a query window. Go to VIEW > SQL and then paste what I gave you EXACTLY AS WRITTEN. Close the window and then when it asks if you want to save, press YES. Then you can open the form and the combo should show the list of forms available.

Now I also guess that I have to SELECT every form that I want included? so Would I modify MsysObjects or ".name" to my form name?

Ex:
SELECT MsysObjects.form1
FROM MsysObjects

NO, not at all. The code I gave you brings back the list of all forms. You just supply the query I gave you to the record source (as noted above).

and I also guess that "(MsysObjects.Type)=-32768" tells the query that the type is a form (32768)?

Yes, that is true.

 

[maGemme]

Ok!!! now I get it! And it does work!

The problem now is that this lists all of my forms, I only want the user to chose from a few forms, not all of them. How would I filter the list?

And once that's done I guess I just create a command button with the first code provided by vbaInet?

 

[vbaInet]

We wouldn't have gone through this whole explanation if you only wanted a few forms

I guess you could create an unlinked table and enter the table names that you would like to use. Then use that table as the Row Source of your combo box.

Bob might have some other ideas.

 

[boblarson]

Ok!!! now I get it! And it does work!

The problem now is that this lists all of my forms, I only want the user to chose from a few forms, not all of them. How would I filter the list?

You can do like I did with the reports. I name everything I want to show with a certain prefix. That way I can have it show up automatically in the list once I have named it. The changed query would be like this (making sure the bound column is 1 and the column widths are 0";2"):

SELECT MsysObjects.Name, Mid([Name],5) AS DisplayName
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "frm_*") AND ((MsysObjects.Type)=-32768))
ORDER BY MsysObjects.Name;

This code is using frm_ as the prefix.

And once that's done I guess I just create a command button with the first code provided by vbaInet?

I think so -

DoCmd.OpenForm Me.ComboBoxNameHere

should do it.

 

[boblarson]

Bob might have some other ideas.

Yep, see my post just previous to my response to this one.

It is based on name of the form so that it will be added automatically.

 

[boblarson]

Bob might have some other ideas.

Yep, see my post (#15).

It is based on name of the form so that it will be added automatically.

 

[vbaInet]

Yep, see my post (#15).

It is based on name of the form so that it will be added automatically.

Excellent! That was neat. I would personally go for postfix though as opposed to the prefix just so that the naming conventions for all forms are the same. It doesn't matter much anyway

 

[boblarson]

Excellent! That was neat. I would personally go for postfix though as opposed to the prefix just so that the naming conventions for all forms are the same. It doesn't matter much anyway

Yeah, either way would be fine. I usually want my forms to show up so my normal frm_ prefix would be fine (like frm_main but until you want it in the list you can use frmMain) and it would not show up because the underscore is not there.

For subforms I use sfrm (or at work right now sbfrm, due to naming conventions already established), so they won't show up in the list.

 

[maGemme]

It works!!!!

thank you both very much!!!

I use prefixes btw... I thought that WAS the convention... :s

i name my forms frmWhatever and subfrmWhatever...