• Status: Solved
• Priority: Medium
• Security: Public
• Views: 137

# Problem with Looping

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.Open "select * from tempumpapp where 1=0", conn, adOpenStatic, adLockOptimistic

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
andrewl77
• 7
• 6
1 Solution

Commented:
Wrong method (.Open)

Should be:    rsdel.Execute
0

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

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

Commented:
change adOpenStatic to adOpenDynamic for recordsets you want to update
0

Billing EngineerCommented:
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 Commented:
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

Commented:
@andrewl77

What's your code doing now?
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 Commented:
The error is runtime error 91

object variable or with block variable not set.

The full code looks like this:

Private Sub Command1_Click()
Dim conn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs10 As ADODB.Recordset
Set rs10 = New ADODB.Recordset
Dim rsdel As ADODB.Recordset
Set rsdel = New ADODB.Recordset
Dim rs11 As ADODB.Recordset
Set rs11 = New ADODB.Recordset
Dim rs12 As ADODB.Recordset
Set rs12 = New ADODB.Recordset
' Open a connection.
Set conn = New ADODB.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

Commented:
You seem to have misinterpreted my earlier comment.  Should be

rsdel.Execute

Set rs1 = conn.Open
0

Author Commented:
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 Commented:
the code doesn't complete for me to confirm or deny the set rs1 = conn.open
0

Commented:
* what statement is giving you the 91 error?

* then how about
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

Commented:
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

Commented:
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 Commented:
Thanks....i might give that a go as there is a large number of records to sift through.

0

## Featured Post

• 7
• 6
Tackle projects and never again get stuck behind a technical roadblock.