Solved

# Problem with Looping

Posted on 2006-05-03
128 Views
Hi all...i have the following code.  What its designed to do is delete the entire contents of the table, then add each value all the way until the recordset......

rs10.Open "select fixtures.*, (select fieldump1 from appointments where fixtures.gameno = appointments.gameno) as fieldump1val, (select fieldump2 from appointments where fixtures.gameno = appointments.gameno) as fieldump2val, (select fieldump3 from appointments where fixtures.gameno = appointments.gameno) as fieldump3val, (select boundaryump1 from appointments where fixtures.gameno = appointments.gameno) as boundaryump1val, (select boundaryump2 from appointments where fixtures.gameno = appointments.gameno) as boundaryump2val," _
& " (select boundaryump3 from appointments where fixtures.gameno = appointments.gameno) as boundaryump3val, (select goalump1 from appointments where fixtures.gameno = appointments.gameno) as goalump1val, (select goalump2 from appointments where fixtures.gameno = appointments.gameno) as goalump2val from fixtures where fixtures.gamecompleted = 0 and fixtures.leagueno = " & (league.ItemData(league.ListIndex)) & " And fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno", conn, adOpenDynamic, adLockOptimistic

Do Until rs10.EOF

If rs10!fieldump1val = 653 Then

Else
If IsNull(rs10!fieldump1val) Then

Else

rs11!gameno = rs10!gameno
rs11!umpireno = rs10!fieldump1val

rs11.Update

rs11.Close
Set rs11 = Nothing
End If
End If

If rs10!fieldump2val = 653 Then

Else
If IsNull(rs10!fieldump2val) Then

Else
rs12.Open "select * from tempumpapp where 1 = 0", conn, adOpenStatic, adLockOptimistic

rs12!gameno = rs10!gameno
rs12!umpireno = rs10!fieldump2val

rs12.Update

rs12.Close
Set rs12 = Nothing

End If
End If

rs10.MoveNext
Loop

rs10.Close
Set rs10 = Nothing
0
Question by:andrewl77

LVL 44

Expert Comment

Wrong method (.Open)

Should be:    rsdel.Execute
0

Author Comment

The actual problem is when the 2nd set of records is added at the point of:

0

LVL 44

Expert Comment

0

LVL 142

Expert Comment

what about replacing the entire code by the following 2 lines:

conn.execute "Delete from tempumpapp"
conn.execute "INSERT INTO tempumpapp ( gameno, umpireno ) select gameno, umpurireno from ( select fixtures.*, (select fieldump1 from appointments where fixtures.gameno = appointments.gameno) as fieldump1val, (select fieldump2 from appointments where fixtures.gameno = appointments.gameno) as fieldump2val, (select fieldump3 from appointments where fixtures.gameno = appointments.gameno) as fieldump3val, (select boundaryump1 from appointments where fixtures.gameno = appointments.gameno) as boundaryump1val, (select boundaryump2 from appointments where fixtures.gameno = appointments.gameno) as boundaryump2val," _
& " (select boundaryump3 from appointments where fixtures.gameno = appointments.gameno) as boundaryump3val, (select goalump1 from appointments where fixtures.gameno = appointments.gameno) as goalump1val, (select goalump2 from appointments where fixtures.gameno = appointments.gameno) as goalump2val from fixtures where fixtures.gamecompleted = 0 and fixtures.leagueno = " & (league.ItemData(league.ListIndex)) & " And fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno ) where fieldump1val = 653 or fieldump2val = 653 "

0

Author Comment

that will no work as the values from fieldump1val, fieldump2val etc are the acutal values that are used to retrieve the umpires names.

I have changed from adOpenStatic to adOpenDynamic and still has no difference.....
0

LVL 44

Expert Comment

@andrewl77

Are there any error messages or syntax errors?

You've shown us your code, but haven't describe a problem to be solved.  Please take a moment to detail your problem.
0

Author Comment

The error is runtime error 91

object variable or with block variable not set.

The full code looks like this:

Private Sub Command1_Click()
' Open a connection.
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "DSN=umas;database=umas;uid=umas;pwd=umas"
conn.Open

rs10.Open "select fixtures.*, (select fieldump1 from appointments where fixtures.gameno = appointments.gameno) as fieldump1val, (select fieldump2 from appointments where fixtures.gameno = appointments.gameno) as fieldump2val, (select fieldump3 from appointments where fixtures.gameno = appointments.gameno) as fieldump3val, (select boundaryump1 from appointments where fixtures.gameno = appointments.gameno) as boundaryump1val, (select boundaryump2 from appointments where fixtures.gameno = appointments.gameno) as boundaryump2val," _
& " (select boundaryump3 from appointments where fixtures.gameno = appointments.gameno) as boundaryump3val, (select goalump1 from appointments where fixtures.gameno = appointments.gameno) as goalump1val, (select goalump2 from appointments where fixtures.gameno = appointments.gameno) as goalump2val from fixtures where fixtures.gamecompleted = 0 and fixtures.leagueno = " & (league.ItemData(league.ListIndex)) & " And fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno", conn, adOpenDynamic, adLockOptimistic

