Hello. I am new to Microsoft Access 2007 - learning by trial and error. I do not yet know how to do programming and was hoping to do the following without programming. Would appreciate any help/insight.

Essentially I am trying to make a form that has three different combo boxes. These are key fields from a table I have of all company employees. The key fields I want to query on are Division, Position, and Grade. Division has a choice of 5, Position a choice of 3 entries, and Grade a list of 4 choices.

I got this to work in Excel using the Auto Filter but now am trying to figure how to do this in Access. I would like the user to pick any of these three combo boxes to use as sort criteria.

For instance, the total table list is 100 employees. When you select Org A from the Org Combo Box, then you have only 50 employees that meet the criteria. If you select Engineer for Position you get 20 of the 50 and so for. Or you could start with Position and have 75 listings for Engineer that comes from multiple Divisions.

Hope this makes sense. I have just been unable to get this to work. I have a form made from the Table with all of the key fields (name, address, ..), the combo boxes for Division, Position, and Grade but can't figure how to link the combo box selections to a query and then update the form.

I know I sound like a beginner - I am!! Any help would be appreciated. I told my boss I would have a simple form to show him on Monday. I may have leaped before I looked.

Thanks!!

Kannon

You can generate a query to filter the table’s records, but it can lead to a messy and slow query; much better to filter the form by generating the filter in code.

First though, you need to check your tables are normalised, you should have four different tables:

tblEmployees
EmpID (PrimaryKey (PK) AutoNumber)
FirstName
LastName
DivisionID (Foreign Key (FK) Number)
PositionID (FK Number)
GradeID (FK Number)

tblDivisions
DivisionID (PK AutoNumber)
Division

tblPositions
PositionID (PK AutoNumber)
Position

tblGrades
GradeID (PK AutoNumber)
Grade

The ID number for the Division, Grade and Position are stored in the Employees table NOT the text.

Now the Combo boxes on your form (call them cmbPosn, cmbGrade, cmbDivn)
each need to have two columns, the first hidden (0 width) and the second showing the text from the table. Locate these three combo boses I the header section of your form.

In the After Update event of one of the combo boxes have the following code:


You will also need to call this code from the after update event of the other two select combo boxes.

I have attached a db (unfortunately in A2003 – but you can import it into A2007 (remember to enable the code))
This shows the form filtering and it also has a query showing how to use the combo boxes as criteria in the query.

HTH

An alternative is attached.

Thank you Bernie - the example provided a great learning tool.

Is there a way to select "All" possible choices in a combo box besides deleting a previous selection?

Thanks again - great info!

Kannon

Hi Kannon - You are very welcome.

I do not usually add an 'all' option to combo boxes but use combo box with nothing selected as meaning 'All'.
To do this you can either highlight the entry and delete it or, as I often do, put the following code in the combo box's Double Click event.

In the code sample I gave you you Me![ComboBoxName] = Null would be
Me![cmbGrade] = Null, or Me![cmbPosn] = Null, or Me![cmbDivn] = Null as appropriate but
ComboBoxName_AfterUpdate can be replaced by cmbGrade_AfterUpdate in all three cases.

To reset all thee buttons you could add a command button with the following code in the On Click event


If you do want to add an 'ALL' row then this web page gives one method.

HTH