Use a combo box to add records

[Sean]

I am trying to create an event database. the main form has the event
information. I want to use combo boxes to add personnel. Team Leader, Flag
1, Flag 2, and Rifle. I tried using a sub form creating a SQL statement.
PersonnelID, Rank&" &"Last Name, Team Leader.(Criteria: Team Leader = true).
The combo box works great, I don't understand why it doesn't add the record
to the event?

Please Help!!!

Thank you,

Sean

 

[John W. Vinson]

I am trying to create an event database. the main form has the event
information. I want to use combo boxes to add personnel. Team Leader, Flag
1, Flag 2, and Rifle. I tried using a sub form creating a SQL statement.
PersonnelID, Rank&" &"Last Name, Team Leader.(Criteria: Team Leader = true).
The combo box works great, I don't understand why it doesn't add the record
to the event?

Please Help!!!

Thank you,

Sean

What's the structure of your table?

A combo box doesn't "add records" to a table. It updates the value of a field
(its Control Source) to the selected value of the combo's Bound Column.

 

[Sean]

John,

My goal is to add Personnel to CG events, it sounds like I cant use the
combo box. I know I could add Team Leader, Flag 1, Flag 2, Rifle to the
color guard table. If I do that will I be able to count how many events a
person participated in?

I hope this is what you meant by the structure. I am new to this.

Relationships:
TBLUIC - 1 to TBLPersonnel - Many
TBLPersonnel - 1 to TBLQualification - Many
TBLCG - 1 to TBLPersonnel - Many


TBLCG:
CGID - PK
Title
Event Date
Event Time
Dressed / Ready
Location
Description
POC
POC #
Ext:
Attachments

TBLPersonnel:
HSTPersonnelID - PK
UICID - FK
CGID - FK
Rank
Last First
SSN
Check-in
Check-out
address
city
state
home #
Cell #
Dept Name
Supervisor
Sup #
Current Member
Coordinator

TBLQUalifications:
QUalificationID - PK
HSTPersonnelID - FK
Bugler -Y/N
Bugler Date
Funeral Rifle - Y/N
Funeral Rifle Date
Flag Folder - Y/N
Flag Folder Date
Rifle Team Leader - Y/N
Rifle Team Leader Date
Funeral Team Leader - Y/N
Funeral Team Leader Date
Color Guard Flag - Y/N
Color Guard Flag Date
Color Guard Rifle - Y/N
Color Guard Rifle Date
Color Guard Team Leader - Y/N
Color Guard Team Leader Date

TBLCommand:
UICID - PK
Command Name


Thank You!

Sean

 

[John W. Vinson]

John,

My goal is to add Personnel to CG events, it sounds like I cant use the
combo box. I know I could add Team Leader, Flag 1, Flag 2, Rifle to the
color guard table. If I do that will I be able to count how many events a
person participated in?

I hope this is what you meant by the structure. I am new to this.

Relationships:
TBLUIC - 1 to TBLPersonnel - Many
TBLPersonnel - 1 to TBLQualification - Many
TBLCG - 1 to TBLPersonnel - Many

You need *another table* - tblEventPersonnel let's say, with fields for CGID
to identify the event, HSTPersonnelID to identify the person, and a field for
the role (you may need to add a Roles table with values like "Team Leader",
"Flag 1" and so on).

Since one person might participate in zero, one *OR MORE* roles over time - or
even for a particular event - and since a given event will involve many
people, you have a many to many relationship, and need a new table to store
that information.

 

[Sean]

You need *another table* - tblEventPersonnel let's say, with fields for CGID
to identify the event, HSTPersonnelID to identify the person, and a field for
the role (you may need to add a Roles table with values like "Team Leader",
"Flag 1" and so on).

Since one person might participate in zero, one *OR MORE* roles over time - or
even for a particular event - and since a given event will involve many
people, you have a many to many relationship, and need a new table to store
that information.

John,
Thank you for your help! everything is working so far!!!!

Sean Packer