asked on

Command to add x number of records with the same information as the current one

Hi folks. I have a database that tracks lab samples and the tests performed on them. My original design adds each sample in a continuous form with a command that opens another continuous form to select the tests to be performed on each sample. My users are finding they are receiving a hundred samples at a time from the same customer, all to be tested for the same things. Entering all the samples and test codes individually is becoming time consuming. Is there a way, maybe a command button that they can click, that would take the field data from the current record, create x number of new records, and paste the field data into the new records?
I'm thinking an append query should do it but I'm not quite sure how to implement it. In my sample form this would be just text information regarding the sample source. But in the continuous form for selecting the tests to run the number of tests and content will vary.
I've included a couple screen shots to make things clearer if I haven't explained well. Thanks!

Rey Obrero (Capricorn1)

the codes to add multiple records is something like this

private sub cmdAdd_click()
dim j as integer, rs as dao.recordset
set rs=currentdb.openrecordset("tableName")
for j = 1 to 5
with rs
.addnew
!field1= me.txtfield1
!field2=me.txztfield2
.update
end with
next

end sub

PatHartman

You will need one or more new tables depending on how complex your testing system is. I'll assume a simple four-table solution (three in addition to the one you already have).
tblItemType:- describes items that are tested
ItemTypeID (autonumber PK)
ItemTypeName
tblTestType: - describes tests
TestTypeID (autonumber PK)
TestDesc
tblStandardTests: - lists standard tests for each item
ItemTypeID (PK fld1)
TestTypeID (Pk fld2)

You would copy the rows from tblStandardTests for the type of item you are testing and append them to the actual test table. If the list of samples to be tested is computer readable, you can use that import process to import the new samples and during the process, append the tests to be performed. There really isn't any way to streamline the recording of the results. The users need to enter them somewhere but you can make the form as easy to enter data as possible.

esbyrt

ASKER

Thanks so much for the suggestions. I will try them out and get back to you ASAP.

esbyrt

ASKER

Hi Capricorn1. I tried adding the code to a command button on my select services form. I updated the !field1= me.txtfield1 !field2=me.txztfield2 with my own field names like !ServiceCode=me.ServiceCode but nothing seems to happen. And how would I pass the info to the control about how many records I want to copy the data to? This would vary with every sample set. The set rs=currentdb.openrecordset("tableName") does that refer to the table I want to paste into? The form I am trying to do this from is based on a query.
Thanks for having a look.

Rey Obrero (Capricorn1)

<The set rs=currentdb.openrecordset("tableName") does that refer to the table I want to paste into?>
yes

replace "tableName" with the actual name of the table

this part of the code

for j = 1 to 5

tells you that you are going to insert 5 records.

give more details about your db..

better upload a copy of the db

.

PatHartman

If you use a query, you don't need to specify a record count. The query will select all the records in the set and append them.

Nick67

The same technique that you see going on in my article
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_6692-Overcoming-unbound-continuous-forms-limitations-by-staging-data.html#c102563
can be of service to you here.

The idea is the same.
Create a form to allow the users to select what pre-existing data they want to mostly duplicate.
Knock that data into a temporary location
Let them edit it.
Then pound it in.

Here's the sample demo'ing what that would look like
AssignHazards.mdb

esbyrt

ASKER

Hi. Thanks again for having a look.

I have uploaded a version stripped of customer data back end and front. From the switchboard if you go to Edit Sample/Sub/Results, then pick Select Open Submission #19, this brings you to the screen for adding samples. I need to do 2 things. Create a command button or something here to add multiple samples at once. So if the customer has sent in 15 samples from the same apiary, colony id etc. (this is for bee diagnostics) the user can click a button to add 14 more with the same information but autonumbered sample IDs.

Next click edit samples and there is a continuous form to select which tests to perform on each sample. Again I would like to have a button to copy the selected tests to the next x number of samples.
NBDC-be.accdb
NBDCv202.accdb

Rey Obrero (Capricorn1)

<So if the customer has sent in 15 samples from the same apiary, >

where do you enter that quantity of samples?

test this
NBDC-be.accdb
NBDCv202-rev.accdb

esbyrt

ASKER

Right now the samples are entered one at a time in the Submissions form and the edit sample subform and then command button to Edit Samples. When we designed this database we had no idea of the volume of samples we would be getting. I tried out what you added but nothing seems to happen when I enter a number and click add records. That is definitely what I was looking for if we can get it working.

esbyrt

ASKER

Nick67 - yes I had already found your article and am working to adapt it to my database. It would be a cleaner interface with less room for error and easier for the users. Thanks for posting it!

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

esbyrt

ASKER

Hi. Looked at it again. It works perfectly the first time the button is used on a new submission, does exactly what I need it to do. But, if it's clicked again it adds one more record with the same sample number as the last one. If the submission form is closed and opened again and I try to add some samples with the button nothing happens. Thanks so much for your help!

Rey Obrero (Capricorn1)

< If the submission form is closed and opened again and I try to add some samples with the button nothing happens.>

are you using the same submission number?

please explain in details your process, from the beginning. i.e., when you received the samples, what do you do and how you do it?

esbyrt

ASKER

Okay so when a submission comes in for a customer the user goes in to edit customer and adds a submission in the subform by putting in the sample dates, then clicking open submission to add the rest of the details. This takes the user to the submission screen where you added the button on the subform. I know this isn't the greatest design, can't recall right now why I did it that way :( . The user will then click on the Edit Samples (above where you added a button) to go into the detail of the sample to add Colony ID, Apiary ID etc. Sample Data form. I need to copy that information as well (apiary ID, Colony ID) to the newly created sample IDs that your button will make.

So when the user initially adds a submission your button works. But, if they have added five and close the form then realize they should have added 50, they can't use the button again. Nothing happens when the submission screen is closed and then reopened.

Rey Obrero (Capricorn1)

<But, if they have added five and close the form then realize they should have added 50, they can't use the button again. Nothing happens when the submission screen is closed and then reopened. >

how are you re-opening the form ?

btw, did you try the second db I uploaded?

I tried it,
>1 select 19 , click open submission
>2 enter 5 in the quantity, click Add Records
>3 it added 5 records for submission 19
>4 close the form

then repeat steps 1 to 4 and it keeps adding the records..

esbyrt

ASKER

Ah sorry. Tried the second db you uploaded and all is working perfectly. Thanks so much!