[Webinar] Streamline your web hosting managementRegister Today


How to create an Access Form with Batch Entry?

Posted on 2008-02-08
Medium Priority
Last Modified: 2013-11-28
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?
Question by:ojespinosa
LVL 14

Expert Comment

ID: 20849947
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.
LVL 14

Expert Comment

ID: 20856524
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.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20856934

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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Author Comment

ID: 20871901
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.

LVL 14

Accepted Solution

ldunscombe earned 2000 total points
ID: 20871917
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.


Author Comment

ID: 20872832
I think I understand what you're saying.  Let me give it a try, I'll get back.

Author Closing Comment

ID: 31429172
My apologies for the delayed response.  It took me a while to deploy the instructions.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question