Do Until rs10.EOF

If rs10!fieldump1val = 653 Then

Else
If IsNull(rs10!fieldump1val) Then

Else
rs11.Open "select * from tempumpapp where 1=0", conn, adOpenDynamic, adLockOptimistic <= Error occurs here on the 2nd recordset

rs11!gameno = rs10!gameno
rs11!umpireno = rs10!fieldump1val

rs11.update

rs11.Close
Set rs11 = Nothing
End If
End If

If rs10!fieldump2val = 653 Then

Else
If IsNull(rs10!fieldump2val) Then

Else
rs12.Open "select * from tempumpapp where 1 = 0", conn, adOpenDynamic, adLockOptimistic

rs12!gameno = rs10!gameno
rs12!umpireno = rs10!fieldump2val

rs12.update

rs12.Close
Set rs12 = Nothing

End If
End If

rs10.MoveNext
Loop

rs10.Close
Set rs10 = Nothing

' Open the Recordset
Set rs1 = conn.Execute("select tempumpapp.*, (select displayname from umpire where tempumpapp.umpireno = umpire.umpireno) as umpirename, (select roundno from fixtures where tempumpapp.gameno = fixtures.gameno) as roundno, (select gamedate from fixtures where tempumpapp.gameno = fixtures.gameno) as gamedate, (select leagueno from fixtures where tempumpapp.gameno = fixtures.gameno) as gameleagueno, (select hteamno from fixtures where tempumpapp.gameno = fixtures.gameno) as hteamno, (select ateamno from fixtures where tempumpapp.gameno = fixtures.gameno) as ateamno, (select venue from fixtures where tempumpapp.gameno = fixtures.gameno) as venue, (select gradecode from fixtures where tempumpapp.gameno = fixtures.gameno) as gamegradecode, (select starttime from fixtures where tempumpapp.gameno = fixtures.gameno) as starttime, (select gamenotes from fixtures where tempumpapp.gameno = fixtures.gameno) as gamenotes, (select teamname from clublist where hteamno = clublist.id) as hometeam," & _
" (select teamname from clublist where ateamno = clublist.id) as awayteam, (select groundname from grounds where venue = grounds.groundid) as groundname, (select groundstreet from grounds where venue = grounds.groundid) as groundstreet, (select groundtown from grounds where venue = grounds.groundid) as groundtown, (select groundmelways from grounds where venue = grounds.groundid) as groundmelways, (Select gradename from competitiongrades where competitiongrades.id = gamegradecode) as gradename, (select leaguename from competitions where gameleagueno = competitions.leagueno) as leaguename, (select fieldno from appointments where tempumpapp.gameno = appointments.gameno) as fieldno, (select fieldump1 from appointments where tempumpapp.gameno = appointments.gameno) as fieldump1val, (select displayname from umpire where umpire.umpireno = fieldump1val) as fieldump1, (select homeph from umpire where umpire.umpireno = fieldump1val) as fu1home," & _
" (select mobileph from umpire where umpire.umpireno = fieldump1val) as fu1mobile, (select fieldump2 from appointments where tempumpapp.gameno = appointments.gameno) as fieldump2val, (select displayname from umpire where umpire.umpireno = fieldump2val) as fieldump2, (select homeph from umpire where umpire.umpireno = fieldump2val) as fu2home, (select mobileph from umpire where umpire.umpireno = fieldump2val) as fu2mobile, (select fieldump3 from appointments where tempumpapp.gameno = appointments.gameno) as fieldump3val, (select displayname from umpire where umpire.umpireno = fieldump3val) as fieldump3, (select homeph from umpire where umpire.umpireno = fieldump3val) as fu3home, (select mobileph from umpire where umpire.umpireno = fieldump3val) as fu3mobile, (select boundaryump1 from appointments where tempumpapp.gameno = appointments.gameno) as boundaryump1val, (select displayname from umpire where umpire.umpireno = boundaryump1val) as boundaryump1," & _
" (select homeph from umpire where umpire.umpireno = boundaryump1val) as bu1home, (select mobileph from umpire where umpire.umpireno = boundaryump1val) as bu1mobile, (select boundaryump2 from appointments where tempumpapp.gameno = appointments.gameno) as boundaryump2val, (select displayname from umpire where umpire.umpireno = boundaryump2val) as boundaryump2, (select homeph from umpire where umpire.umpireno = boundaryump2val) as bu2home, (select mobileph from umpire where umpire.umpireno = boundaryump2val) as bu2mobile, (select boundaryump3 from appointments where tempumpapp.gameno = appointments.gameno) as boundaryump3val, (select displayname from umpire where umpire.umpireno = boundaryump3val) as boundaryump3, (select homeph from umpire where umpire.umpireno = boundaryump3val) as bu3home, (select mobileph from umpire where umpire.umpireno = boundaryump3val) as bu3mobile, (select goalump1 from appointments where tempumpapp.gameno = appointments.gameno) as goalump1val," & _
" (select displayname from umpire where umpire.umpireno = goalump1val) as goalump1, (select homeph from umpire where umpire.umpireno = goalump1val) as gu1home, (select mobileph from umpire where umpire.umpireno = goalump1val) as gu1mobile, (select goalump2 from appointments where tempumpapp.gameno = appointments.gameno) as goalump2val, (select displayname from umpire where umpire.umpireno = goalump2val) as goalump2, (select homeph from umpire where umpire.umpireno = goalump2val) as gu2home, (select mobileph from umpire where umpire.umpireno = goalump2val) as gu2mobile, (select town from umpire where umpire.umpireno = fieldump1val) as fu1sub, (select town from umpire where umpire.umpireno = fieldump2val) as fu2sub, (select town from umpire where umpire.umpireno = fieldump3val) as fu3sub, (select town from umpire where umpire.umpireno = boundaryump1val) as bu1sub, (select town from umpire where umpire.umpireno = boundaryump2val) as bu2sub," & _
" (select town from umpire where umpire.umpireno = boundaryump3val) as bu3sub, (select town from umpire where umpire.umpireno = goalump1val) as gu1sub, (select town from umpire where umpire.umpireno = goalump2val) as gu2sub, (select note from globalnotes where gamedate >= globalnotes.start and gamedate <= globalnotes.enddate) as globalnote From tempumpapp group by tempumpapp.umpireno order by gameno", , adCmdText)

