Which macro condition do I use?

[Mark25]

Any body knows how to do this?,

I know how to build a combo box that filters certain data. What I wish to do now is create an automated process that opens a form corresponding with the record the user chooses from the combo box. Let's assume I have a filtered list of 12 customers in my combo box. Once the user chooses a name from the filtered list, I want the corresponding customer-form to open automatically. How do I do this?

I'm trying to do it this way: by attaching a macro to the combo box which carries itself out "on choose". The macro contains 12 "Open Form" instructions for every name in the list. My idea is to build in a condition for the macro to make sure that only the chosen name is opened, and the rest is ignored.
Which expression do I use to obtain that result?

[GolferGuy]

Mark,
I'm assuming you don't know how to code in VBA, so I'll try to explain how you can streamline this within a Macro.
So, you have a combo box, which I'll ask you to name cboCustomerName.
I'll assume the name of your form is "Form1", but I'm fairly sure it is not "Form1", so you will need to change Form1 in my examples to the name of the form you are using.
In the AfterUpdate event for the combo box, enter "macOpenCustomerForm"
Now, create a new form (unless you have the form you want to use). I'm assuming this form is named "frmCustomerForm"
I'm assuming you have a field in the customer record named "CustomerName" Change this example to use what ever name you actually have for the customer name field.
If you have the whole customer table as the RecordSource for this form, or if you are creating a new form, you need to change from the table to a query over the customer table. Be sure to include all the fields you need in this query, but in the Criteria row of the query grid, in the CustomerName column, enter this: "Forms!Form1!cboCustomerName" (without the quotes).
Now you need to build the macro. The name of the macro will be "macOpenCustomerForm". This is the name that was specified earlier as the name for the AfterUpdate event for the combo box (cboCustomerName). You only need one Action in this macro, "OpenForm". Then in the Action Arguments section of the macro, you need to enter the form name, "frmCustomerForm". Close the macro and be sure you name it "macOpenCustomerForm".
You are now ready to test this. You should have the AfterUpdate event in the combo box set to "macOpenCustomerForm". You should have a form named "frmCustomerForm" that has a query as it's RecordSource, and the query should have "Forms!Form1!cboCustomerName" in the criteria row for the CustomerName column. And, you should have a macro named "macOpenCustomerForm" that has only one action, "OpenForm" with a form name of "frmCustomerForm". Be sure that any objects you have that do not have the same name that I have assumed in these examples, that you change the names within the examples to the names you are using.
Hope this helps!
Vic

[Mark25]

Vic, thanks for your effort, I appreciate it. I'm not sure however whether I succeeded in explaining my problem. The idea was to design a combobox list, where the user can choose from a list of 12 customers, and by doing so (-this is, by clicking on the name-), automatically open the form that corresponds to the chosen customername. That means there must be -not one, but- 12 possible choices, corresponding with 12 different forms. Your expression ("Forms!Form1!cboCustomerName") will probably just open one customerform specified as Form1, displaying a filtered list from a customer source, right? O.k., fine, but that's not my intention. Can you analyze the problem again, with the desired result as described above? Thanks again.
Mark

[GolferGuy]

Mark,
If what you are saying is that you want to open 1 of 12 different forms, based on Customer Name? So CustomerJones will get frmJones and CustomerSmith will get frmSmith, and CustomerMark will get frmMark? If that is your intent, please explain why different forms, and what is different about each form?
Thanks,
Vic

[Mark25]

Well, the 12 forms contain elaborate histories of every customer in such a way, that I need an entire page for every customer. That's why it works most efficient for me to create a separate form for every customer, instead of putting all information in one table. So the idea is to pick the customers name out of a combo list and by means of this action automatically be transferred to that customers form. Indeed, as you said, if you pick mr.Anderson, you have to be guided straight to mr.Andersons form, if you pick mr.Allenby, you have to be guided straight to mr.Allenby's form.
Of course there are more than 12 customers, but the combo box that handles these 12 names, only has to deal with the names beginning with an "A". Once I have created this combo box, I need to make another 25 to cover all letters of the alphabet.

So I have to build into this combo box (or in the macro that is attached to it), a program that covers all twelve choices. I think it must be possible in Access, but I simply don't know how. My idea was to trigger off a macro with 12 "OpenForm" commands, but pre-conditioned in such a way, that -in any given case- only one meets the condition (namely the one that corresponds with the chosen name). That one should be carried out, while the others are ignored. I don't know how do-able that is.
I would appreciate your suggestions.
Mark

[GolferGuy]

Mark,
I'll tell you how to this this in a very simple manner, but you first have to listen to me preach! You are creating a MONSTER! A Very, Very BIG MONSTER!

OK, now let me help you create this monster.

The combo box needs to have two columns. Column one will be the form name, column two will be the customer name. Make the column width property (format tab) of the combo box be 0" That way, the first column is hidden, but will be what is gotten when the combo box is reference later.
CREATE A TABLE THAT HOLDS THE FORM NAME AND CUSTOMER NAME. That way you will have a little bit of flexibility. (You could add FormName to the Custome table also)

Then in your macro, in the name of form field, put Forms!Form1!cboCustomerName.

Have fun!

[Mark25]

O.k., and what does "Form1" stand for? Is it an expression that remains as it is, regardless of the choice one makes in the combobox?
I mean, how is the user redirected to the right form using only this formula?

[GolferGuy]

Then in your macro, in the name of form field, put Forms!Form1!cboCustomerName

This needs an equal (=) sign in front of it. "=Forms!Form1!cboCustomerName" Sorry I missed that.

Now to answer your last question. Go back to my first reply. It was long, but it explained how to use one form (which I called "frmCustomerForm") and filter the data in your database to have only one customer show up on that form. But, because you are creating the MONSTER that you are, you feel you need one form for each customer. The form that I assumed you had your combo box in, I assumed is named "Form1". That is what Form1 stands for. By having the combo box contain a list of customer form names to be opened, and the customer name paired with the form name to be opened, after a customer name is selected in this combo box, then refering to the combo box will provide the Access program with the NAME of the form to be opened for that customer.

[Mark25]

Allright, Vic, thanks, but there may be just one small tiny little problem left here. Remember I'm building a monster, right? My "Form1" page does not contain one, but twenty six comboboxes, covering all the letters of the alphabet. How does this affect your formula, since it points to only one imaginative combobox?

[GolferGuy]

"=Forms!Form1!cboCustomerNameA"
"=Forms!Form1!cboCustomerNameB"
"=Forms!Form1!cboCustomerNameC"
etc.