Enter Multiple Records From One Form

[Tiger6115]

I am attempting to setup a database and need some assistance/direction in setting up a form that captures/enters multiple records from one form.

We have batches of case files consisting of 25 folders each. I would like to setup the form where the user only has to enter the [User], [FileGroupID], [Examiner] and [DateAssigned] just once, but also allow for the individual 25 case file numbers in that batch to be entered. (See attached image)

So far i have created 2 tables; BatchLog table which would capture the batch details that is entered once and a CaseFiles table which would capture the individual case file numbers. I don't know if i'm on the right track or if i'm making it more complicated than it should be :banghead:.

Any help or guidance is much appreciated. Thanks in advance

 

[Uncle Gizmo]

Can you post images of your tables?

 

[Tiger6115]

Sure, It currently has no data, but here's the data structure.

 

[Uncle Gizmo]

Well, you got File group ID as a number in one place and text in another. It really wants to be well, MUST be a unique ID in one of your tables, I'd make it unique in the Batch Log Table, in fact you'd be better off using the BatchLog ID, as long as its an Auto Number. Don't have a separate FileGroup ID.

 

[Tiger6115]

You're right, i didn't think about just making the existing autonumber ID field as the unique id for the batches. I also forgot to change the data type for the FileGroupID in the CaseFile Tbl to number.

After those changes, how would i get the 25 different case file numbers to create new records in the CaseFile Table and create the single entry of the batch details in the BatchLog tbl. I've seen other examples using Insert commands, AddRecord functions and others using an append query but none of the suggestions seem to be exactly right for my particular situation.

 

[Uncle Gizmo]

>>> but also allow for the individual 25 case file numbers in that batch to be entered<<<

I assume from your question that you are entering the 25 numbers in that form you showed an image of...

 

[Uncle Gizmo]

The essence of it is, do you want the data entry as you have shown, or is that just what you thought it might look like? In other words are you open to an alternative method?

 

[Uncle Gizmo]

Are there always 25 folders? Or could there be 20 sometimes and 30 others?

 

[Tiger6115]

I created the form layout in the first pic believing that that might be the most efficient way for a user to enter in multiple case files without having to reenter the batch details over and over again. i am completely open to any alternative methods that would produce an efficient outcome.

We have a large fileroom of 15,000+ folders for this project and the designated number for each examiner is a batch (25) at a time. So the very last batch might be a little short, hell i'll put those in manually myself if i have to. LOL

(the 25 was determined by management).

 

[Uncle Gizmo]

I have in mind a solution for you. This would be a main form which carries the batch details. Underneath the batch details, you would have 3 subforms for entering/displaying the file Records.

I propose an extra table which is basically a DataTable and provides you with 1 to 25, the numbers you require for the subform. Alternatively you could use a routine to generate these 25 numbers however I've suggested a table because I can point you to a solution which is already set up using that system. If you decided that a routine would be better you could create that later. The advantage of a routine is that you could specify the total number of records, you would not necessarily be limited to 25.

Entering a new batch would automatically enter the 25 Records in the subforms.

Does that sound about right?

 

[Uncle Gizmo]

This is what I'm thinking you need but replace the Check boxes with Text Boxes.

YouTube Video;- Adding a Checklist to MS Access 0

Set of explanation YouTube Videos HERE:- Add a Check List to your DB - Videos (View Time about 18min at Double speed - YouTube Time Saving Tip! )

 

[Uncle Gizmo]

I found the time today to show how to convert the general questionnaire video lessons into the specific answer required for Tiger6115's question:-

See this YouYube Video:- Document Batches of Case Files (MS Access)