vba loop statement

[Grimwadec]

My table has about 50 fields, one containing "Products" and the other fields
containing various prices for the products depending on certain
circumstances, e.g. one price field is simply called "PriceStandard" I don't
have a good enough understanding (Translation - no idea!) of loops to be able
to write the code required to go through each field (some are Null) and if it
contains a Currency Value then increase it by a percentage criteria which I
will feed into the code from a dialog box. Now this may be how I will learn
to understand loops now that I have a practical application. Can anybody help
please?

 

[David H]

It isn't so much loops that you need to investigate as it is DAO. Loops are
actually quite easy as in ...

for i = 0 to 100
[do something]
next i

for i = 0 to Forms.count - 1
[do something]
next i

while not rs.EOR
[do something]
wend

Its the [do something] that's the key.

The example below you get you started and is pretty standard when looping
through records. I adapted it from an example to show you how to update the
value in a field.

Function DAORecordsetExample()
'Purpose: How to open a recordset and loop through the records.
'Note: Requires a table named MyTable, with a field named MyField.
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT MyField FROM MyTable;"

'Creates a Recordset object pointing to the records selected in strSQL
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

'Basic loop
While Not rs.EOF
Debug.Print rs!MyField
rs.MoveNext
wend

'Basic loop to update the value in the 'Amount' field by 10%.
While Not rs.EOF
with rs
.Edit
.Fields("Amount") = .Fields("Amount") * 1.10
.Update
.MoveNext
wend

rs.Close
Set rs = Nothing
End Function

That being said, if you're updating records in batch, you're probably better
off updating the records via a SQL Statement as in...

UPDATE Products SET Standard_Amount = Standard_Amount * 1.10,
Discount_Amount = Discount_Amount * 1.10

The SQL statement above will change all of the records in one fell swoop. If
you have a situation where the discounted price is fixed price less than the
standard you could go with...

UPDATE Products SET Standard_Amount = Standard_Amount * 1.10,
Discount_Amount = Standard_Amount *.30 * 1.10

adding a WHERE statement can be used to select specific records.

 

[David H]

If you don't have one, I would add a field that captures the Date/Time that
the record was updated as without it you won't know which records were
updated in the event that the code craps out. Going with the SQL Statement
will obviously eliminate that possibility.

It isn't so much loops that you need to investigate as it is DAO. Loops are
actually quite easy as in ...

for i = 0 to 100
[do something]
next i

for i = 0 to Forms.count - 1
[do something]
next i

while not rs.EOR
[do something]
wend

Its the [do something] that's the key.

The example below you get you started and is pretty standard when looping
through records. I adapted it from an example to show you how to update the
value in a field.

Function DAORecordsetExample()
'Purpose: How to open a recordset and loop through the records.
'Note: Requires a table named MyTable, with a field named MyField.
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT MyField FROM MyTable;"

'Creates a Recordset object pointing to the records selected in strSQL
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

'Basic loop
While Not rs.EOF
Debug.Print rs!MyField
rs.MoveNext
wend

'Basic loop to update the value in the 'Amount' field by 10%.
While Not rs.EOF
with rs
.Edit
.Fields("Amount") = .Fields("Amount") * 1.10
.Update
.MoveNext
wend

rs.Close
Set rs = Nothing
End Function

That being said, if you're updating records in batch, you're probably better
off updating the records via a SQL Statement as in...

UPDATE Products SET Standard_Amount = Standard_Amount * 1.10,
Discount_Amount = Discount_Amount * 1.10

The SQL statement above will change all of the records in one fell swoop. If
you have a situation where the discounted price is fixed price less than the
standard you could go with...

UPDATE Products SET Standard_Amount = Standard_Amount * 1.10,
Discount_Amount = Standard_Amount *.30 * 1.10

adding a WHERE statement can be used to select specific records.

My table has about 50 fields, one containing "Products" and the other fields
containing various prices for the products depending on certain
circumstances, e.g. one price field is simply called "PriceStandard" I don't
have a good enough understanding (Translation - no idea!) of loops to be able
to write the code required to go through each field (some are Null) and if it
contains a Currency Value then increase it by a percentage criteria which I
will feed into the code from a dialog box. Now this may be how I will learn
to understand loops now that I have a practical application. Can anybody help
please?

 

[John Spencer]

You don't need to worry about nulls since Null times anything is still Null

The basic SQL statement to do this would be something like:

UPDATE YourTable
SET YourField = YourField * 1.11

The following UNTESTED VBA sub would work. Make sure you pass the increase as
a decimal fraction. That is an 11 percent increase should be passed to the
sub as .11 and not as 11.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Public Sub sIncrementValues(Increase As Double)
Dim Db As DAO.Database
Dim tDef As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String

Set Db = CurrentDb()
Set tDef = Db.TableDefs("YourTable")

