RE: vba loop statement
- From: David H <DavidH@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 27 Sep 2009 18:50:01 -0700
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.
"David H" wrote:
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.
"Grimwadec" wrote:
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
- Follow-Ups:
- RE: vba loop statement
- From: Grimwadec
- RE: vba loop statement
- References:
- vba loop statement
- From: Grimwadec
- RE: vba loop statement
- From: David H
- vba loop statement
- Prev by Date: RE: vba loop statement
- Next by Date: Re: How to Link Outlook items to Access OLE field
- Previous by thread: RE: vba loop statement
- Next by thread: RE: vba loop statement
- Index(es):
Relevant Pages
|
