Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 01-23-2009, 04:43 AM   #1
js982
Registered User
 
Join Date: Dec 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
js982 is on a distinguished road
loop trough records?

Pls allow me to ask this, probably for you, basic question.
I've searched for the solution and came up with some but I don't seem to be able to add it to the code below.

I'm trying to create a db that will generate an default xls report.
This report will be used for postprocessing in xls.

what I try to do: I am having 1 element that consists of different unit types.
Each unit type is multiple times available in the element.

so we assume, 1 element has 2 unit types and each unit type has 2 units. This would result in 4 units being installed.

I have the code to generate the 2 records for each unit type at once with some basic date like prices etc.

Code:
Dim NE_ID
Dim phase
Dim price
Dim unit_types

NE_ID = Me.NE_ID
phase = Me.[SF_RollOut].Form![PhaseDescription]
price = Me.price
unit_types = Me.UnitTyperPerNE

        	For counter = 1 To unit_types
			mysql = "insert into [T_phasesUnits] (NE_ID, PhaseDescription, price) VALUES (" & NE_ID & "," & phase & "," & price & ");"

DoCmd.RunSQL mysql
Now I would like to implement some phases (years) to it.

In 2009, we will have an installed base of 4 units
In 2010, we will have an installed base of 8 units.

When the above code is run, it fills the table T_phasesUnits with the correct data, but only for the 1st phase.
How do I have to do so that all phases will be processed?

Thanks!

js982 is offline   Reply With Quote
Old 01-23-2009, 09:18 AM   #2
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,090
Thanks: 6
Thanked 57 Times in 55 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Re: loop trough records?

js,

Code:
Dim NE_ID
Dim phase
Dim price
Dim unit_types

NE_ID = Me.NE_ID
phase = Me.[SF_RollOut].Form![PhaseDescription]
price = Me.price
unit_types = Me.UnitTyperPerNE

For counter = 1 To unit_types
   mysql = "insert into [T_phasesUnits] (NE_ID, PhaseDescription, price) VALUES (" & NE_ID & ",'" & phase & "'," & price & ");"
   DoCmd.RunSQL mysql
   Next i
That will make it syntactically correct, but the same values are going to
be Inserted "unit_types" times.

Where does the initial data come from?

Are you trying to "copy" all of the rows in a subform?

Wayne
__________________
Pool Players Know All The Angles
WayneRyan is offline   Reply With Quote
Old 01-23-2009, 09:43 AM   #3
js982
Registered User
 
Join Date: Dec 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
js982 is on a distinguished road
Re: loop trough records?

Hi Wayne,

Thanks for the response.
You are right, I forgot the "next" in the code.

We do work on repairing all kinds of units.
In the beginning we can only live with assumptions.
We say a system has 5 different cards and each of this card is present 5 times in the system.
My report needs to be an installed base on unit type, because these can all have different repair data like eg. costs.
But this specific data has to be updated in time once we get more accurate information on unit level.

That's why I am trying to copy these "Unit_types" times with the initial assumed data.

But I still can't figure out how to do it related to phases/years.
These phases are on a subform and with the code above it only takes the first phase in the subform.
so I want to copy it (phase 1 * Unit_Times) till (phase x * Unit Times)

Thanks

js982 is offline   Reply With Quote
Old 01-23-2009, 10:17 AM   #4
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 128 Times in 123 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
Re: loop trough records?

Quote:
Originally Posted by js982 View Post
Pls allow me to ask this, probably for you, basic question.
I've searched for the solution and came up with some but I don't seem to be able to add it to the code below.

I'm trying to create a db that will generate an default xls report.
This report will be used for postprocessing in xls.

what I try to do: I am having 1 element that consists of different unit types.
Each unit type is multiple times available in the element.

so we assume, 1 element has 2 unit types and each unit type has 2 units. This would result in 4 units being installed.

I have the code to generate the 2 records for each unit type at once with some basic date like prices etc.

Code:
Dim NE_ID
Dim phase
Dim price
Dim unit_types
 
NE_ID = Me.NE_ID
phase = Me.[SF_RollOut].Form![PhaseDescription]
price = Me.price
unit_types = Me.UnitTyperPerNE
 
            For counter = 1 To unit_types
            mysql = "insert into [T_phasesUnits] (NE_ID, PhaseDescription, price) VALUES (" & NE_ID & "," & phase & "," & price & ");"
 
DoCmd.RunSQL mysql
Now I would like to implement some phases (years) to it.

In 2009, we will have an installed base of 4 units
In 2010, we will have an installed base of 8 units.

When the above code is run, it fills the table T_phasesUnits with the correct data, but only for the 1st phase.
How do I have to do so that all phases will be processed?

Thanks!


A few observations regarding this code:
  • Your loop Control Variable (Counter), and the SQL String Variable (mysql) are undefined. Adding "Option Explicit" at the top of the module that contains the code will help you to fix this.
  • Your loop has no end (Next) statement, as pointed out by WayneRyan. However, I believe that you may need to use Next Counter as opposed to Next i.
  • I see nothing inside the loop that is either dependent on the Loop Control Variable, or modified by the result of any previous loop. As a result, I believe that the loop will repeat the same thing each time that it is executed.
__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MSAccessRookie is offline   Reply With Quote
Old 01-24-2009, 05:09 AM   #5
js982
Registered User
 
Join Date: Dec 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
js982 is on a distinguished road
Re: loop trough records?

Hi,

I've made a small example of my actual db.
I hope this makes it easier to explain.

You'll see 2 buttons, on the default unit parameters tab.
One with the code metioned above and one with code that I've tried to modify to make it work.
I just can get the Loop function to work with the for function.
When the 2nd button is clicked, I see it calculating, but no records are added.
The 1st button only takes the year 2009 into account.
Attached Files
File Type: zip sample.zip (156.7 KB, 61 views)
js982 is offline   Reply With Quote
Old 01-24-2009, 10:06 AM   #6
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,090
Thanks: 6
Thanked 57 Times in 55 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Re: loop trough records?

js,

Units has no value:

'units = Me.UnitsPerUnitType

For counter = 1 To Units ' <--- change to Me.UnitsPerUnitType

Wayne
__________________
Pool Players Know All The Angles
WayneRyan is offline   Reply With Quote
Old 01-25-2009, 01:17 AM   #7
js982
Registered User
 
Join Date: Dec 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
js982 is on a distinguished road
Re: loop trough records?

Hi WayneRyan,

Thanks!

That did it.


js982 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
loop through records selecting only those with checkbox selected peteB1735 Forms 7 08-01-2008 06:30 AM
Create Records Macro / Loop jagstirling Modules & VBA 9 07-01-2008 12:26 AM
If loop - Pulling records for the same date Taji Modules & VBA 4 07-09-2007 08:20 AM
Fixed number of records in a query Fernando General 4 04-10-2007 04:36 AM
how to read multiple records using a loop? catcoota Modules & VBA 13 09-11-2003 12:57 PM




All times are GMT -8. The time now is 05:13 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World