By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,570 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,570 IT Pros & Developers. It's quick & easy.

How to open form based on combo box selection.

P: 6
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.
Attached Images
File Type: jpg DB1.jpg (11.2 KB, 1011 views)
File Type: jpg DB2.jpg (14.7 KB, 796 views)
Jul 20 '10 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. Dim myVar 'This is a variable for the value in the Staff combobox
  2.  
  3. 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.
  4.  
  5. DoCmd.OpenForm "[Test 2]", WhereCondition:=myVar 'This opens the form with the filter you created above.
  6.  
This should get you taken care of.

Share this Question
Share on Google+
11 Replies


AccessIdiot
100+
P: 493
Look into DoCmd.OpenForm and put it in the onclick event of your button.
Jul 20 '10 #2

beacon
100+
P: 579
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:

Expand|Select|Wrap|Line Numbers
  1. Dim myVar 'This is a variable for the value in the Staff combobox
  2.  
  3. 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.
  4.  
  5. DoCmd.OpenForm "[Test 2]", WhereCondition:=myVar 'This opens the form with the filter you created above.
  6.  
This should get you taken care of.
Jul 20 '10 #3

P: 6
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.
Jul 20 '10 #4

P: 6
see new attachment
Attached Images
File Type: jpg db3.jpg (14.8 KB, 624 views)
Jul 20 '10 #5

beacon
100+
P: 579
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).
Jul 20 '10 #6

Jerry Maiapu
100+
P: 259
In post#3 try, try removing these brackets.. [] in code line 5. And try that..
Should be something like this..

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Test 2", WhereCondition:=myVar 
Cheers

Passing by

JM
Jul 21 '10 #7

Jerry Maiapu
100+
P: 259
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Test 2",,,myVar 
Jul 21 '10 #8

P: 6
Thank you, it works!
Jul 21 '10 #9

Jerry Maiapu
100+
P: 259
Which one worked..also CHOOSE the post as BEST ANSWER

Glad it helped..

Regards
Jul 21 '10 #10

P: 6
It opens the form, but the staff_init box doesnt filter to the selection of the combo box, it stays in blank.
Sep 17 '10 #11

beacon
100+
P: 579
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.
Sep 20 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.