Solved

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

Posted on 2013-12-07
17
294 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39703744
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 35

Expert Comment

by:PatHartman
ID: 39703750
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
ID: 39707017
Thanks so much for the suggestions.  I will try them out and get back to you ASAP.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:esbyrt
ID: 39712764
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39712830
<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 35

Expert Comment

by:PatHartman
ID: 39713025
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
ID: 39713030
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
ID: 39713078
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39713105
<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
ID: 39713212
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
ID: 39713214
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39713215
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
ID: 39714800
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39714866
< 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
ID: 39715582
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39715600
<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
ID: 39717496
Ah sorry.  Tried the second db you uploaded and all is working perfectly.  Thanks so much!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

831 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