Access 2007 Combo Box to Limit Selections

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

MrsMILITARY

Thread Starter
Joined
Oct 27, 2011
Messages
7
I've been reading the threads and I haven't been able to find answers to my Access 2007 problem. Im a decent user, Tables, queries, and basic forms with commands using the wizard - but Im stumped. I need to limit the values on Combo Box #2 based on what I selected in Combo Box #1. I'm pretty sure I need to use some type of VBA coding but I can't figure it out and can't find any decent tutorials online. Specifically, once I select "GS level 12" on my form, I want the user to only be able to choose "GS level 12" performance criteria in the next combo box. Any Help would be appreciated! :)
 

MrsMILITARY

Thread Starter
Joined
Oct 27, 2011
Messages
7
Thanks OBP for the threads, But I still dont understand what I need to do. Im lost! Please help me!
 
Joined
Jul 29, 2001
Messages
21,334
Is there a relationship between what you are selecting in combo box 1 and combo box 2?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
OK, first of all the first combo needs some simple VBA code in it's "After Update Event Procedure". The code is
me.[second combo field name].requery
Where second combo field name is the actual name of the second combo, this action re-queries the combo with your latest selection of the first combo.
The second part involves the Record source of the second combo, which should contain the key field of the first Combo, if you have used a query for the combo you can add the "Filter" to the query's Criteria row of the first Combo's key field.
If you didn't use a query then the combo Wizard will have created an SQL query for you. You add the same Filter to the SQL, which you do not save, just close and Access will ask if you want to save the Combo's SQL and you say yes.
The Filter is
Forms![Form name where your combos are]![First combo name]
Where Form name where your combos are is the actual name of your form and First combo name is the actual name of the first combo.
 

MrsMILITARY

Thread Starter
Joined
Oct 27, 2011
Messages
7
Thanks OBP, I got the first step and attempted the 2nd step with the Row Source. Once I select the 1st combo box, the 2nd combo box doesnt give me any options now. It may be a problem with the my table set up and my relationships. Ughh - so frustrated.
 

MrsMILITARY

Thread Starter
Joined
Oct 27, 2011
Messages
7
I have a table that lists each GS Series (these are job levels in the govt) and a seperate table for each GS Series performance criteria. SO what I want to happen on my form is, Once the user selects their GS Series - the user will only be able to select the performance measures that applies. Any suggestions on the table?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Does the Performance Criteria table have the key field of the Series table in it?
The second combo needs something in the second table to relate it to the first combo/table.
 

MrsMILITARY

Thread Starter
Joined
Oct 27, 2011
Messages
7
SO my combo box with the performance criteria needs to contain a column that links it to GS-Series? I think this is where my confusion lies. Ive only done basic databases where the data does not need to link.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
The main point of Relational databases like Access is to link the data between the tables, sometimes you link directly between the 2 tables and sometimes you need a 3rd "Many to Many" table to join the other 2 tables together.
That situation would arise if you had performance criteria that could be used in more than one GS-Series.

Normally to link 2 tables you would have a main table (GS-Series) which should have a Key field.
The Sub Table (performance criteria) should have it's own key field and a Field set to Numeric (Type Long) which holds the Key field of the GS-Series which it is related to.
Which was the reason for Rockn's question at post #4.

I can't work with Access 2007, but I could set it up for you in Access 2003.
 

MrsMILITARY

Thread Starter
Joined
Oct 27, 2011
Messages
7
Okay there is the problem, I have designed databases using numeric data only and this one with text is throwing me off. I made a dummy databse in 2003 and tried to attach it, but for some reason I cant select the attachment button. Actually none of the buttons on my post box work. I dont understand what the key field should be in the sub table. Since its not a unique identifier. GS Series_TABLEGS 10GS 11GS 12GS 12 Performance Criteria_TablePerformance Criteria (column name)Leads TeamsPlans ProjectsTrains new EmployeesGS 11 Performance Criteria_Table Performance Criteria (Column name)Assist new employeesContributes to teamsContrubutes to ProjectsGS 10 Performance Criteria_TablePerformance Criteria (column name)attends training as neededFiles paperworkSupports new ProjectsThe remaining tables work fine with the form becasue they are not dependent on each other (like name, work area, date, etc)
 

MrsMILITARY

Thread Starter
Joined
Oct 27, 2011
Messages
7
OBP, I finally figured it out. Duh! Thanks for all your help. I have one table for GS Series with an autonumber, and the second table for Performance Criteria that has all the perforance criteria listed and a 3rd field with the GS level it applies to. THANK YOU SOOO MUCH!!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top