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.

On Wed, 21 Mar 2007 17:26:03 -0700, Marie <(E-Mail Removed)>
wrote:

>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.

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/acc...resources.html

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

John W. Vinson [MVP]

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.

"John W. Vinson" wrote:

> On Wed, 21 Mar 2007 17:26:03 -0700, Marie <(E-Mail Removed)>
> wrote:
>
> >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.
>
> 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/acc...resources.html
>
> The Access Web resources page:
> http://www.mvps.org/access/resources/index.html
>
> John W. Vinson [MVP]
>

On Wed, 21 Mar 2007 19:28:10 -0700, Marie <(E-Mail Removed)>
wrote:

>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.

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]

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.

"John W. Vinson" wrote:

> On Wed, 21 Mar 2007 19:28:10 -0700, Marie <(E-Mail Removed)>
> wrote:
>
> >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.
>
> 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]
>

On Thu, 22 Mar 2007 06:58:18 -0700, Marie <(E-Mail Removed)>
wrote:

>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.

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]

Thank you so much. I will try that and let you know how I make out.

"John W. Vinson" wrote:

> On Thu, 22 Mar 2007 06:58:18 -0700, Marie <(E-Mail Removed)>
> wrote:
>
> >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.
>
> 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]
>

It worked! Thank you.

"Marie" wrote:

> Thank you so much. I will try that and let you know how I make out.
>
> "John W. Vinson" wrote:
>
> > On Thu, 22 Mar 2007 06:58:18 -0700, Marie <(E-Mail Removed)>
> > wrote:
> >
> > >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.
> >
> > 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]
> >