For Next Syntax

  • Thread starter Thread starter Wayne Emminizer
  • Start date Start date
W

Wayne Emminizer

Hopefully a quick question on For Next loop function. I
have the code below. Basically I want this to check the
item code field for the current ID in the loop and make
sure it exists. There are times when the next ID won't
exist such as when I put a filtered set of records in
this table and the ID's are not sequential. In this
event I need for the code to go back to the top and go to
the next ID and test it. If the item code is not null
then it needs to run the code. It is working in it's
present form but I think it's not working correctly...I
am still trying to validate that.

lngMin = DMin("[ID]", TempTable)
lngMax = DMax("[ID]", TempTable)

Counter = lngMin
For Counter = lngMin To lngMax Step 1
If IsNull(DLookup("[ItemCode]", TempTable, "[ID]
=" & Counter & "")) Then
Counter = Counter + 1
Else
ActiveItemCode = DLookup("[ItemCode]",
TempTable, "[ID]=" & Counter & "")
ActiveOriginCompany = DLookup
("[OriginCompany]", TempTable, "[ID]=" & Counter & "")
'******There is a section here that runs a ton of code I
am not copying up since it would take too much space***

End If
DoCmd.RunSQL "UPDATE [TEST] SET
[TEST].NewItemName='" & sqlString & "'" & " WHERE [ID]="
& Counter & ";"
'DoCmd.RunSQL "UPDATE [vueItemMaster] SET
[vueItemMaster].NewItemName='" & ActiveItemCode & " " &
ActiveOriginCompany & "'" & " WHERE [ID]=" & Counter & ";"
Next Counter
 
Your syntax for the For Next loop is correct, and I see
what you're getting at, but there's a better way. Your
approach works, but is VERY inefficient. From what I can
gleen, the following procedure will accomplish the same
thing without all the unnecessary overhead. You'll need
to reference the DAO 3.6 Object Library from the code
window. It's under Tools, References on the VB menu bar.

Sub ValidateMyStuff()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(TempTable, _
dbOpenSnapshot)
Do Until rst.EOF
ActiveItemCode = rst!ItemCode
ActiveOriginCompany = rst!OriginCompany
'**There is a section here that runs a ton of code
DoCmd.RunSQL "UPDATE [TEST] " _
& "SET [TEST].NewItemName='" & sqlString & "' " _
& "WHERE [ID]=" & rst!ID
DoCmd.RunSQL "UPDATE [vueItemMaster] " _
& "SET [vueItemMaster].NewItemName='" _
& ActiveItemCode & " " & ActiveOriginCompany & "' " _
& "WHERE [ID]=" & rst!ID
rst.MoveNext
Loop
End Sub

I don't want to burden you with details, but you could
even get rid of those 'RunSQL' methods if you add 2
additional 'dynaset' recordsets and use those to perform
your updates to the TEST and vueItemMaster tables. Just
food for thought. Good luck.
 
wow...perfect...that works and is a lot faster....thanks
a ton....I am sort of self teaching myself VB so I was
unfamiliar with that method....I didn't quite follow the
replacing SQL statements with 2 new dynasets though to
run those updates....how would I go about doing that?

Thanks again...
-----Original Message-----
Your syntax for the For Next loop is correct, and I see
what you're getting at, but there's a better way. Your
approach works, but is VERY inefficient. From what I can
gleen, the following procedure will accomplish the same
thing without all the unnecessary overhead. You'll need
to reference the DAO 3.6 Object Library from the code
window. It's under Tools, References on the VB menu bar.

Sub ValidateMyStuff()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(TempTable, _
dbOpenSnapshot)
Do Until rst.EOF
ActiveItemCode = rst!ItemCode
ActiveOriginCompany = rst!OriginCompany
'**There is a section here that runs a ton of code
DoCmd.RunSQL "UPDATE [TEST] " _
& "SET [TEST].NewItemName='" & sqlString & "' " _
& "WHERE [ID]=" & rst!ID
DoCmd.RunSQL "UPDATE [vueItemMaster] " _
& "SET [vueItemMaster].NewItemName='" _
& ActiveItemCode & " " & ActiveOriginCompany & "' " _
& "WHERE [ID]=" & rst!ID
rst.MoveNext
Loop
End Sub

I don't want to burden you with details, but you could
even get rid of those 'RunSQL' methods if you add 2
additional 'dynaset' recordsets and use those to perform
your updates to the TEST and vueItemMaster tables. Just
food for thought. Good luck.

-----Original Message-----
Hopefully a quick question on For Next loop function. I
have the code below. Basically I want this to check the
item code field for the current ID in the loop and make
sure it exists. There are times when the next ID won't
exist such as when I put a filtered set of records in
this table and the ID's are not sequential. In this
event I need for the code to go back to the top and go to
the next ID and test it. If the item code is not null
then it needs to run the code. It is working in it's
present form but I think it's not working correctly...I
am still trying to validate that.

lngMin = DMin("[ID]", TempTable)
lngMax = DMax("[ID]", TempTable)

Counter = lngMin
For Counter = lngMin To lngMax Step 1
If IsNull(DLookup("[ItemCode]", TempTable, "[ID]
=" & Counter & "")) Then
Counter = Counter + 1
Else
ActiveItemCode = DLookup("[ItemCode]",
TempTable, "[ID]=" & Counter & "")
ActiveOriginCompany = DLookup
("[OriginCompany]", TempTable, "[ID]=" & Counter & "")
'******There is a section here that runs a ton of code I
am not copying up since it would take too much space***

End If
DoCmd.RunSQL "UPDATE [TEST] SET
[TEST].NewItemName='" & sqlString & "'" & " WHERE [ID] ="
& Counter & ";"
'DoCmd.RunSQL "UPDATE [vueItemMaster] SET
[vueItemMaster].NewItemName='" & ActiveItemCode & " " &
ActiveOriginCompany & "'" & " WHERE [ID]=" & Counter & ";"
Next Counter
.
Click to expand...
.
Click to expand...
 
Action queries are ideal when you're updating many records
at once, but since you're not its better to avoid them.
Follow the 3 steps below and you should see major
improvement to your process time. The effeciency stems
from opening a recordset only once, and using it many
times.

1) Add this to the top of your existing code;

Dim rstTest As DAO.Recordset
Dim rstMstr As DAO.Recordset
Set rstTst = CurrentDb.OpenRecordset("Test", _
dbOpenDynaset)
Set rstMst = CurrentDb.OpenRecordset("vueItemMaster", _
dbOpenDynaset)

2) Paste this separate procedure into a module;

Sub RUpdate(strField As String, _
rstRecordset As DAO.Recordset, _
strCriteria As String, varUpdateTo As Variant)
With rstRecordset
.FindFirst strCriteria
Do Until .NoMatch
.Edit
.Fields(strField) = varUpdateTo
.Update
.FindNext strCriteria
Loop
End With
End Sub

3) Replace your 'RunSQL' statements with these;

RUpdate("NewItemName", rstTest, _
"[ID]=" & rst!ID, _
sqlString)
RUpdate("NewItemName", rstMstr, _
"[ID]=" & rst!ID, _
ActiveItemCode & " " & ActiveOriginCompany)

-----Original Message-----
wow...perfect...that works and is a lot faster....thanks
a ton....I am sort of self teaching myself VB so I was
unfamiliar with that method....I didn't quite follow the
replacing SQL statements with 2 new dynasets though to
run those updates....how would I go about doing that?

Thanks again...
-----Original Message-----
Your syntax for the For Next loop is correct, and I see
what you're getting at, but there's a better way. Your
approach works, but is VERY inefficient. From what I can
gleen, the following procedure will accomplish the same
thing without all the unnecessary overhead. You'll need
to reference the DAO 3.6 Object Library from the code
window. It's under Tools, References on the VB menu bar.

Sub ValidateMyStuff()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(TempTable, _
dbOpenSnapshot)
Do Until rst.EOF
ActiveItemCode = rst!ItemCode
ActiveOriginCompany = rst!OriginCompany
'**There is a section here that runs a ton of code
DoCmd.RunSQL "UPDATE [TEST] " _
& "SET [TEST].NewItemName='" & sqlString & "' " _
& "WHERE [ID]=" & rst!ID
DoCmd.RunSQL "UPDATE [vueItemMaster] " _
& "SET [vueItemMaster].NewItemName='" _
& ActiveItemCode & " " & ActiveOriginCompany & "' " _
& "WHERE [ID]=" & rst!ID
rst.MoveNext
Loop
End Sub

I don't want to burden you with details, but you could
even get rid of those 'RunSQL' methods if you add 2
additional 'dynaset' recordsets and use those to perform
your updates to the TEST and vueItemMaster tables. Just
food for thought. Good luck.

-----Original Message-----
Hopefully a quick question on For Next loop function. I
have the code below. Basically I want this to check the
item code field for the current ID in the loop and make
sure it exists. There are times when the next ID won't
exist such as when I put a filtered set of records in
this table and the ID's are not sequential. In this
event I need for the code to go back to the top and go to
the next ID and test it. If the item code is not null
then it needs to run the code. It is working in it's
present form but I think it's not working correctly...I
am still trying to validate that.

lngMin = DMin("[ID]", TempTable)
lngMax = DMax("[ID]", TempTable)

Counter = lngMin
For Counter = lngMin To lngMax Step 1
If IsNull(DLookup("[ItemCode]", TempTable, "[ID]
=" & Counter & "")) Then
Counter = Counter + 1
Else
ActiveItemCode = DLookup("[ItemCode]",
TempTable, "[ID]=" & Counter & "")
ActiveOriginCompany = DLookup
("[OriginCompany]", TempTable, "[ID]=" & Counter & "")
'******There is a section here that runs a ton of code I
am not copying up since it would take too much space***

End If
DoCmd.RunSQL "UPDATE [TEST] SET
[TEST].NewItemName='" & sqlString & "'" & " WHERE [ID] ="
& Counter & ";"
'DoCmd.RunSQL "UPDATE [vueItemMaster] SET
[vueItemMaster].NewItemName='" & ActiveItemCode & " " &
ActiveOriginCompany & "'" & " WHERE [ID]=" & Counter & ";"
Next Counter
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top