'Loop through all the fields
For Each fld In tDef.Fields
'Check if field is currency and if so update the value
If fld.Type = dbCurrency Then
'build the SQL statement
strSQL = "UPDATE [YourTable]" & _
" SET [" & fld.Name & "]= [" & fld.Name & "] * (1 + " & Increase & ")"

Db.Execute strSQL, dbFailOnError

End If
Next fld

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Grimwadec]

Thanks.seems to be just what I am looking for.Will give it a run and see how
I go
--
Grimwadec

You don't need to worry about nulls since Null times anything is still Null

The basic SQL statement to do this would be something like:

UPDATE YourTable
SET YourField = YourField * 1.11

The following UNTESTED VBA sub would work. Make sure you pass the increase as
a decimal fraction. That is an 11 percent increase should be passed to the
sub as .11 and not as 11.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Public Sub sIncrementValues(Increase As Double)
Dim Db As DAO.Database
Dim tDef As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String

Set Db = CurrentDb()
Set tDef = Db.TableDefs("YourTable")

'Loop through all the fields
For Each fld In tDef.Fields
'Check if field is currency and if so update the value
If fld.Type = dbCurrency Then
'build the SQL statement
strSQL = "UPDATE [YourTable]" & _
" SET [" & fld.Name & "]= [" & fld.Name & "] * (1 + " & Increase & ")"

Db.Execute strSQL, dbFailOnError

End If
Next fld

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

My table has about 50 fields, one containing "Products" and the other fields
containing various prices for the products depending on certain
circumstances, e.g. one price field is simply called "PriceStandard" I don't
have a good enough understanding (Translation - no idea!) of loops to be able
to write the code required to go through each field (some are Null) and if it
contains a Currency Value then increase it by a percentage criteria which I
will feed into the code from a dialog box. Now this may be how I will learn
to understand loops now that I have a practical application. Can anybody help
please?

 

[Grimwadec]

Thanks David - see John Spencer's Post
--
Grimwadec

If you don't have one, I would add a field that captures the Date/Time that
the record was updated as without it you won't know which records were
updated in the event that the code craps out. Going with the SQL Statement
will obviously eliminate that possibility.

It isn't so much loops that you need to investigate as it is DAO. Loops are
actually quite easy as in ...

for i = 0 to 100
[do something]
next i

for i = 0 to Forms.count - 1
[do something]
next i

while not rs.EOR
[do something]
wend

Its the [do something] that's the key.

The example below you get you started and is pretty standard when looping
through records. I adapted it from an example to show you how to update the
value in a field.

Function DAORecordsetExample()
'Purpose: How to open a recordset and loop through the records.
'Note: Requires a table named MyTable, with a field named MyField.
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT MyField FROM MyTable;"

'Creates a Recordset object pointing to the records selected in strSQL
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

'Basic loop
While Not rs.EOF
Debug.Print rs!MyField
rs.MoveNext
wend

'Basic loop to update the value in the 'Amount' field by 10%.
While Not rs.EOF
with rs
.Edit
.Fields("Amount") = .Fields("Amount") * 1.10
.Update
.MoveNext
wend

rs.Close
Set rs = Nothing
End Function

That being said, if you're updating records in batch, you're probably better
off updating the records via a SQL Statement as in...

UPDATE Products SET Standard_Amount = Standard_Amount * 1.10,
Discount_Amount = Discount_Amount * 1.10

The SQL statement above will change all of the records in one fell swoop. If
you have a situation where the discounted price is fixed price less than the
standard you could go with...

UPDATE Products SET Standard_Amount = Standard_Amount * 1.10,
Discount_Amount = Standard_Amount *.30 * 1.10

adding a WHERE statement can be used to select specific records.

My table has about 50 fields, one containing "Products" and the other fields
containing various prices for the products depending on certain
circumstances, e.g. one price field is simply called "PriceStandard" I don't
have a good enough understanding (Translation - no idea!) of loops to be able
to write the code required to go through each field (some are Null) and if it
contains a Currency Value then increase it by a percentage criteria which I
will feed into the code from a dialog box. Now this may be how I will learn
to understand loops now that I have a practical application. Can anybody help
please?

 

[Grimwadec]

And I finally got around to setting the code and it works perfectly, even
added in a WHERE statement for records with a fixed price flag...Thanks heaps!
--
Grimwadec

You don't need to worry about nulls since Null times anything is still Null

The basic SQL statement to do this would be something like:

UPDATE YourTable
SET YourField = YourField * 1.11

The following UNTESTED VBA sub would work. Make sure you pass the increase as
a decimal fraction. That is an 11 percent increase should be passed to the
sub as .11 and not as 11.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Public Sub sIncrementValues(Increase As Double)
Dim Db As DAO.Database
Dim tDef As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String

