Solved

Help with code to copy and paste with a loop in VBA for MS Access 2013

Posted on 2014-09-29
21
7 Views
Last Modified: 2016-06-26
Good Afternoon
I’m writing to seek assistance with a project in MS Access 2013. I have a sample database that records imaging equipment that is shipped to different study sites, but managed under one study. One study can have several different study locations. So, my database is a log for each system shipped to each study site. Each site would receive the same hardware configuration. I need a solution to automate this in VBA Code. I've tried an update query, but that will not work in this situation. I need to tell the code how many copies of a system a study requires.

The work-flow currently allows the project manager to create the "study" for a given sponsor. He/She will then create the "system" that will be used for that study. He/She will select hardware items from a drop down list and will create the "system". We want to then copy this initial entry however many times is required by study. I am looking to accomplish this task by have a button either at the system level or study lever that will execute this task.

To assist, I have attached a sample of the database in question. Once opened, you will arrive at the Study Page. Here you can see the systems created per study.

Many thanks in advance to anyone who can help.
Kind Regards,
gonzalezllg
SampleSystemBreakdown.accdb
0
Comment
Question by:Lenny Gonzalez
  • 11
  • 9
21 Comments
 
LVL 84
ID: 40350913
So the components are all the same for each Study Location?

Do you need to collect data about the different components of the System at each Study Location? Or do you just need to identify that the equipment is at a specific Study Location for a specific period of time?

I ask because if you do NOT need to collect data about the different components, then you really don't need to duplicate those items. Instead, you'd store that data one time, as you're doing now, and then just store the StudyLocations, and relate all that together with the Study.

If you DO need to collect data, then perhaps you'd add another table where you could join the StudyLocation + StudyComponents, and then store details about that in a table (perhaps named StudyComponentDetails).
0
 

Author Comment

by:Lenny Gonzalez
ID: 40350948
Yes, details about the components at each site will need to be recorded. Once all of the Systems are entered into the database, we then need to record each serial number delivered to each site. (I have a serial number field on the systen details form) This way, management can report that will identify which site has what serialized equipment for support purposes.

Entering the serial numbers will remain a manual process handled by the inventory Mgr. He/She will enter the serial numbers per system as a Quality Control Check before the shipment is scheduled. We are just looking for a solution to copy the "system" and the "systemdetails" assigned to it by a value entered in the database. Some study's can have ten, twenty and in some cases 300 systems that ship domestic and internationally. We definitely want to automate that process by code if at all possible.
0
 
LVL 84
ID: 40350968
Perhaps I misunderstand you - do you mean that a Study can have more than one "package" of components, each of which will ship to a specific location? So PackageA will go to Location1, PackageB will go to Location2, and so on?

Or you do you mean that each study will have a SINGLE package, and that package would be shipped to multiple locations - so it'd go to Location1, then Location2, then location3 and so on?
0
 

Author Comment

by:Lenny Gonzalez
ID: 40350980
Sorry, to clarify....your first scenario is what we are trying to record in the database. Study A, will have multiple packages. Package A will go to location 1 and Package B will go to location 2, so on and so forth. So each study will have a number of systems out in the field. And, of course, each study is different in the number of packages it is requiring us to ship. So Study A can have ten "packages", while study B can have 300. I’m trying to automate the packages by copying the package and its packagedetails by a number i can enter in a calculation.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40350989
I see ...

You can insert records using standard SQL and VBA procedures. I'm assuming that the table named "SystemDetailsT" is the table that stores those details? If so, then you could do this:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM SystemDetailsT WHERE SystemsID=" & Me.YourSystemsID)
'/ the user would enter the number of systems in a textbox
Dim i As Integer
For i = 1 to Me.txNumOfSystems
  Currentdb.Execute "INSERT INTO SystemDetailsT(ProductID, Qty) VALUE(" & rst("ProductID") & "," & rst("Qyt") &")"
Next

You really don't need to store StockCode or Description, unless those would also be changed for each "package".
0
 

Author Comment

by:Lenny Gonzalez
ID: 40350999
Thank you for your time and assistance.
I have a few questions before I implement this solution in a test environment. Do I associate this code with a button to execute on the "System" Form where I am entering the system details for the first time?

Second, to i add a text box to the same form where the user can enter the number of copies required?

thanks again.
0
 
LVL 84
ID: 40351074
You could use a button to fire this off, and yes, I'd add a Textbox on the form where the user could enter the number of copies. The code I provided refers to that textbox here:

For i = 1 to Me.txNumOfSystems
0
 

Author Comment

by:Lenny Gonzalez
ID: 40352319
First off, thanks for the clarification.
Ok, here's what I've done so far. I added a text box to the "Systems" labeled "txNumofSystems". Next, I added a button, labeled "AutomateSystems", to fire the code off on the  "Click" event. I then created a new package on the "Systems" form. I entered a quantity of 20 in the text box and clicked the Execute button. I get the following Error:

Compile Error
Method or data member not found.

When I click ok to see the Code, The following is highlited in yellow:
Private Sub AutomateSystems_Click()

And the next section is in blue:
.YourSystemsID
that is from the 2nd line of code.

Should I attach the sampple DB to see this in action?

LG
0
 
LVL 84
ID: 40352326
Yes ... please compact it first, and then attach it here. Be VERY specific as to exactly what we do to recreate the error.
0
 

Author Comment

by:Lenny Gonzalez
ID: 40352389
OK....
Ive cleaned out the test data i was playing around with. Here goes the workflow:
-From opening form, you will be on BillingCodeID #40, SponsorName "a Laboratories.

-Click "Add Shipment" Button.

