Iterate through sequencial variable

[QB]

I am trying to iterate through a series of sequential variables to use as a
multiplicative factor in a function but can't get the synthax quite right.
Could someone show me the proper way to do the following

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i


if iNoEntries = 3 it would do 3 iterations, the equivalent of:

Me.Amount = Me.[Base Amount] * Ratio1
Me.Amount = Me.[Base Amount] * Ratio2
Me.Amount = Me.[Base Amount] * Ratio3

Thank you

QB

 

[Clifford Bass]

Hi,

This is a little unclear. Are Ratio1, Ratio2, ... text boxes on a
form? If so, you could try:

rs![Amount] = Me.[Base Amount] * Me.Controls("Ratio" & i).Value

Clifford Bass

 

[JimBurke via AccessMonster.com]

Like Clifford said, it depends on where those values are coming from. If you
have variables Ratio1, Ratio2, ..., you could create a function GetRatio:

Private Function GetRatio(byval ratioID as integer) as Double (or whatever
data type is needed)

Select Case ratioID
case 1
GetRatio = Ratio1
case 2
GetRatio = Ratio2
... as many case statements as values you have for Ratio...
case Else
do whatever you would do here in case the value isn't valid
End Select

End Function

then in your loop use
rs![Amount] = Me.[Base Amount] * GetRatioAmount(i)

This would depend on how many possible values there are for Ratio - if you a
very large number that method may not be feasible. But this is only if they
are in variables.

If you have a known number of Ratio values and know what their values will
always be, you could create a table tblRatios with fields called ID and
RatioValue, then do a DLookup based on the value of 'i' in your loop, e.g.

rs![Amount] = Me.[Base Amount] * DLookup("RatioValue", "tblRatios",
"RatioID = " & i)

I am trying to iterate through a series of sequential variables to use as a
multiplicative factor in a function but can't get the synthax quite right.
Could someone show me the proper way to do the following

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

if iNoEntries = 3 it would do 3 iterations, the equivalent of:

Me.Amount = Me.[Base Amount] * Ratio1
Me.Amount = Me.[Base Amount] * Ratio2
Me.Amount = Me.[Base Amount] * Ratio3

Thank you

QB

 

[QB]

Ratio1, Ratio2, ... are all variables in the sub

I use a Select Case statement at the beginning to setup the variable
according to user selectiong on the form.

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio1 = 1
Case "2 payments"
iNoEntries = 2
Ratio1 = 0.9
Ratio2 = 0.1
End Select

Set rs = Me.frm_Fact.Form.RecordsetClone

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

rs.close
set rs = Nothing
Me.frm_Fact.Form.Requery


It is simply a question of iterating through the variables defined by the
iNoEntries variable.

QB

Like Clifford said, it depends on where those values are coming from. If you
have variables Ratio1, Ratio2, ..., you could create a function GetRatio:

Private Function GetRatio(byval ratioID as integer) as Double (or whatever
data type is needed)

Select Case ratioID
case 1
GetRatio = Ratio1
case 2
GetRatio = Ratio2
... as many case statements as values you have for Ratio...
case Else
do whatever you would do here in case the value isn't valid
End Select

End Function

then in your loop use
rs![Amount] = Me.[Base Amount] * GetRatioAmount(i)

This would depend on how many possible values there are for Ratio - if you a
very large number that method may not be feasible. But this is only if they
are in variables.

If you have a known number of Ratio values and know what their values will
always be, you could create a table tblRatios with fields called ID and
RatioValue, then do a DLookup based on the value of 'i' in your loop, e.g.

rs![Amount] = Me.[Base Amount] * DLookup("RatioValue", "tblRatios",
"RatioID = " & i)

I am trying to iterate through a series of sequential variables to use as a
multiplicative factor in a function but can't get the synthax quite right.
Could someone show me the proper way to do the following

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

if iNoEntries = 3 it would do 3 iterations, the equivalent of:

Me.Amount = Me.[Base Amount] * Ratio1
Me.Amount = Me.[Base Amount] * Ratio2
Me.Amount = Me.[Base Amount] * Ratio3

Thank you

QB

 

[Clifford Bass]

Hi,

In which case just use an array with the most entries you will need
(i.e. 10):

Dim Ratio(1 To 10) As Double

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio(1) = 1
Case "2 payments"
iNoEntries = 2
Ratio(1) = 0.9
Ratio(2) = 0.1
End Select

....

rs![Amount] = Me.[Base Amount] * Ratio (i)


Clifford Bass

Ratio1, Ratio2, ... are all variables in the sub

I use a Select Case statement at the beginning to setup the variable
according to user selectiong on the form.

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio1 = 1
Case "2 payments"
iNoEntries = 2
Ratio1 = 0.9
Ratio2 = 0.1
End Select

Set rs = Me.frm_Fact.Form.RecordsetClone

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

rs.close
set rs = Nothing
Me.frm_Fact.Form.Requery


It is simply a question of iterating through the variables defined by the
iNoEntries variable.

QB

Like Clifford said, it depends on where those values are coming from. If you
have variables Ratio1, Ratio2, ..., you could create a function GetRatio:

Private Function GetRatio(byval ratioID as integer) as Double (or whatever
data type is needed)

Select Case ratioID
case 1
GetRatio = Ratio1
case 2
GetRatio = Ratio2
... as many case statements as values you have for Ratio...
case Else
do whatever you would do here in case the value isn't valid
End Select

End Function

then in your loop use
rs![Amount] = Me.[Base Amount] * GetRatioAmount(i)

This would depend on how many possible values there are for Ratio - if you a
very large number that method may not be feasible. But this is only if they
are in variables.

