How to open form based on combo box selection.

[rakabuku]

I have this form called "switchboard" and in it are two combo boxes: Organization and Staff Initials (cboOrganization, cboStaff_init). There is also a search button called "OK". See picture "DB1"

What I want to do is, based on the selection of my "Staff Initials" combo box, when I click the "OK" button, I want the Multiple Items Form (Test 2) to open that correspond only to that "Staff Initials". See Picture DB2 and DB 1.

I'm really new at this VBA code, please provide code if possible.

[AccessIdiot]

Look into DoCmd.OpenForm and put it in the onclick event of your button.

[beacon]

Hi raka,

I'm going to assume that your field in the "Test 2" form for staff initials is called Staff_Init. If it's different, you'll need to modify it in the code. The idea behind the code is that you are creating a filter for the form and triggering it when you open the form from the "switchboard" form. You'll need to make sure that the "Allow Filters" property for the "Test 2" form is set to "Yes".

In the Click event for the command button on your "switchboard" form, enter the following code:

Dim myVar 'This is a variable for the value in the Staff combobox
 
myVar = "[Staff_Init] = '" & cboStaff_Init.Value & "'" 'This is the filter...the single quotation marks indicate that the value being passed is a string. If it were a date you'd need the "#" sign and if it was a number you wouldn't need single quotations or the "#" signs.
 
DoCmd.OpenForm "[Test 2]", WhereCondition:=myVar 'This opens the form with the filter you created above.
 

This should get you taken care of.

[rakabuku]

When I click on the button it says that the form "TEST 2" does not exist or its misspelled. However that is the actual name of the form. Any idea what could be happening? The snaff_init values come from Table 1.

See attachment on next post.

[rakabuku]

see new attachment

[beacon]

Try it without the brackets around "Test 2" in the code and see if that works. Usually a form, table, query, etc. that has spaces in the name has to be enclosed in brackets, but maybe that isn't the case with the DoCmd (I never have that problem because I don't put spaces in the names of my objects...just so you know, it's not good practice to create with spaces in the name, so you may want to consider that in your future developments).

[Jerry Maiapu]

In post#3 try, try removing these brackets.. [] in code line 5. And try that..
Should be something like this..

DoCmd.OpenForm "Test 2", WhereCondition:=myVar 

Cheers

Passing by

JM

[Jerry Maiapu]

If that does not work I do not know about access 2007 but this works in Access 2003 so try this..

Replace my previous suggestion code with this:

DoCmd.OpenForm "Test 2",,,myVar 

[rakabuku]

Thank you, it works!

[Jerry Maiapu]

Which one worked..also CHOOSE the post as BEST ANSWER

Glad it helped..

Regards

[rakabuku]

It opens the form, but the staff_init box doesnt filter to the selection of the combo box, it stays in blank.

[beacon]

Do you have a Staff_Init combo box on the form that you've opened? Are you wanting the value that you entered on the calling form to appear in a combo box on the form that was opened?

If that's the case, you will probably have to send the myVar variable in the code I provided initially as an OpenArg too. Then, in the form that is opened, you'll have to assign the OpenArg to the combo box in the FormLoad event.