Forms - Combo Box

I only know the basics to Access. I just want to add a Combo Box to a form so that the rest of the information is filtered based on the information displayed in the Combo Box.

Specifically, certain CAMs have certain Employees. I want to chose a CAM in the combo box and have only their employees infomation show in the report.

I would appreciate your help. I've been watching tutorials all day!

Thanks.

Tell us a little more. What are the names of your tables that contain CAMs and Employees, and what are the primary key fields named? You can find that information by opening the Database Window (for the table names) and then opening each of those tables in Design View and looking for the little gold key icon at the left of the list of field names. Also, in the Employees table, can you identify which field corresponds to the primary key in the CAMs table? It may be named the same or something that makes it obvious that it's the foreign key. With this information, we can guide you through creating a combo box and filtering your form. You mentioned a report, but it sounds like you meant to say form. If that's not what you meant, please explain more fully.

Table WOAs:
Project
Assignment (Primary Key)
Description
CAM
Employees (I used Lookup Wizard to check multiple values for employees)

Table Employees:
Employee (Primary Key)
eTES Manager
Assignment (I used Lookup Wizard to check muptile values for assignments)
Location
Company

I guess I want a drop-down list box. They will not need to chose more than one option, and I would like to do this on a report if possible because I wanted this easy to print.

One report is CAM and I wanted a drop down list box to choose a CAM then the report would show this information:

Project Assignment Location Description
Employee eTES Manager

Another report is eTES Mngr and I wanted a list box to chose an eTES Manager so that the report shows this information:

Employee
Project Assignment Location Description CAM

Can you help?

Sorry, here is another

I just posted this on another thread, but sjben69's sticky has a link to datapig technologies. The video tutorials walks you through a number of beginner problems. If you are still having trouble let us know. I am giving you a link for cascading combo boxes. The idea is the same for whatever object you want to update - it sounds like you want a list box with all the fields for the associated record showing. The link for cascading comboboxes is:

http://datapigtechnologies.com/flashfiles/combobox2.html

FYI - a "drop-down" box is a combobox. A list box is a static box that allows you to scroll through records from a table or query.

Also, it doesn't look like your primary keys are numbers. Ideally, all you would have multiple tables that would be normalized so you aren't repeating data and look somehting like.

tblEmployees:
EmployeeID.............Number (Primary Key)
EmployeeFirstName...Text
EmployeeLastName...Text
eTES Manager.........Number (Foreign Key)
Assignment.............Number (Foreign Key - from a list table)
(if there can be multiple assignment this should be a seperate table)
Location.................Number (Foreign Key - from a list table)
Company................Number (Foreign Key - from a list table)

All of that said and depending on how many options there are you could also use combo boxes where you manually set all the options and use text fields (except the primary key)

[QUOTE=Emily.Hargett]Table WOAs:

I guess I want a drop-down list box. They will not need to chose more than one option, and I would like to do this on a report if possible because I wanted this easy to print.
QUOTE]

Also, forgot to mention, when you talk about objects like list boxes and combo boxes these are contained on forms. You can generate a report from a form. But, a report can't have a list box on it.

Thanks for your help. I have Access 2007 so the tutorial isn't exactly the same, but I think it will help.

So, I have another question then.

How can I just make one record for a form with all the data rather than the 473 records I have now? I just want all my information on one record.

Thanks.

While I don't yet have Office 2007, my understanding is that even the help articles on Word aren't updated fully. So I can't imagine trying to find stuff on 07



Hmmmm...I'm not sure exactly what you mean here...but, I'll try to help. When you typically set up a form (e.g., from the wizard it is bound to a recordset (e.g., a table or query). When you view the form it typically only shows one record. I don't think the wizard even gives you an option. You can go through record via the navigation buttons on the bottom of the form. You can also hide the navigation buttons if you want. If you want to find a specific record you have to search or filter the table/query some way (there are many ways to do this). If you need to do a search, lets deal with one problem at a time and focus on getting your tables, queries, and form objects (e.g., combo boxes) working first.

That said, you may also be seeing a continuous form where multiple records are displayed(assuming it is not continuous-although this can be changed in the form's properties). You can change this by right clicking on an open area on your form. In the box that pops up, ensure "form" is selected at the top so you are viewing the form properties. Look under Default View, it should be set to single form.

Once you get your table normallized repost them so we can make sure you are on the right track. If your tables are set up correctly, your going to have a very difficult time.

Okay, so I don't think I have my tables set up right then.

One Employee has one Etes Manager, one Location, one Company, but many Assignments.

One Assignment has one Project, one Description, one CAM (Cost Account Manager) and sveveral Employees.

How am I supposed to set this up without duplicate information?

Sounds like you need a form with a subform, and it's not too hard to do, if none of your needs deviate outside of what's normal...

First thing is to create a main form - just a form. Base this main form on the table that holds info on your CAM's (and bear in mind, you probably want to update the "Row Source" property - build a query that's basically "Select distinct [CAM field] from [YourCAMTable] group by [CAM field]", and then the drop down will show you a distinct and ordered list of CAM's that are in your system.

Depending on how it's created, you might be best off deleting the control that corresponds to the CAM and re-adding it, using the wizard (just make sure the wizard wand is highlighted in the toolbar, then click the combobox button in the toolbar, then draw your combobox). When you add it using the wizard this way, you get three options, and the third one you can check to do something like "use this combobox to select all other records on this form". That's completely not what it says literally, but that's the gist of it.

Then, follow this tutorial to add a subform to display all corresponding employees that are associated with that CAM:

http://www.brainbell.com/tutorials/...th_Subforms.htm

That tutorial is a quick and easy guide on how to include a subform in your form, and then how to associate the synchronize the subform with your main form.

Now, when you select a CAM in the drop-down that's located in the main form, any and all associated employees will dynamically appear in the subform. Take note of the tutorial where it shows you how to change the default 'datasheet' view into 'continuous forms', and then you'll be able to edit any employee record that's retrieved.

Split them based on one-to-many relationships. You know the business, so you know the relationships. Also, Google "third normal form" and "normalizing a database". It'll walk you through normalizing your data to get rid of issues like this.

But basically, based on what you say, you probably don't need a company table (unless the database encompasses multiple companies - not likely), but if one existed, it would potentially have many locations - so you'd put data specifically related to each location in a "Locations" table...
and so on, like this:
Conceptually, one company can have many locations.
One location can have many managers.
One manager can have many employees
One employee can have many assignments.

The underlined tables would represent tables you'd want to create.

You could be slick, since managers are also employees, if there was no extra data specific to managers you needed to store - you could simply have a checkbox "yes/no" indicating whether an employee was a manager in the employee table...
...but then you'd still need to create a matrix table possibly named "ManagerEmployees" or something of the sort, that would matrix together the manager's employee key with all their associated employee's employee keys, which would let you run your report/form on what employees were under what manager.

Hope that helps...

Thanks for all your help. I figured it out!