Loop in button code

[Kevin199]

I have a file with a bunch of data from different sources. I have a form
where the user picks the source of data and a report is generated that shows
the correlation of the data from the picked source compared to the other
sources. What I would like to do now is come up with a report that shows
each source and its correlation to the others. I think I will need to have a
loop in my button’s on click event procedure. Can anyone provide me with
some code of a loop. I tried but I get “Object required messageâ€. Thank you.

 

[dymondjack]

There's a number of different ways to handle a loop. The main component is
some sort of criteria that tells the loop when to stop (this is most commonly
done using a counter, or the end of file/recordset).

example using a counter:

Dim iCount as Integer
iCount = 0

While iCount < 20
'Do Something Here
Wend



example using a recordset

Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset("....")

While rs.EOF = False
'Do Something Here
rs.MoveNext
Wend



Besides While/Wend, there are other loop options available as well

For/Next:

For Each "whatever" In "collection
'Do Something
Next "whatever"


Do/Loop:

Do
'Do Something
Loop


If you want something specific to your task you'll need to provide some more
detailed info.

hth

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[dymondjack]

Well, I forgot to increment the counter in my first example, which results in
an infinate loop...

Dim iCount as Integer
iCount = 0

While iCount < 20
'Do Something Here

iCount = iCount + 1 '<---- ADD THIS

Wend

add the iCount + 1 line inside the loop to increment it each time it runs...


--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Kevin199]

Here is my code. I get a message "object required" from this mess.

Private Sub All_Locations_Click()
On Error GoTo Err_All_Locations_Click
Dim stDocName As String
stDocName = "AppToCorr"
While YG.EOF = False
DoCmd.OpenQuery stDocName, acNormal, acEdit
Wend
Exit_All_Locations_Click:
Exit Sub
Err_All_Locations_Click:
MsgBox Err.Description
Resume Exit_All_Locations_Click
End Sub

 

[dymondjack]

What is YG? You don't have it defined as anything within that procedure
(which seems to be the cause of the error). You'll need to dimension it and
set a reference to it:


Dim YG As <some sort of object>
Set YG = <some method depending on the type of object>
While YG.EOF = False
....
Wend
Set YG = Nothing



What are you trying to run a loop for? I'm not sure your ultimate goal and
don't really have any guesses on what object you need or why you need to run
a loop. If you can give some more info I might be able to suggest something
to get it working.

hth
--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[John W. Vinson]

What is YG? You don't have it defined as anything within that procedure
(which seems to be the cause of the error). You'll need to dimension it and
set a reference to it:


Dim YG As <some sort of object>
Set YG = <some method depending on the type of object>
While YG.EOF = False
...
Wend
Set YG = Nothing



What are you trying to run a loop for? I'm not sure your ultimate goal and
don't really have any guesses on what object you need or why you need to run
a loop. If you can give some more info I might be able to suggest something
to get it working.

hth

If YG is a recordset you'll also need a YG.MoveNext in the loop or it will
just loop forever on the first record.

I agree with Dymondjack - this seems to be a very odd thing to do; why are you
*opening a whole bunch of query datasheets*???

 

[Kevin199]

YG is a table with all my data. I left my old do code in. Once I get the
loop to work I will fill in the right code, sorry for the confusion.

 

[John W. Vinson]

YG is a table with all my data. I left my old do code in. Once I get the
loop to work I will fill in the right code, sorry for the confusion.

Perhaps if you were to explain what you wanted the loop to DO someone could
help.

 

[Kevin199]

A little more detail of my original post may be in order. I want the loop to
go through my YG table and compare sales volumes of inventory items from
various retail sites and calculate a correlation from each location compared
to the rest of the location combined. In the past I have done the
calculation in excel. I have now been given the project to go back into
historical data and run hundreds of these calculations, thus my motivation.
I thought that once I got a loop to work off my button on a form I could
start putting in code to get the rest to work. Right know I have 4 sub
queries that pull data out of my table and calculate the correlation of one
location compared to the others. I am planning on using them in some form in
my loop.

 

[John W. Vinson]

A little more detail of my original post may be in order. I want the loop to
go through my YG table and compare sales volumes of inventory items from
various retail sites and calculate a correlation from each location compared
to the rest of the location combined. In the past I have done the
calculation in excel. I have now been given the project to go back into
historical data and run hundreds of these calculations, thus my motivation.
I thought that once I got a loop to work off my button on a form I could
start putting in code to get the rest to work. Right know I have 4 sub
queries that pull data out of my table and calculate the correlation of one
location compared to the others. I am planning on using them in some form in
my loop.

I still don't see how the OpenQuery method is of use here. This would not
calculate any correlations; it would just open a blizzard of datasheets which
the user would have to look at and close one at a time.

 

[Kevin199]

Please understand John that this OpenQuery statement is just a place holder
for some code I still need to write. I envision that somewhere near the
bottom of the loop there will be an append query or an update query or
something else that deals with each site and its correlation. At this point
in time all I need help with is making a loop that will go through each
record in my YG table. I don’t understand how to Dim and Set the data in my
table. I believe that is what is causing the error message “Object
Required†to appear.

 

[Jack Leach]

Here's now to get YG to work:

'-----------
Dim YG as DAO.Recordset
Set YG = CurrentDB.OpenRecordset("yourtablename")

'Make sure theres records
If YG.RecordCount > 0 Then
'Move to the first record
YG.MoveFirst

'Loop the recordset
While Not YG.EOF
'Put some code in here
YG.MoveNext
Wend
End If

YG.Close
Set YG = Nothing
'-----------



That will loop through each record of a recordset.

However, it may not be what you a re "really" looking for. In essence, a
query is very much like a loop... it performs a similar operation for each
record of a given recordset. Rarely do you need to resort to running an
actual loop through a recordset. Furthermore, running an append query (or
any other query) inside a loop is a big no-no (because at that point, you're
almost running a loop inside a loop, and performance is going to be
terrible). There's almost always a better way to do this.

I didn't quite get enough of your situation to give advice on a different
method, but anyway, the above example shows how to utilize a recordset loop,
and you can do what you'd like with it from there.


hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Kevin199]

Thanks to everyone. She works great. I may try to change to only a query
and no loop at some point, but you know how that is. "If it ain't broke..."