Setting up form

[BigK9]

It's been a few years and I'm a little rusty. I'm am building a very small
scheduling db. I have 2 tables.............People List and Schedule Data.
People list has a listing of employees with the following fields.
Active_Badge_# (primary key) (number field)
Emp_First_Name (text)
Emp_Last_Name (text)
Department (text)

Schedule Data Table has:
Active_Badge_# (number field)
Emp_First_Name (text)
Emp_Last_Name (text)
Department (text)
Work_Date (date/time - calendar pop up)
Start_Time (date/time)
End_Time (date/time)
autonumber primary key (autonumber)

I need to be re-educated on how to set up the form so that when I enter the
employee's badge number (active_badge_#) the first and last name as well as
the department fields are automatically filled with the corresponding data.
The People List table has all of the employees already entered into it.

Thanks for any help you can give.

 

[tina]

you'll want to refresh yourself on relational design principles, to get back
in the groove. putting the employee names and dept in the schedule table
violates normalization rules. all you need is the Active_Badge_# field in
the schedules table, as a foreign key. (though i'd recommend not using the
pound sign # in your fieldnames; preferably the only the "special" character
in anything *you* name in Access should be an underscore _ otherwise, stick
with alpha characters, and numerics if you must.)

in a form, there are several ways you can *display* the employee names and
dept while working with the schedules table - without redundantly saving
that data in the table.

hth

 

[BigK9]

Thanks Tina..........It's starting to come back to me now. If I remember
correctly, I can design the form from the two tables with a one to many
relationship (People List ....to.... Schedule Data). One more
question...............What is the correct code in the "control source"
dialogue and do I use "text boxes" or "combo boxes"?

Thanks again

 

[John W. Vinson]

I need to be re-educated on how to set up the form so that when I enter the
employee's badge number (active_badge_#) the first and last name as well as
the department fields are automatically filled with the corresponding data.

No. You don't need to or want to do this.

Copying the information redundantly into the Schedule table is neither
necessary nor good design. You're using a relational database - *use it
relationally!* If you need to see the name in conjunction with schedule
information, store the active_badge_No (DON'T use # in fieldnames, it's a date
delimiter) and create a Query joining the tables.

John W. Vinson [MVP]

 

[tina]

well, i'm assuming that the employees table has a 1-to-many relationship
with the schedules table, correct? as in "one employee record may be related
to many schedule records, but each schedule record is related to only one
employee record".

standard form setup would be a main form bound to the employees table, with
a subform bound to the schedules table, with the two linked on the
Active_Badge_No field. but i'm guessing you'll need a form that is focused
more on schedules than employees. so you could base the form on schedules,
with the Active_Badge_No field bound to a combo box control. the RowSource
of the combo box would be based on the employees table. include the badge
number, employee names, and dept fields in the RowSource, make sure the
badge number field is the first in the RowSource, and make sure the combo
box's BoundColumn property is set to 1 (that's the default setting). what
you *show* in the combo droplist depends on how you want to choose an
employee - by badge number? by employee name? for a field you want to
display on the form, but not show in the combo droplist (such as the dept,
perhaps?), add an unbound textbox with the following in the ControlSource,
as

=ComboBoxName.Column(n)

where ComboBoxName is replaced by the correct name of the combo box control,
and the "n" is replaced by the index number of the column. remember that
combo box columns are zero-based, so the first column's index is (0), the
second is (1), etc.

hth

 

[tina]

(oops, meant to answer your post, not my own!)

well, i'm assuming that the employees table has a 1-to-many relationship
with the schedules table, correct? as in "one employee record may be related
to many schedule records, but each schedule record is related to only one
employee record".

standard form setup would be a main form bound to the employees table, with
a subform bound to the schedules table, with the two linked on the
Active_Badge_No field. but i'm guessing you'll need a form that is focused
more on schedules than employees. so you could base the form on schedules,
with the Active_Badge_No field bound to a combo box control. the RowSource
of the combo box would be based on the employees table. include the badge
number, employee names, and dept fields in the RowSource, make sure the
badge number field is the first in the RowSource, and make sure the combo
box's BoundColumn property is set to 1 (that's the default setting). what
you *show* in the combo droplist depends on how you want to choose an
employee - by badge number? by employee name? for a field you want to
display on the form, but not show in the combo droplist (such as the dept,
perhaps?), add an unbound textbox with the following in the ControlSource,
as

=ComboBoxName.Column(n)

where ComboBoxName is replaced by the correct name of the combo box control,
and the "n" is replaced by the index number of the column. remember that
combo box columns are zero-based, so the first column's index is (0), the
second is (1), etc.

hth