Please Help...I can't add a new record to my form.

[Guest]

I am sure this question is common here. I did read through the post's a few
times, and my question was close to be answered. I am still rather new to
Access and have had two college classes and a workshop on it but don't use it
often enough to really fully understand it. My boss is having me build a very
simple database. I have two tables right now... Leader Information AND
Attendee Information. They have in common a Pack number (although the pack
number is not the primary key for either of these tables)

What I am trying to do is make a form in which I can have leader
information listed in the form header and then have attendee information in
the detail section. I need to be able to add records to attendee while
keeping the Leader information the same until I move to the next pack...Does
this make sense? and if it does can someone please help me make it work. I
have it set up right now, and I can see the one trial record I have in each
table but it will not allow me to add a new record. I have made sure that I
am in Data Entry Mode, I have the form set to continuous, and now I am at my
wits end. Please help.

Thanks in Advance from a clueless access user

 

[jahoobob via AccessMonster.com]

You will need two more table, one that has information for each workshop and
one that maintains the attendance for all workshops. You will need at least
a primary key WorkshopID and LeaderID and probably WorkshopDate and other
info. You will need at least a WorkShopID foreign key and an AttendeeID
foreign key in your attendance table. You will need a LeaderID primary key
in the Leader table and an AttendeeID primary key in the Attendee table.
Note: All primary keys should be autonumber and the foreign keys should be
Long integers. Exceptions would be the Leader adn Attendee IDs if you have
some id already like employee or memeber number.
Your main form (a single form default view) will be based on the Workshop and
you need a combobox for leader entry based on the Leader table witht he bound
column being te LeaderID.
Place a continuous subform in this main form that is based on the attendance
table in which you enter the AttendeeID for each attending the wrokshop.
Link this subform to the main form via the WorkshopIDs. This link
automatically places the WorkshopID with each AttendeeID you enter in the
subform so you can always recreate the workshop attendance records.
Hope this helps,
Bob

 

[John Vinson]

I am sure this question is common here. I did read through the post's a few
times, and my question was close to be answered. I am still rather new to
Access and have had two college classes and a workshop on it but don't use it
often enough to really fully understand it. My boss is having me build a very
simple database. I have two tables right now... Leader Information AND
Attendee Information. They have in common a Pack number (although the pack
number is not the primary key for either of these tables)

Does either table HAVE a primary key? If not, you'll have trouble. A
table without a primary key won't be updateable in queries, just for
starters.

What I am trying to do is make a form in which I can have leader
information listed in the form header and then have attendee information in
the detail section.

Not quite. You need to have the leader information displayed on the
Form - a "main form" - and the attendee information on a Subform. Use
the Subform control in the toolbar to set this up. Form Headers are
useful, but not for this purpose!

I need to be able to add records to attendee while
keeping the Leader information the same until I move to the next pack...Does
this make sense? and if it does can someone please help me make it work. I
have it set up right now, and I can see the one trial record I have in each
table but it will not allow me to add a new record. I have made sure that I
am in Data Entry Mode, I have the form set to continuous, and now I am at my
wits end. Please help.

Data Entry Mode is a bit misleading. It means that you can ONLY use
the form to enter new data - it will conceal any existing data from
view. Leave it off if you want to be able to both enter new data and
view or edit existing data!

John W. Vinson[MVP]

 

[Jeff L]

If I am reading this correctly, it sounds to me like a leader is
responsible for multiple attendees. This is called a relationship. In
your attendee table, you should have a LeaderId, which would link back
to the leader table. The Pack Number is not necessary in both tables.
I am guessing that you have linked the two tables based on Pack number.
You cannot edit your query because the fields that you are linking on
are not the primary key for either table. In other words, no
relationship has been established. If you link the tables based on
LeaderId instead, I believe you will have more success.

I would also suggest that you set up the relationship by clicking
Tools, Relationships. Add your two tables and then link them on
LeaderID. You will want to check the three checkboxes in the window
that opens when you establish the relationship.

Hope that helps!

 

[jahoobob via AccessMonster.com]

Sorry about that stupid reply. I claim old age as the reason. I got
workshop from the background paragraph!
If you are attempting to keep and attendance for each meeting, then follow my
previous post except Meeting will replace Attendance and Pack will replace
Workshp.

 

[Guest]

Hi, Thanks for such a quick response! Yes both tables have autonumber set for
the primary key.... I was not sure at the time if that would be a good idea
or not, but with there being many leaders per pack in one table AND many
attendees per leader and pack I felt it might be the only choice I had to
keep from having duplicates. Your information was right on target too! I made
a form and subform (with the subform being continuous) ALTHOUGH, I have
another issue now. My boss has just come to me and asked if we can make these
DAP so that the Leaders can update and fill out their information via the
Scout website. Can DAP's use Subforms? I don't recall it being able to but I
obviously don't recall much of what I learned in college *smiles*
THank you, Thank you, Thank you for your assistance.

 

[Guest]

Hello, This is probably another stupid question, but... Could I use the
leader name AND pack number to make a primary key? The form will be filled
out by multiple leaders from the same pack all bringing their own
"attendee's". I am sorry I sounds so clueless about this, I was just thrown
into this job. AND yes I was trying to use the Pack number as a common field,
bad idea I found out.

Thank you for your information, This is a good place to get help. Thank you
for being patient with me too.

 

[Jeff L]

Not the best idea. I would not recommend that. Your primary key could
be LeaderID and PackNumber. That will enable you to have more than one
leader per Pack.