Create complex data entry form

[John Hawker]

I need help designing/creating a complex data entry form. I am developing a
training db to capture individual training attendance. The problem is
recording mutiple people and multiple topics at the same time (i.e.; 1
employee record for each topic--regulatory reasons). I have the following
tables: Employees, Events (sessions), Topics, and Attendance (join table
containing keys for Employees, Events, and Topics.

I was thinking of a main form to create the session with 2 independent
subforms for attendees and topics. Each subform would be populated by a
pop-up(?) form based on the Employee/Topic tables and selected items would be
copied to the subforms. A command button, when clicked, would iterate
through the 2 subform entries, creating individual training records using
EventID/EmpployeeID/TopicID, and post these to the Attendance table.

Question 1: Is this the best design? Or is there something simpler/easier?

Question 2: How do you code the OnClick event?

I would appreciate any insight you can provide.
John Hawker

 

[Steve]

TblEmployee
EmployeeID
Employee Data Fields>

TblTopic
TopicID
Topic

TblEvent
EventID
EventDate
TopicID

TblEventAttendance
EventAttendanceID
EventID
EmployeeID

1. Use a form to enter employees in TblEmployee
2. Use a form to create the list of topics in TblTopic
3. Use a form to record events in TblEvent. Use a combobox on this form
to select the topic of each event.
4. Use a form/subform to record event attendance. Base the main form on
TblEvent so you can select the event. Base the subform on TblEventAttendance
to record each employee who attended the event. Use a combobox on the
subform to select the employee in each record.

Steve

 

[John Hawker]

Thanks for the reply, Steve. I started out with this idea but ran into the
problem that there would be at least 7 and as many as 12 topics in the most
common session type. And, not everyone at the session would get all of the
topics.

For that reason, I thought of putting the TopicID in the Attendance table.
From a design perspective, the training record shows "this person took a
particular topic at a specific session."

I would appreciate any insights on design alternatives.

 

[Steve]

Your saying an Event (Session) may have multiple topics and employees can
attend one or more topics at a session. You have a one-to-many relationship
between Event and Topic so you need another table. Further, since an
employee may attend one or more topics at an event, TblEventAttendance needs
to be replaced with a different table.

TblEmployee
EmployeeID
Employee Data Fields>

TblTopic
TopicID
Topic

TblEvent
EventID
EventDate

TblEventTopic
EventTopicID
EventID
TopicID

TblEventTopicAttendance
EventTopicAttendanceID

EventTopicID
EmployeeID

1. Use a form to enter employees in TblEmployee
2. Use a form to create the list of topics in TblTopic
3. Use a form/subform to record event topics. Base the main form on
TblEvent so you can select the event. Base the subform on TblEventTopic
to record each topic at each event. Use a combobox on the
subform to select the topic in each record.
4. Use a form/subform-subform to record event-topic attendance. Base the
main form on
TblEvent so you can select the event. Base the subform on TblEventTopic so
you can select a topic at the selected event. Base the subform of the
eventtopic subform on TblEventTopicAttendance to record each employee who
attended the topic of the event. Use a combobox on this subform to select
the employee in each record.

Steve

 

[John Hawker]

Here are the tables I have:

TblEmployee
EmployeeID
Employee Data Fields>

TblTopic
TopicID
Topic

TblEvent
EventID
EventDate

TblAttendance
AttendanceID
EventID
EmployeeID
TopicID

The reason being that people attend whatever topics interest them and/or
ones they need to attend; it's a professional conference. Also, while not an
issue in this discussion, some of these topics must be done every year so
it's important to track topic attendance individually. And that may be what
got me into trouble here.

Got any ideas?

====

 

[Steve]

We're close to being on the same page. In your tables. you are missing a
table that lists the topics for an event. My TblEventTopic does that:
TblEventTopic
EventTopicID
EventID
TopicID

In your TblAttendance, you have to record both the topic and employee in
each record. In my tables the topic is automatically recorded via the
form/subform construct. The linkmaster and linkchild properties are
EventTopicID that ties the employee to a specific event and a specific topic
of that event.
TblEventTopicAttendance
EventTopicAttendanceID
EventTopicID
EmployeeID

Try this table design odf a main form and a subform within a subform and you
will see how it works.

Steve

 

[John Hawker]

OK, let's see if I understand this correctly. A main form for the Event with
a subform for Employees. Within the Employees subform is a second (nested)
subform for topics attended> Did I get that right?

John

 

[Steve]

No, see #4 of my previous response. There is no employee subform. A main
form for the Event with
a subform for Topics in the Event..Within the Topics subform is a second
(nested) subform for employee attendance at each topic.

Steve