' Connect the Recordset to the DataReport.
Set rptumpapp1.DataSource = rs1
rptumpapp1.WindowState = vbMaximized
rptumpapp1.Show vbModal

rs1.Close
conn.Close
End Sub
0

LVL 44

Expert Comment

You seem to have misinterpreted my earlier comment.  Should be

rsdel.Execute

Set rs1 = conn.Open
0

Author Comment

rsdel.execute doesn't work at all.  The problem is not this.  This part of the code works.  The problem is the looping i beleive.....
0

Author Comment

the code doesn't complete for me to confirm or deny the set rs1 = conn.open
0

LVL 44

Expert Comment

* what statement is giving you the 91 error?

conn.Execute "Delete from tempumpapp"

The reason for this recommendation is that a Delete SQL statement does not return any records, so the .Open method is not appropriate.

=================================
I'll reformat your code to make it a bit more understandable:

Do Until rs10.EOF

If rs10!fieldump1val = 653 Then
Else
If IsNull(rs10!fieldump1val) Then
Else
rs11.Open "select * from tempumpapp where 1=0", conn, adOpenDynamic, adLockOptimistic <= Error occurs here on the 2nd recordset

rs11!gameno = rs10!gameno
rs11!umpireno = rs10!fieldump1val

rs11.update

rs11.Close
Set rs11 = Nothing
End If
End If

If rs10!fieldump2val = 653 Then
Else
If IsNull(rs10!fieldump2val) Then
Else
rs12.Open "select * from tempumpapp where 1 = 0", conn, adOpenDynamic, adLockOptimistic

rs12!gameno = rs10!gameno
rs12!umpireno = rs10!fieldump2val

rs12.update

rs12.Close
Set rs12 = Nothing

End If
End If

rs10.MoveNext
Loop

* Notice that after adding a record to rs11 or rs12, you not only close the recordset, but set the recordset object = nothing.  If your rs10 data would cause more than one additional record in either of these (rs11, rs12) recordsets, you will certainly receive a 91 error.  If you want to accomodate multiple additions to rs11 and rs12, you should move their .Close and Set ___ = Nothing statements outside of your Do...Loop.
0

LVL 44

Accepted Solution

so the bottom part of your code might look like
...
rs10.MoveNext
Loop

rs11.Close
Set rs11 = Nothing
rs12.Close
Set rs12 = Nothing

rs10.Close
Set rs10 = Nothing

' Open the Recordset
...
0

LVL 44

Expert Comment

Thanks for the points.  Glad I could help.

If you have a big league, you might notice some poor performance populating your tempumpapp table.  Iterating through rs1 is a relatively slow process.  If you experience a slow down, consider populating the tempumpapp with a couple of Update SQL statements.  You will completely eliminate your looping.

Also, your rs10 SQL is much more complicated than it needs to be to feed the two tempumpapp appends.  Given the conditions in the columnar SQL, you should probably be able to do a simple table join

Example:
Conn.Execute "Insert Into tempumpapp (gameno, umpireno)
Select fixtures.gameno, appointments.fieldump1
From Fixtures Inner Join Appoints On
fixtures.gameno = appointments.gameno
Where fixtures.gamecompleted = 0
and fixtures.leagueno = " & (league.ItemData(league.ListIndex)) & "
And fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & "And fieldump1 Is Not Null And fieldump1 <>653"

Conn.Execute "Insert Into tempumpapp (gameno, umpireno)
Select fixtures.gameno, appointments.fieldump2
From Fixtures Inner Join Appoints On
fixtures.gameno = appointments.gameno
Where fixtures.gamecompleted = 0
and fixtures.leagueno = " & (league.ItemData(league.ListIndex)) & "
And fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & "And fieldump2 Is Not Null And fieldump2 <>653"
0

Author Comment

Thanks....i might give that a go as there is a large number of records to sift through.

0

## Featured Post

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…