Set Db = CurrentDb()
Set tDef = Db.TableDefs("YourTable")

'Loop through all the fields
For Each fld In tDef.Fields
'Check if field is currency and if so update the value
If fld.Type = dbCurrency Then
'build the SQL statement
strSQL = "UPDATE [YourTable]" & _
" SET [" & fld.Name & "]= [" & fld.Name & "] * (1 + " & Increase & ")"

Db.Execute strSQL, dbFailOnError

End If
Next fld

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

My table has about 50 fields, one containing "Products" and the other fields
containing various prices for the products depending on certain
circumstances, e.g. one price field is simply called "PriceStandard" I don't
have a good enough understanding (Translation - no idea!) of loops to be able
to write the code required to go through each field (some are Null) and if it
contains a Currency Value then increase it by a percentage criteria which I
will feed into the code from a dialog box. Now this may be how I will learn
to understand loops now that I have a practical application. Can anybody help
please?

 

[Grimwadec]

Well John it did initially work I thought it also worked after I added the
WHERE statement but I must have been dreaming as it doesn't like my WHERE
statement I added to make the SQL read:

strSQL = "UPDATE [tblPatterns]" & _
"WHERE (((tblPatterns.FixPrice)=""No"" Or (tblPatterns.FixPrice) Is Null))"
& _
" SET [" & fld.Name & "]= [" & fld.Name & "] * (1 + " & Increase & ")"

--
Grimwadec

Thanks.seems to be just what I am looking for.Will give it a run and see how
I go
--
Grimwadec

You don't need to worry about nulls since Null times anything is still Null

The basic SQL statement to do this would be something like:

UPDATE YourTable
SET YourField = YourField * 1.11

The following UNTESTED VBA sub would work. Make sure you pass the increase as
a decimal fraction. That is an 11 percent increase should be passed to the
sub as .11 and not as 11.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Public Sub sIncrementValues(Increase As Double)
Dim Db As DAO.Database
Dim tDef As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String

Set Db = CurrentDb()
Set tDef = Db.TableDefs("YourTable")

'Loop through all the fields
For Each fld In tDef.Fields
'Check if field is currency and if so update the value
If fld.Type = dbCurrency Then
'build the SQL statement
strSQL = "UPDATE [YourTable]" & _
" SET [" & fld.Name & "]= [" & fld.Name & "] * (1 + " & Increase & ")"

Db.Execute strSQL, dbFailOnError

End If
Next fld

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

My table has about 50 fields, one containing "Products" and the other fields
containing various prices for the products depending on certain
circumstances, e.g. one price field is simply called "PriceStandard" I don't
have a good enough understanding (Translation - no idea!) of loops to be able
to write the code required to go through each field (some are Null) and if it
contains a Currency Value then increase it by a percentage criteria which I
will feed into the code from a dialog box. Now this may be how I will learn
to understand loops now that I have a practical application. Can anybody help
please?

 

[Grimwadec]

AND, thanks to MVP Allen Browne's SQL converter, finally got the SQL correct
with the WHERE statement included thus:
strSQL = "UPDATE [tblPatterns]" & vbCrLf & _
" SET [" & fld.Name & "]= [" & fld.Name & "] * (1 + " & Increase & ")" &
vbCrLf & _
"WHERE (((tblPatterns.FixPrice)<>""No"" Or (tblPatterns.FixPrice) Is Null));"

--
Grimwadec

You don't need to worry about nulls since Null times anything is still Null

The basic SQL statement to do this would be something like:

UPDATE YourTable
SET YourField = YourField * 1.11

The following UNTESTED VBA sub would work. Make sure you pass the increase as
a decimal fraction. That is an 11 percent increase should be passed to the
sub as .11 and not as 11.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Public Sub sIncrementValues(Increase As Double)
Dim Db As DAO.Database
Dim tDef As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String

Set Db = CurrentDb()
Set tDef = Db.TableDefs("YourTable")

'Loop through all the fields
For Each fld In tDef.Fields
'Check if field is currency and if so update the value
If fld.Type = dbCurrency Then
'build the SQL statement
strSQL = "UPDATE [YourTable]" & _
" SET [" & fld.Name & "]= [" & fld.Name & "] * (1 + " & Increase & ")"

Db.Execute strSQL, dbFailOnError

End If
Next fld

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

My table has about 50 fields, one containing "Products" and the other fields
containing various prices for the products depending on certain
circumstances, e.g. one price field is simply called "PriceStandard" I don't
have a good enough understanding (Translation - no idea!) of loops to be able
to write the code required to go through each field (some are Null) and if it
contains a Currency Value then increase it by a percentage criteria which I
will feed into the code from a dialog box. Now this may be how I will learn
to understand loops now that I have a practical application. Can anybody help
please?