Add Combo Box to Multiple Tables for name updates

[Guest]

I have multiple tables that all have the field "Name" I would like to create
a combo box that will add a new name to every table field "Name" based from a
form? Any suggestions would be much appreciated.

 

[Guest]

Hi Roby,

Why do you have the same field appearing in multiple tables that will store
the same data? This usually indicates an issue with the design of your
tables.

Damian.

 

[Jeff Boyce]

Roby

First, if the actual title of the field is "Name", you've managed to confuse
both Access and yourself ... this word (and others) is a "reserved word" in
Access. When Access sees it, it thinks about one thing, but when you see
it, you think about something else.

Second, you think about MANY somethings else -- from your description, you
have multiple tables with the same field. That design might be necessary if
you were building a spreadsheet... Access is a relational database and you
will only make MUCH more work for both yourself and Access if you try to
feed it 'sheet data.

If you post back a description of your tables and the fields (not all of
them, just enough to get a sense of what you are working on), the 'group's
readers may be able to offer an approach that is easier for both you and
Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Well I have a table for each department of my industry and the same employees
work in each department during different times of the year. Therefore I would
like to be able to keep the tables different so I can create forms with a
control source of each department.

This allows the employees to insert information with the department and date
already added to the form. Saves them from adding this information and it
doesn't get added incorrectly. I have a conituos form for each department
with the department name on top and the date already added. But I need a
combo box in each of these forms that will update the " Name" field in all of
the tables

 

[Guest]

Thankl you for your help everyone. I have changed my set up so that now I
only have one table to draw from. The field name is now "employee name" and I
have just created different forms for each department. All I need to
accomplish now is a way that will update the "employee name" combo boxes on
each form every time I get a new employee. (Rather than manually going in and
typing it in the properties.) Your help is greatly appreciated, please excuse
my knowledge as I am learning more and more as I train on access!

Thanks!

 

[Duane Hookom]

Your combo box should have your employee table as its Row Source. This would
make any updates un-necessary.

BTW: you should kick your field naming up a notch and NOT allow spaces in
field names.

 

[Guest]

Sorry, I just need to be able to create a combo box in a form that will
contain all of my employee names. Whenever I get a new employee and need to
add that employee to the combo box, how can I or the user do this? I know I
need to store this information in the "employeename" field but I am not sure
how to do that. Do I need to do it through a query?

P.S. thanks for the tip on the spaces. Did not know that.

 

[Guest]

Hi Roby,

Set the data source for your combo box to be something similar to the
following (remember to insert your own table/field names).

"select EmployeeName from tblEmployees order by EmployeeName"

Hope this clears it up.

Damian.

 

[Guest]

Thank you everyone that worked great. This reformation of the database
actually gives me many many more query options as well. thanks you for all
the help!

 

[John Vinson]

Well I have a table for each department of my industry and the same employees
work in each department during different times of the year. Therefore I would
like to be able to keep the tables different so I can create forms with a
control source of each department.

If you're assuming that you must have a separate table to have a
different control source, your assumption is WRONG. So is your table
design.

You can have a Departement field in the single table, and use a Query
selecting one department to display that department's employees.

This allows the employees to insert information with the department and date
already added to the form. Saves them from adding this information and it
doesn't get added incorrectly. I have a conituos form for each department
with the department name on top and the date already added. But I need a
combo box in each of these forms that will update the " Name" field in all of
the tables

Storing the name redundantly is simply incorrect design.

If you have a many to many relationship between employees and
departments, you need (at least) three tables:

Departments
DepartmentID
DepartmentName
<other info about the department>

Employees
EmployeeID
LastName
FirstName
<other bio data>

EmployeeAssignments
EmployeeID <who's assigned>
DepartmentID <where they're working>
StartDate
EndDate
Position
<other info about this employee in this department>

You can still have a Form based on Departments, with the department
name; it can be filtered so only one department can be shown. On the
Subform (based on EmployeeAssignments) you would have a combo box
allowing the employee name to be selected.

The end result is that you get what you asked for - the name
associated with each department; a history of all the employee
assignments; no need to correct the spelling of the name in all the
different departments, or to change it if the person's name changes; a
smaller and more efficient database; and no need for code to store
your data redundantly. Would you like some other arguments for
properly normalizing your data?

John W. Vinson[MVP]