loop trough records?

[js982]

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.

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!

 

[WayneRyan]

js,

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 & ",[B][SIZE="3"]'[/SIZE][/B]" & phase & "[B][SIZE="3"]'[/SIZE][/B]," & price & ");"
   DoCmd.RunSQL mysql
   [B][SIZE="3"]Next i[/SIZE][/B]

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

 

[js982]

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

 

[MSAccessRookie]

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.

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.

 

[js982]

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.

 

[WayneRyan]

js,

Units has no value:

'units = Me.UnitsPerUnitType

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

Wayne

 

[js982]

Hi WayneRyan,

Thanks!

That did it.