One (probably unbound) Access 2003 form to create multiple records.

Question

I've been recruited to work on a form for tracking specimens. Each specimen is associated with a subject; each specimen also has a particular slot in a 9 x 9 storage box. For ease of data entry, I think it would be best if the Access form mirrored the box itself (and the paper forms that will be used to enter data into Access): nine columns by nine rows, with each element consisting basically of a text box for the specimen ID. This is basically how I'd like it to look:

So the data entry person would essentially type in the box number and specimen IDs, then click "Create Records" to pop all of those records into existence (you can see some other stuff going on here, but that's not really important right now). I'm not really sure about the best way to code this, however. Right now, the best process I can think of is to: 1) run an insert query to create the box if it doesn't exist, 2) run an insert query to create the subject (person), if it doesn't exist, and 3) run an insert query for each specimen, hard-coding in its row and column (e.g. box_col = 'A', box_row = '1').

Note: the subject ID and specimen ID would both be parsed out of the ID field - it's goofy, not my idea, but that's how it's set up. I can handle that, though.

This is a certainly a kludge, but I'm not sure what else to do and most of what I've googled up hasn't been pertinent to multiple-record creation from a single form. Is there a better way to do this? Should I simply abandon the idea and go with a more traditional bound subform approach? I'd be very grateful for your insights and suggestions. Thanks so much.

Answer 1

This could be all done in a sub form - however, I assume for simplicity (less clicks, easier user experience, and intuition) you've designed it so that the end user sees everything he /she need to enter. There is nothing wrong with doing it this way. Once the data is all entered just have a button on the form that does the multiple inserts at once. Start at 1 and include 9 iterations each time issuing a new INSERT statement.

Once the statement has been completed I would personally put a little check mark next to each row so that if an insert succeded it would check it true, else false. You could be nifty and use a green image / red image. After it has completed the process all fields should be cleared allowing additional entry.

I don't see an issue with what you have.

Answer 2

FRIG. I just lost ten minutes of typing. This is why I don't care for web based forms. Although to be fair this is the first time this has happened on StackOverflow.

Do you really need to view the data later in the same format as it's entered. If they can just view regular subforms that's at least half the work as you no longer need to do updates of this form.

Also note that there is a lifetime amaximum of 768, if I recall correctly, controls per form. 9 x 9 x 2 is 162 so you'll be ok there. However if you decide to delete and recreate lots of cotrols you could be in trouble. If you do hit that limit I think saving the form under a separate name should reset the counter.

Note you can use the following construct to refer to controls and make life easier.

Me.Controls("abc" & Row & column) 

For example in the After Update of the type control you could use

call InsertRecords(3, "B")

sub InsertRecords(row as integer, Column as string)
....
cboTypeValue = Me.Controls("cboTypeID" & Row & Column) 
....

Answer 3

This could be used as some kind of native access controls only grid.

If someone would post an example of how to create custom unbound subform with row for each unbound record and a code to loop and save it to the tables would be just great.

There wouldn't be as much controls and it wouldn't have any limits by the numbers of fields on form.