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!
![the sample entry screen]()
![test selection screem]()
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!
Microsoft Access
Last Comment
esbyrt
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.
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.
ASKER
Thanks so much for the suggestions. I will try them out and get back to you ASAP.
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.ServiceCod
Thanks for having a look.
Thanks for having a look.
<The set rs=currentdb.openrecordset
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
.
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
.
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.
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
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
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
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
<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
where do you enter that quantity of samples?
test this
NBDC-be.accdb
NBDCv202-rev.accdb
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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!
< 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?
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?
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.
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.
<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..
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..
ASKER
Ah sorry. Tried the second db you uploaded and all is working perfectly. Thanks so much!
private sub cmdAdd_click()
dim j as integer, rs as dao.recordset
set rs=currentdb.openrecordset
for j = 1 to 5
with rs
.addnew
!field1= me.txtfield1
!field2=me.txztfield2
.update
end with
next
end sub