Solved

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

Posted on 2013-12-07
17
273 Views
Last Modified: 2013-12-13
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 screentest selection screem
0
Comment
Question by:esbyrt
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 

Author Comment

by:esbyrt
Comment Utility
Thanks so much for the suggestions.  I will try them out and get back to you ASAP.
0
 

Author Comment

by:esbyrt
Comment Utility
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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


.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
The same technique that you see going on in my article
http://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
0
 

Author Comment

by:esbyrt
Comment Utility
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
0
 

Author Comment

by:esbyrt
Comment Utility
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.
0
 

Author Comment

by:esbyrt
Comment Utility
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!
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
which db are you using?
did you look at the be I posted?
it added 12 records for submission 19, when I enter 15 in the quantity textbox and click on the Add record button
NBDCv202-rev.accdb
0
 

Author Comment

by:esbyrt
Comment Utility
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!
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
< 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?
0
 

Author Comment

by:esbyrt
Comment Utility
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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..
0
 

Author Closing Comment

by:esbyrt
Comment Utility
Ah sorry.  Tried the second db you uploaded and all is working perfectly.  Thanks so much!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now