Add New Record Using Combo Box

M

Marie

I am using 2 different tables: Employee Info and Trip Details. I need to have
a blank form open to enter new information, but I would like a combo box to
choose the employees name, as there are over 300 employees. Ideally, the
first name, last name, and DOB would be populated, but the rest of the
information would still be blank to add new data. I could then just hit a
button to open the form again to add information about a different employee.
I am having a problem doing this and would greatly appreciate if someone
could let me know how to accomplish this. Thanks.
 
J

John W. Vinson

I am using 2 different tables: Employee Info and Trip Details. I need to have
a blank form open to enter new information, but I would like a combo box to
choose the employees name, as there are over 300 employees. Ideally, the
first name, last name, and DOB would be populated, but the rest of the
information would still be blank to add new data. I could then just hit a
button to open the form again to add information about a different employee.
I am having a problem doing this and would greatly appreciate if someone
could let me know how to accomplish this. Thanks.
Click to expand...

STOP.

You're misunderstanding how relational databases work!

A table of Employee Info should have last name, first name, etc.

That information should NOT be stored redundantly in the Trip Details table -
only the unique EmployeeID should.

Typically one would use a Form based on Employee Info, and a Subform based on
Trip Details, with (just!) trip information entered on the subform. The
EmployeeID would be the master link field/child link field of the subform.

Check out some of the suggestions at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]
 
M

Marie

In the Trip Details table, I added the SSN as a foreign key, I didn't add any
other employee data. The primary key in that table is Trip ID. I linked SSN
from employee info table to the SSN in Trip Details table for a one to many
relationship. I want to have the main form to hold the employee information,
and the subform for the trip information. However, I want the user to be able
to click a button in the main switchboard to open the form in add new record
view, so when the form opens, it will be completely blank. Then, I want to
have a combo box which has all the employees names listed, and when the user
clicks on the name they want, the rest of the employee fields will be
populated, but the subform will still be blank so that the user can add all
the new trip information about the employee they picked.
 
J

John W. Vinson

I want the user to be able
to click a button in the main switchboard to open the form in add new record
view, so when the form opens, it will be completely blank. Then, I want to
have a combo box which has all the employees names listed, and when the user
clicks on the name they want, the rest of the employee fields will be
populated, but the subform will still be blank so that the user can add all
the new trip information about the employee they picked.
Click to expand...

Use the Form toolbox Combo Box wizard to create an unbound combo box on the
main form - "Use this combo to find a record". You don't want or need to
create a new employee record, just navigate to the existing one.

What exactly are the trip details? Are there multiple records of details about
each trip? If so you need a Trips table: one employee --- many trips, each
trip --- many details. You may need a sub-subform.

John W. Vinson [MVP]
 
M

Marie

These are bus drivers for people with disabilities. There is 1 bus driver per
trip and many details for the trip, bus#, route#, number of passengers, is
there an aide, odometer reading before and after, etc. It's really pretty
simple. Like you said, the bus drivers are already in their own table, so a
new driver will not be added, just a new trip for each driver. But because
there are so many drivers, I would like a drop down box for the user to
select the driver's name and just add all the trip details. Thanks again.
 
J

John W. Vinson

These are bus drivers for people with disabilities. There is 1 bus driver per
trip and many details for the trip, bus#, route#, number of passengers, is
there an aide, odometer reading before and after, etc. It's really pretty
simple. Like you said, the bus drivers are already in their own table, so a
new driver will not be added, just a new trip for each driver. But because
there are so many drivers, I would like a drop down box for the user to
select the driver's name and just add all the trip details. Thanks again.
Click to expand...

This should be pretty simple then. Simply base a Form on the trip table; use
the Combo Box Wizard to create a combo box bound to the driverID, based on the
table of drivers. The combo can *display* the driver's name while storing
their ID; the user doesn't need to even see the ID.


John W. Vinson [MVP]
 
Top