Multiple Record Data Entry Form in Access

Question

Hello, I am trying to create an Access form which allows multiple records to be entered and inserted on one form.

Simple single table with 2 fields Entrantnbr and Awardnbr. I created the table with the data validation relationships to other tables.

The validation rules work well for one record at a time. I figured out how to enable the "new record" every time.

However, I'd like the user to enter the Entrantnbr once and allow up to 10 different Awardnbrs, inserting a record for each (repeat Entrantnbr).

I am a novice, so I have no concept of VB programming so I need to use the wizards or design options. Thank You For Your consideration.

Answer 1

Does the awards table into which you are entering the data reference a separate entrants table?  It  should.  Also, can each award be entered by more than one entrant?  If not the relationship type is one-to-many so you need tables in broad outline like this:

Entrants

....EntrantID  (PK)

....FirstName

....LastName

....etc

Awards

....AwardID  (PK)

....Award

....EntryDate

....EntrantID  (FK)

Otherwise the relationship type is many-to-many, so the model would be:

Entrants

....EntrantID  (PK)

....FirstName

....LastName

....etc

Awards

....AwardID  (PK)

....Award

and to model the relationship type by resolving it into two one-to-many relationship types:

AwardEntants

....AwardID  (FK)

....EntrantID  (FK)

....EntryDate

The primary key of the last table is a composite one of the foreign keys AwardID and EntrantID if each entrant can enter the same award only once.  If, on the other hand, each entrant can enter the same award more than once on different dates the primary key is a composite one of all three columns.

For data entry the interface should, in both scenarios be, as Duane says, a parent form and subform.  The parent form can be based on Entrants or on Awards depending on how you wish to enter the data.  In the first scenario the subform would be based on the other table.  In the second scenario it would be based on AwardEntants.

You'll find an example of a basic many-to-many relationship type as ParentActivities.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes forms/subforms for entering activities per parent or parents per activity.

Answer 2

Hello, I'm sorry for the long delay to respond. I understand the information you provided. I was able to access your Parent Activities file. my situation is as you stated "The primary key of the last table is a composite one of the foreign keys AwardID and EntrantID if each entrant can enter the same award only once."         

I set the relationships, one to many with no problem.                                                                                                   What I can't seem to accomplish is the setting of the 2 foreign keys EntrantID and AwardID. It Seems my only option is one primary key. Any advice? Thank You Kindly.

Answer 3

You can use a main form with a control to select the entrant. Then add a subform to entere the awards. Set the Link Master/Child properties of the subform control to the Entrantnbr field.

Answer 4

Each EntrantNbr can submit up 1-10 awards. One entrant might submit only award 22. Another entrant might submit awards 33,44,55 and 78. The data entry person does not have time to type the entrantnbr for each record individually, so I was hoping to create a form which allows him to type the entrantnbr once and the awardnbrs individually. Hope this helps and THANK YOU

Answer 5

Can you provide an example of your records before and after your requested procedure? What do you mean by "up to 10"? How would the procedure know whether there should be 3 or 9 or 7 or 10?