multiple records from a single form

[clickster]

I have a form where I need to enter name, address, phone number, gender, age, etc. of people. That wouldn't be a problem except that I need the form to have fields where I can enter 20 people into the form and then, with the click of a button, have it create a SEPARATE record for each person. I understand that I could easily put in a single record, save it and go on to the second record. There's a long explanation as to why I can't do it that way, but I really need to be able to do them all at once. Can anyone tell me how to do this? I assume that there would be some sort of VB or INSERT loop, but I'm not good with those without a little help. If you could point me in the right direction I could probably figure it out. Thanks in advance for any help.

 

[jimb0ne]

I'm not sure how your form is going to be set up, but the way to do it using vb would be to have something like the following... a bunch of unbound fields, and then just have code going through each set of fields that you want to create records for and create records for each. Something like the following....oh yeah, set the recordsource of the form you're using to the table you want the records to be added to.


dim rs as dao.recordset
dim icounter as integer

set rs = me.recordset

for icounter = 1 to 20
with rs
.addnew
!Name = txtname.value
!address = txtaddress.value
!phone = txtphone.value
!gender = txtgender.value
!etc etc etc
.update
.bookmark = .lastmodified
end with
next icounter

this won't really work though because you'll be entering the same values for each new record, if you told me more about the way your form is set up, what controls you're using, etc. that would help me develop a solution for you.

 

[ahmun]

You could set up a temporary Table that is a mirror of the actual table, then run an INSERT query after you have populated that temp table with all the values:

[ol][li]Create a temp table with same structure as your actual table[/li]
[li]Create a form (multi record) that you can use for data entry into your actual table[/li]
[li]create a Submit button that executes an INSERT query to copy the entries from the temp table to the actual table.[/li]
[li]Do whatever cleanup you desire (i.e. clearing the temp table's contents with a DELETE query)[/li][/ol]

If you are worried about multi-users, you can also add an extra "UniqueKey" field to the Temp table, that can store a randomly generated number so that you only insert/delete the info that you are working on.

Let me know if you want some help on specific syntax.

Earnie Eng

 

[clickster]

Thanks ahmun and jimb0ne. Ahmun, I hadn't thought about that. It sounds like an easy way to go. But how do I tell the form that any data that goes into the fields on the 3rd row of the form goes in the fields on the 3rd record on the table?

 

[ahmun]

Clickster,

is the actual position (record number) of the records important? Maybe you can describe in more detail what you are trying to accomplish.

My assumptions are that your main table would be a place where you would store many user's records, and you would be just appending more records as you go. (i.e. the first 20 people you enter will go in the first 20 slots of your table, but the next 20 goes as record 21-40)

you can always add an autonumbered Key to the temp table. So you have a field to use for defining a sort order when inserting.

you can create a query in Access, or use SQL:

INSERT INTO tblUsers ( name, address, phone, gender, age )
SELECT name, address, phone, gender, age FROM tblTempUsers
WHERE uniqueKey = 1234
ORDER BY intKey

where intKey is the autoNumber field and uniqueKey is that unique identifier I mentioned in my earlier post. This way the data is entered in the order in which it was keyed in on the form.

Earnie Eng

 

[clickster]

The postion of the records shouldn't matter at all. Once I've got data in a table, the insert shouldn't be too hard. I'll try and draw out what I'm asking below. I may not be asking the right questions.

My form needs to be similar to the one below

NAME ADDRESS PHONE AGE
John 2134 NE 23rd 4567890 32
Sally 423 Drummond 2894982 24
Jim 23 Rainmont 3848934 56

Then, once I've entered all of the people's info into the form, I need to be able to click a button and have it create a record for each person (3 records in this case) in the main table (or the temp table followed by an insert into the main table).
My real problem lies in the fact that I need all of the people info to be on the form before a record is ever actually created on any table.

 

[jimb0ne]

when you layed out your form above, would you just have 20 textboxes under each heading? I'm still unclear about the design of your form.

 

[clickster]

Actually, some of the confusion is my fault. Ahmun's suggestion of using a continuous form with a temporary table will work. It's just Monday and my mind is apparently already on vacation for Christmas. Thanks for all of the help and sorry to take up so much of your time.