Multiple Record Entry Using a Form

  • Thread starter Thread starter adurrschmidt
  • Start date Start date
A

adurrschmidt

Access Monsters,

I have a table where I need to enter many records containing the same
information for each field (except for the unique primary key). The size of
each recordset can vary from day to day.

I would like to be able to enter an interger into a box on a form (i.e. 345)
that represents how many records I need that day. Then enter into each
respective box on the form the data that coincides with those particular
records fields (all of the records on a given day will have the same fields.
Hit a "submit records" button and have access automatically enter 345 records
all with that same data while maintaining their unique IDs.

I am a beginner with Access but am willing to work with macros or code or
whatever is necessary to make this work. Thank you!

Andrew
 
I'm sorry, but I have to ask "Why?" (as in "Why do you want to create
hundreds of identical records?"). If you have such a requirement, your
database may not be optimally designed.
 
Douglas,

We collect or recieve "x" amount of samples per day, each sample gets a
unique number. I have the following data in a table called sample entry.

Year, ProjectID, SampleID, SampleType, CollectDate, RecieveDate, Technician

This data should be entered as the samples are collected or recieved.

After running multiple tests on these samples they are broken down into
subsamples i.e. Sample 1A, Sample 1B, Sample 1C, etc...all with unique
attributes. I have these subsamples in another table with the fields that
relate to them. So the initial table will have hundreds of identical records
but later as more tests are run on the samples they become more and more
unique.

It is very possible that the database isnt designed optimally, I've been
tasked out to design this database and teach myself access.. so I've been
learning at the same time I'm designing. Any help you can offer would be
greatly appreciated.
I'm sorry, but I have to ask "Why?" (as in "Why do you want to create
hundreds of identical records?"). If you have such a requirement, your
database may not be optimally designed.
Access Monsters,
Click to expand...
[quoted text clipped - 13 lines]
Andrew
Click to expand...
Click to expand...
 
Strikes me that you don't need individual records until there's something
different about them. Initially, you need one record that indicates you
received n samples. As you work, you'd introduce new rows once you've
changed something about them.

However, if you want to continue your way, one thing you can do is create
two tables: a table that has a single row in it: your "standard" entry for
the samples, and a table that has a single numeric column in it. For the
sake of argument, I'll assume the first table is named StandardSample, the
second table is named Samples, and the field in the Samples is named
SampleCount.

Insert rows with the numbers 1, 2, 3, up to whatever you consider a
reasonable maximum into this second table. You can quickly create the table
in Excel and import it into Access, or you can use code like

Sub PopulateSamples()
On Error GoTo Err_PopulateSamples

Dim dbCurr As DAO.Database
Dim lngLoop As Long
Dim strSQL As String

DoCmd.Hourglass True

Set dbCurr = CurrentDb
For lngLoop = 1 To 1000
strSQL = "INSERT INTO Samples(SampleCount) VALUES(" & lngLoop & ")"
dbCurr.Execute strSQL, dbFailOnError
Next lngLoop

End_PopulateSamples:
Set dbCurr = Nothing
DoCmd.Hourglass False
MsgBox "All done"
Exit Sub

Err_PopulateSamples:
MsgBox Err.Number & ": " & Err.Description
Resume End_PopulateSamples

End Sub

Now, create a query. Drag both StandardSample and Samples into the query,
but don't join them. Drag the fields from table StandardSample into the
query grid, and drage SampleCount from table Samples. Uncheck the box under
SampleCount, and put a criteria of <= [How Many Samples?].

Run that query. You'll get prompted to enter a value. Put, say, 10, and hit
Enter. You'll see the resultant query will return 10 identical rows. Do it
again and enter a different number. You'll see that many rows in the result.
Convert that query to an Append Query (under Query on the menu bar when the
query's open in Design mode), and you'll be able to populate your actual
table with any number of rows anytime you want. Just make sure that you
don't have a field corresponding to the Autonumber field in your real table
in StandardSample.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


adurrschmidt said:
Douglas,

We collect or recieve "x" amount of samples per day, each sample gets a
unique number. I have the following data in a table called sample entry.

Year, ProjectID, SampleID, SampleType, CollectDate, RecieveDate, Technician

This data should be entered as the samples are collected or recieved.

After running multiple tests on these samples they are broken down into
subsamples i.e. Sample 1A, Sample 1B, Sample 1C, etc...all with unique
attributes. I have these subsamples in another table with the fields that
relate to them. So the initial table will have hundreds of identical records
but later as more tests are run on the samples they become more and more
unique.

It is very possible that the database isnt designed optimally, I've been
tasked out to design this database and teach myself access.. so I've been
learning at the same time I'm designing. Any help you can offer would be
greatly appreciated.
I'm sorry, but I have to ask "Why?" (as in "Why do you want to create
hundreds of identical records?"). If you have such a requirement, your
database may not be optimally designed.
Access Monsters,
Click to expand...
[quoted text clipped - 13 lines]
Andrew
Click to expand...
Click to expand...
Click to expand...
 
Douglas,

Thank you. I'll try to work with both of your ideas and see which one is
best for us. I appreciate your help immensely!

Andrew
Strikes me that you don't need individual records until there's something
different about them. Initially, you need one record that indicates you
received n samples. As you work, you'd introduce new rows once you've
changed something about them.

However, if you want to continue your way, one thing you can do is create
two tables: a table that has a single row in it: your "standard" entry for
the samples, and a table that has a single numeric column in it. For the
sake of argument, I'll assume the first table is named StandardSample, the
second table is named Samples, and the field in the Samples is named
SampleCount.

Insert rows with the numbers 1, 2, 3, up to whatever you consider a
reasonable maximum into this second table. You can quickly create the table
in Excel and import it into Access, or you can use code like

Sub PopulateSamples()
On Error GoTo Err_PopulateSamples

Dim dbCurr As DAO.Database
Dim lngLoop As Long
Dim strSQL As String

DoCmd.Hourglass True

Set dbCurr = CurrentDb
For lngLoop = 1 To 1000
strSQL = "INSERT INTO Samples(SampleCount) VALUES(" & lngLoop & ")"
dbCurr.Execute strSQL, dbFailOnError
Next lngLoop

End_PopulateSamples:
Set dbCurr = Nothing
DoCmd.Hourglass False
MsgBox "All done"
Exit Sub

Err_PopulateSamples:
MsgBox Err.Number & ": " & Err.Description
Resume End_PopulateSamples

End Sub

Now, create a query. Drag both StandardSample and Samples into the query,
but don't join them. Drag the fields from table StandardSample into the
query grid, and drage SampleCount from table Samples. Uncheck the box under
SampleCount, and put a criteria of <= [How Many Samples?].

Run that query. You'll get prompted to enter a value. Put, say, 10, and hit
Enter. You'll see the resultant query will return 10 identical rows. Do it
again and enter a different number. You'll see that many rows in the result.
Convert that query to an Append Query (under Query on the menu bar when the
query's open in Design mode), and you'll be able to populate your actual
table with any number of rows anytime you want. Just make sure that you
don't have a field corresponding to the Autonumber field in your real table
in StandardSample.
Douglas,
Click to expand...
[quoted text clipped - 26 lines]
Click to expand...
 
Back
Top