Add Multiple New Records to a Existing Table (1 Viewer)

Hi,

I have a registration form and a field on it for the user to add a number, could be any amount, probably usually between 1-50 but could be as high as a few hundred.

The table has an auto number field which is used as the sample number for the record. What I want to do is to add a number in the field on the form (Say 12), click on a button and then the database adds that number of records (12 records) to the table to take the next x number (x12) of the auto numbers (sample numbers). The fields will essentially be blank and updated from the Holding table later.

And then I want it to add those new x (12 records) to a holding table to use elsewhere.

Form: Sample_Registration_Form
Field: Number_Of_Samples
Table: Sample_Results
Holding Table: Registration_Holding

Anyone help?

Cheers
Jdlewin1

1. What's the holding table accomplish for you? Why not just create the recrods in your table and have a new field in that table that designates if those records are on hold or not?

2. What have you tried to accomplish what you have laid out? How can we specifically assist you?

Thanks for the response.

I know the holding table isn’t the best way to do it but it’s what I’m used to. I basically use that holding table to house the data and then I auto generate a receipt with the info registered which is expected and saved in odd and then attached and emailed to an email address.

It was the cleanest way I knew how to do it, if there’s a solution for not using s holding then I’m happy to try a better approach.

I haven’t started looking yet as I don’t know where to start on getting the system to add the records.

It sounds like you want to create a RecordSet for your table and perform an AddNew and Update in a For loop, using Me.New_Record_Count (or whatever you call your filed) as the limit.

That sounds about right, how do you go about doing that!?

The code is basically

However, the approach sounds goofy. You need to explain more. There should be no reason for a "holding" table and making dummy records. I would fix that instead of trying to add dummy records.

jdlewin1,

Can you tell us about the business behind your proposed database/approach?
Along the lines of a "typical day at the office"?

We need to know WHAT before deciding HOW.

And I don't like the idea of creating records in advance awaiting some event, but I don't know your business.

MajP said:

The code is basically

Code:Copy to clipboard

Dim I as integer
dim numRecords as integer
dim strSql as string
numRecords = me.somecontrolvalue
for I = 1 to numRecords
  'insert query goes here
  strSql = "INSERT into SomeTable (fieldName1,...) Values (" & someValue & ", " & someValue2 & ")"
  currentdb.execute StrSql
 next i

Click to expand...

I find INSERTs confusing if there are a lot of columns (matching te column to the value can be tricky). Is there a good reason not to use a RecordSet?

Ok so we receive samples from client (around 15,000 per year) on receipt each sample is registered into the database. The main table has an auto number field which is the unique sample number (eg: 00001001, 00001002, 00001003 and so on). The main registration form is used to enter the sample details into the system but I need it to take the next auto number from the main table so that they continue to be uniqu and in one numbering format. That sample number then needs to be written in the label of the sample and that number is then used throughout the analysis process.

Once the samples are received and registered the user clicks a button and they get added to the main table at that point they are regeatiered. The samples that have been registered are then sent to the client in a registration receipt. So the database will save that new sample list and generate a PDF and excel sheet of the sample info which is then automatically sent to the client.

As the sample goes through the labs the results are logged against the sample number and then thy can be assessed and reported over the next few days.

They way the system currently operates to get the correct sample numbers is to use a holding table to generate the same numbers, the registration / data entry is done in this table by a sub form and then it’s automstically added to the main table. The pdf / excel reports are generated from the holding table so only the new samples received are included.

At the minute the user can add as many new samples (records) as they want but if they add an extra line it generates the next auto number and then has to be used otherwise the auto number misaligns and doesn’t work. So I want the user to specifically state the number of samples to be registered and then the sub form to display that many new samples with the sample numbers there already so that they can only add that many into the system and can not generate additional sample Numbers by mistake. If more samples are received then they are registered separately and the steps repeated.

The main table is used throughout the process by a number of users to enter info against the samples so the holding table was used to make sure the main table was not open all the time by the registration database.

I hope that makes more sense and gives enough info...

I'm not sure why you add the records into a holding table and then transfer them later. Is there some "balancing" that must be done to ensure that everything adds up?

The sequence should be two part. One for the parent (group) record which can be an autonumber. The child table can use a user generated sequence number so you can control when to generate the number and avoid wasting numbers if you are not going to insert the record.

Not sure that helps me here.

The sample number is the auto number and must be auto generated.

The holding table is used due to the reasons given in my previous post. If I do t need to use it then great but I can’t have the main table utilised all the time by the registration as this is open all day and don’t want to cause issues with it being open when other users are entering results and running reports etc. The holding table was the easiest way to have the main table open as little as possible.

All I want to be able to do is take the next x records from the main table and either add them to the holding table or display then in the form so the sample information can be added against them.

Not really. An insert is more efficient, but unlikely to have any impact in what you are doing. But since you are adding a blank record, I cannot imagine you would add more than one field. Basically just replace the insert with the RS.addnew. Still, like everyone is saying, try to find a better general approach to this. I am sure you can do this easily without the holding table idea.

I agree that you should simply add these as new records to your main table, I would simply set a field on the new records to something that identifies that it needs additional information, maybe set a FK field value to 0 that would make it stick out like a sore thumb?

The most efficient way to do this would be to Insert Into Query that uses a join to a "numbers" table that you include to add that number of records, this avoids the use of a loop.

Hi Maj,

Which bit of that code do I need to replace with The RS.New?

Cheers

Again, like everyone else, let us help you come up with a better way. But if you insist here is a generic function to add x records and set one fields value.


You would call it like this
InsertRecords "Table2","Cost",15,0

If not clear that creates 15 records in Table2 and sets the Cost field to 0.