If you have a known number of Ratio values and know what their values will
always be, you could create a table tblRatios with fields called ID and
RatioValue, then do a DLookup based on the value of 'i' in your loop, e.g.

rs![Amount] = Me.[Base Amount] * DLookup("RatioValue", "tblRatios",
"RatioID = " & i)

I am trying to iterate through a series of sequential variables to use as a
multiplicative factor in a function but can't get the synthax quite right.
Could someone show me the proper way to do the following

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

if iNoEntries = 3 it would do 3 iterations, the equivalent of:

Me.Amount = Me.[Base Amount] * Ratio1
Me.Amount = Me.[Base Amount] * Ratio2
Me.Amount = Me.[Base Amount] * Ratio3

Thank you

QB

 

[QB]

I will give it a try! Thank you for the help.

QB

Hi,

In which case just use an array with the most entries you will need
(i.e. 10):

Dim Ratio(1 To 10) As Double

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio(1) = 1
Case "2 payments"
iNoEntries = 2
Ratio(1) = 0.9
Ratio(2) = 0.1
End Select

...

rs![Amount] = Me.[Base Amount] * Ratio (i)


Clifford Bass

Ratio1, Ratio2, ... are all variables in the sub

I use a Select Case statement at the beginning to setup the variable
according to user selectiong on the form.

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio1 = 1
Case "2 payments"
iNoEntries = 2
Ratio1 = 0.9
Ratio2 = 0.1
End Select

Set rs = Me.frm_Fact.Form.RecordsetClone

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

rs.close
set rs = Nothing
Me.frm_Fact.Form.Requery


It is simply a question of iterating through the variables defined by the
iNoEntries variable.

QB

Like Clifford said, it depends on where those values are coming from. If you
have variables Ratio1, Ratio2, ..., you could create a function GetRatio:

Private Function GetRatio(byval ratioID as integer) as Double (or whatever
data type is needed)

Select Case ratioID
case 1
GetRatio = Ratio1
case 2
GetRatio = Ratio2
... as many case statements as values you have for Ratio...
case Else
do whatever you would do here in case the value isn't valid
End Select

End Function

then in your loop use
rs![Amount] = Me.[Base Amount] * GetRatioAmount(i)

This would depend on how many possible values there are for Ratio - if you a
very large number that method may not be feasible. But this is only if they
are in variables.

If you have a known number of Ratio values and know what their values will
always be, you could create a table tblRatios with fields called ID and
RatioValue, then do a DLookup based on the value of 'i' in your loop, e.g.

rs![Amount] = Me.[Base Amount] * DLookup("RatioValue", "tblRatios",
"RatioID = " & i)

QB wrote:
I am trying to iterate through a series of sequential variables to use as a
multiplicative factor in a function but can't get the synthax quite right.
Could someone show me the proper way to do the following

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

if iNoEntries = 3 it would do 3 iterations, the equivalent of:

Me.Amount = Me.[Base Amount] * Ratio1
Me.Amount = Me.[Base Amount] * Ratio2
Me.Amount = Me.[Base Amount] * Ratio3

Thank you

QB

 

[Clifford Bass]

Hi,

However, Jim's suggestion of putting the information into a table would
be better. Especially if the ratios might change over time. More on that in
a bit.

tblRatios:

PaymentCount RatioNumber RationValue
1 1 1
2 1 0.9
2 2 0.1

etc.

Then use a DLookup to get the appropriat value; or load the values for
the appropriate payment count into an array and use it.

If the ratios can change over time, add an effective date field:

PaymentCount RatioNumber EffectiveDate RationValue
1 1 01/01/2000 1
2 1 01/01/2000 0.9
2 2 01/01/2000 0.1
2 1 01/01/2010 0.8
2 2 01/01/2010 0.2

And incorporate the use of the effective date in relation to either
today or in relation to a date in a record in order to determine the correct
value(s) to use. If you need more on that let me know.

Clifford Bass

 

[Clifford Bass]

Hi,

You are welcome! And good luck.

Clifford Bass

 

[JimBurke via AccessMonster.com]

As far as I know there is no way to refer to a VBA variable other than by
it's actual name. If it's a form control you can do what Clifford said - Me.
Controls("Ratio" & i).Value. If it's a recordset field you can use rst!Fields
("Ratio" & i).Value. For a VBA variable I'm pretty sure you can't do anything
other than specify the actual variabe name - you can't use a string to
represent a variable name. If you know you have a very limited number of
values for iNoEntries, you can create a hidden textbox for each possible
ratio (txtRatio1, txtRatio2,...), then in your Select that you already have,
instead of Ratio1 = 1, Ratio2 = 2... use Me.Controls("txtRatio1").Value = 1, .
.. , then inside the loop use

rs![Amount] = Me.[Base Amount] * Me.Controls("txtRatio" & i).value

If you have a lot of possible ratios, I can't think of any simple way to do
it.

Ratio1, Ratio2, ... are all variables in the sub

I use a Select Case statement at the beginning to setup the variable
according to user selectiong on the form.

Select Case Me.Method
Case "1 payment"
iNoEntries = 1
Ratio1 = 1
Case "2 payments"
iNoEntries = 2
Ratio1 = 0.9
Ratio2 = 0.1
End Select

Set rs = Me.frm_Fact.Form.RecordsetClone

For i = 1 To iNoEntries
rs.AddNew
rs![Amount] = Me.[Base Amount] * "Ratio" & i
rs.Update
Next i

rs.close
set rs = Nothing
Me.frm_Fact.Form.Requery

It is simply a question of iterating through the variables defined by the
iNoEntries variable.

QB

Like Clifford said, it depends on where those values are coming from. If you
have variables Ratio1, Ratio2, ..., you could create a function GetRatio:

[quoted text clipped - 47 lines]