?
Solved

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

Posted on 2013-12-07
17
Medium Priority
?
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 38

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 38

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 2000 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 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