-This will open the form labeled "SystemsFSysDetailQ_SandR_Rev1B.

-The BillingCodeID on this form is defaulted to the opening form.

-Once opened, Tab till you through OpenProducts, Close, Save, Refresh, Shipped.

-You'll arrive on Date Allocated.

-Enter any date then Tab passed SystemsID, BillingCodeID.

-You will now be on System #. Select System #01 from the drop down list.  Then Tab.

-You will now be on Center Code. This is the Site ID. We use Alphanumeric Codes like NJ-Site115 or NY-Site201. Its a text field so feel free to enter something in that format. Then Tab to move to notes and Tab to moce to System Details.

-Next, click on the DropDown box next to the "Add Product" Button found in the form footer.

-Select any com,bination of products. We ususally Ship a Camera, Lens and Flash. For Example, select code 3042, 220 and 16.

-Once the codes have been selected, click in the "# of Systems" text box. Here, enter any random number. I entered 20.

-And Finally, click the button labeled "AutomateSyst"
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:Lenny Gonzalez
ID: 40352592
0
 

Author Comment

by:Lenny Gonzalez
ID: 40353063
After some tweaking,  & Me.YourSystemsID was changed to Me.SystemsID)

That seems to be accepted by the Debug Application. Now onto the next problem. Once I made that change, I ran another test example. I now get the following pop up notice:

Run-Time Error '3265"
Item not found in this collection

When debug is selected, the following line of code is highlighted in yellow:

CurrentDb.Execute "INSERT INTO SystemDetailsT(ProductID, Qty) VALUE(" & rst("ProductID") & "," & rst("Qyt") & ")"

Any idea why?
0
 

Author Comment

by:Lenny Gonzalez
ID: 40353298
Upon further inspection, Qty i misspelled. After the correction, I now get:

Run-Time error 3134
Syntax Error in INSERT INTO Statement

And the following line of code is highlighted:
 CurrentDb.Execute "INSERT INTO SystemDetailsT(ProductID, Qty) VALUE(" & rst("ProductID") & "," & rst("Qty") & ")"
 

Can anyone tell me where the Syntax is off?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40353316
VALUE should be VALUES
0
 

Author Comment

by:Lenny Gonzalez
ID: 40353360
OK.......With all of the updates to the code, I click the button to execute the code and nothing happens. Here is the code after the updates:

 [Private Sub AutomateSystems_Click()

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM SystemDetailsT WHERE SystemsID=" & Me.SystemsID)
'/ the user would enter the number of systems in a textbox
Dim i As Integer
For i = 1 To Me.txNumofSystems
  CurrentDb.Execute "INSERT INTO SystemDetailsT(ProductID, Qty) VALUES(" & rst("ProductID") & "," & rst("Qty") & ")"
Next

End Sub

Once executed, nothing happens. No additional records are created.
0
 
LVL 84
ID: 40353421
Does your recordset return any records? To check that, set a Breakpoint on the "For 1 = 0 blah blah" line, and run the code, then type this in the Immediate box:

?rst("ProductID")

And press Enter. Do you see anything in the Immediate window? If not, your Recordset is not populated, so you won't insert anything.

Is ProductID a Text or Numeric value?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40353441
Change the code to this:

Dim rst As DAO.Recordset
 Set rst = CurrentDb.OpenRecordset("SELECT * FROM SystemDetailsT WHERE SystemsID=" & Me.SystemsID)
 '/ the user would enter the number of systems in a textbox
 Dim i As Integer
 For i = 1 To Me.txNumofSystems
   CurrentDb.Execute "INSERT INTO SystemDetailsT(SystemsID,ProductID, Qty) VALUEs(" & Me.SystemsID & "," & rst("ProductID") & "," & rst("Qty") & ")"
Next

Open in new window



Also, it's not populating StockNumber and Description, so you'd need to add that in - or do it the right way, and join the Products table to show those details.
0
 

Author Comment

by:Lenny Gonzalez
ID: 40354636
Good Morning
Thank you for the update. We are getting close. The updated code appears to be working......slightly.
I once I execute the command, I get no indication that it has completed its task. So, I closed the form and look at the systemdetail table. And wa-la....the code has made additional entries. However, there are a few issues.

Heres are the problems:
-If I add multiple lines on a package, the code is only replicating the first line. So, If add a camera, lens and flash. The code is only replicating the first line assigned to the system ID.

-Next issue pertains to the actual systemID. I want the code to make a copy of the items in the package, and paste them while also assigning them the next SYSTEMID number. Currently, the code is replicating the data, but assigning it to the same systemid.

To recap, one study will have multiple systems. Each system will have multiple items assigned to the system. (Or pakcage). I want to create the first system for a study, then "copy" the details (multiple lines) assigned to that system by a numer entered in the text box. The "copies" would need to be assigned a new systemID in order to work.

Thank for all of your help. Your assistance has helped move this project further along!
0
 
LVL 84
ID: 40354949
Right - my code was intended as a starting point for you to build on, and not as a complete solution. EE is a place where we help YOU do the work - not where you tell us what you need, and we do it for you.

If you can post the code where you've tried to implement those items, and indicate where your code is not working, we can help you get past those hurdles.
0
 

Author Comment

by:Lenny Gonzalez
ID: 40354962
got it.........

I do want to thank you for all of your time. This sets me down the right path.

Many thanks
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 41674827
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Scott McDaniel (Microsoft Access MVP - EE MVE ) (https:#a40350989)
-- Scott McDaniel (Microsoft Access MVP - EE MVE ) (https:#a40353316)
-- Scott McDaniel (Microsoft Access MVP - EE MVE ) (https:#a40353441)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now