How to create an Access Form with Batch Entry?

I want to make an Access Form that allows batch entry of MemberID for one CourseID.  I have 10 students who took CourseID: 1234  I don't want to have to reenter the CourseID 10 times and I don't want to enter the CourseDate 10 times if applicable.  Can someone explain how to create this batch entry form?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

There are a couple of ways to do this.  Is the "data" portion of the database separate?  Is it Access, SQL Server or another DB structure?

THe form itself is easy enough.  Start off with two labels, one idetified the corse number, the other the course Date.  Two Text feilds are then added to allow the user to enter the Course ID and Course date.

A third label is added to identify the Student ID.  A text box is added to allow the user to enter the Student ID.

Now, add a command button with the caption "Save".  In its _OnClick event, you enter the code to update your database with the information on the screen.  Once the Information is saved, you would set the text box that has the student ID back to blank.
A command button to close would finalise the form.
First and foremost I would reconsider your db structure and create a normalized database similar to below.

3 Tables.

Table 1  tblMembers
MemberID (Primary Index, Autonumber)

Table 2  tblCourses
withe the following fields.

CourseID (Primary Index, Autonumber)

Table 3  tblCourse_Members
with the following fields


Then you could create a form based on your tblCourse table with a subform based on tblCourse_Members table into which adding or removing members would be a breeze.  You could even use a combo box from your Members table to enter the course participants.

Jeffrey CoachmanMIS LiasonCommented:

OK, I hate to bring this up as well, but...

Why *aren't* you entering the student classes individually (at the time they sign up)?
In other words, why are you doing them all at once?

This means if a student comes in on 1/1/2008 and signs up for a class, you WILL NOT enter them into the Database until all students have signed up? (Which might be 1/5/2008 or even 1/30/2008)?

How are you "storing" or "que-ing" all of the records in preparation for this Bulk Insert?

Doing them all at once seems like it saves time, but consider this... do you have a system to alert you if too many students register for the same class? If not your "Bulk Insert" might exceed the limit.

Do you have any course prerequisites? If you do a bulk insert of 25 students and 10 dont meet the pre-req, are you also prepared to create "Bulk Delete" and "Bulk Modify code"???

What if a person drops a course before the bulk insert? How would you know?

What if on Jan 3, someone asks you: "How many students are currently enrolled in course 1234?"
Would you say: "Wait till Friday."?

Again, I know it sounds like something cool to do to save time. But that is how data entry is done.

Too many things can go wrong, forcing you to "Bulk Delete" groups of records.
I have been here for over 3 years, and I have never seen a Q asking for this particular type of update.

IMHO, it is better to enter the records one at a time and be accurate.

Can you explain?

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ojespinosaAuthor Commented:
The data is in a separate Access Database.  I feel like I should be more clear.  We are entering Course Attendance Rosters.  The attendees vary so much that pre-registration is a nightmare.  We are tracking who attended.  So I have a course on a given date and want to enter who attended and for how long.  What I was hoping for is a form that keeps the date constant, the course title constant and allows multiple entries for attendees and times.  

As far as the normalized tables:  I've got them set up very similar to what one of you layed out.

Sorry for the delayed response, I took a trip to Chicago and my wireless card went out.

If you have a form with the tblCourses as its record source and include a subform with tblCourse_Members as i'ts record source with the CourseID as the linked fields you should get what you are after.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ojespinosaAuthor Commented:
I think I understand what you're saying.  Let me give it a try, I'll get back.
ojespinosaAuthor Commented:
My apologies for the delayed response.  It took me a while to deploy the instructions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.