combo box dilemma

[John]

I placed a posting awhile back and recvd a response that
got me going (thanks Fred). But in doing so, I ran into
a further snag. So I am posting it again since the other
is back a bit. Needless to say I was a bit hasty in
saying it worked.
I have a form that has a combobox on it from which
clients can be selected from. The clients have client
codes assigned to them. All the forms are filterd based
on the client code. There are numerous forms requiring
numerous buttons (actually another form to hold them
all). Currently there is a macro to the buttons that
opens up the form and filters it with the following in
the macro WHERE line. It is [ClientCode]=Forms!
[usrfrmClientSelection]![ClientCode]. Now, the division
asking for this Access database is requesting a few more
forms to be added. The number of buttons is getting more
then it should be. I would like to offer a different
method of opening the forms.
I would like to use a combo on the Client Selection form
that lists out the form names for the user to select
from. Thanks to Fred I have in the click event of the
combo the following;

DoCmd. OpenForm comboboxname, acNormal,,[ClientCode]=
[Forms]![usrfrmClientSelection]![ClientCode].

There remainder is all default and so it isn't needed.
The form opens up but is not filtered. This is the
stumbling block I have encountered.
Any assistance is appreciated.
*** John

 

[Crystal]

If I read this correctly, you want to open a form by
choosing it's name from a combo box?

If that's the case then, here is one possible solution:

Create the combo box and in the data field use:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768));

as the SQL statement. This will give you a combo box
with all of your form names in the database. Then, on the
AfterUpdate event of the combo box, write this code:

Dim strFormName as String
strFormName = Me.ComboBox.Value
DoCmd.OpenForm strFormName

This will open the form.

If you have additional forms in your database that you do
not want to show up in the combo box, you could make a
query in design view and customize the criteria to find
only the forms you want.

Example: Use a uniform naming convention for the forms
that your users will access from the combo box (usrForm1,
usrForm2, etc.) In the Name field of the MSysObjects
table, write criteria: Like "usr" & "*"

This will return all of the forms that have that prefix.
You'd use the same code on the combo box in this
situation.

Hope this helps,
Crystal

 

[John]

Crystal, I got the filter part to work. I added it as
part of the DoCmd line. This is beginning to work good.
Thanks for the help on this.
Just so you know why I was trying to get this one of the
divisions asked for this db and wanted all of the
processes separated out so each has its own form.
They've been giving me the process in chuncks. I'm at 31
forms so far with (and I'm guessing) at least that many
more to go. That's not counting the "popups" they want.
So you can see, the standard button method could be a bit
cumbersome by the time this all comes together. Oh well,
if that's what they want, that's what they'll get.
*** John