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

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
   
    rsdel.Open "Delete from tempumpapp", conn, adOpenDynamic, adLockOptimistic, adCmdText
     
    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.addnew
   
    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.addnew
   
    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
Asked:
andrewl77
  • 7
  • 6
1 Solution
 
aikimarkCommented:
Wrong method (.Open)

Should be:    rsdel.Execute  
0
 
andrewl77Author 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
 
aikimarkCommented:
change adOpenStatic to adOpenDynamic for recordsets you want to update
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Guy Hengel [angelIII / a3]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
 
andrewl77Author 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
 
aikimarkCommented:
@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
 
andrewl77Author 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
   
    rsdel.Open "Delete from tempumpapp", conn, adOpenDynamic, adLockOptimistic, adCmdText
     
    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.addnew
   
    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.addnew
   
    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
 
aikimarkCommented:
You seem to have misinterpreted my earlier comment.  Should be

rsdel.Execute

Set rs1 = conn.Open
0
 
andrewl77Author 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
 
andrewl77Author Commented:
the code doesn't complete for me to confirm or deny the set rs1 = conn.open
0
 
aikimarkCommented:
* 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.addnew
         
          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.addnew
         
          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
 
aikimarkCommented:
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
 
aikimarkCommented:
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
 
andrewl77Author Commented:
Thanks....i might give that a go as there is a large number of records to sift through.

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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