condition not working under do loop

Hello reader(s),

Here is my code:

Code:

---


Private Sub Combo1_Click()
Dim r
    conName.Provider = "Microsoft.Jet.OLEDB.4.0"
    conName.ConnectionString = App.Path & "\rosterdb.mdb"
    conName.CursorLocation = adUseClient
    conName.Open
 
rsid.Open "select * from DayWiseRoster", conName, adOpenDynamic, adLockOptimistic

' in below loop i got the problem. it prompts "loop without do"
Do While Not rsid.EOF

If rsid("rm_id") = Txtrostermnthid.Text Then

r = MsgBox("Shift of " & CboName.Text & " for the date of " & Label1.Caption & " have been saved. Do you want to update shift for " & CboName.Text & "", vbYesNo)

If r = vbYes Then

ssql = "update DayWiseRoster set shift=" & Combo1.Text & "where Date= ‘" & Text1.Text & "’"

rsdate.Open ssql, conName, adOpenDynamic, adLockOptimistic

Else
Exit Sub
CboName.SetFocus
End If
rsid.close
End if
rsid.MoveNext
Loop


    ssql2 = "INSERT INTO DayWiseRoster (rm_id) SELECT rostermnth_id FROM UserOfTheMonth where rmname='" & CboName.Text & "'"

    rsName.Open ssql2, conName, adOpenDynamic, adLockOptimistic

rsmnth.Open "select * from DayWiseRoster", conName, adOpenDynamic, adLockOptimistic

rsmnth.MoveFirst

  Do While Not rsmnth.EOF

If Txtrostermnthid.Text = rsmnth.Fields("rm_id") Then

rsmnth.Fields("Date").Value = Trim(Label1.Caption)
rsmnth.Fields("shift").Value = Trim(Combo1.Text)
rsmnth.Update

End If
  rsmnth.MoveNext
  Loop

conName.Close

---

Can you please help me for the first loop? Using VB 6.0 and Access.

Regards

Well, firstly, you'd benefit from indenting your code so it is easily understandable. Then you might see coding errors more readily.

Code:

---

Do While Not rsid.EOF
  If rsid("rm_id") = Txtrostermnthid.Text Then
    r = MsgBox("Shift of " & CboName.Text & " for the date of " & Label1.Caption & " have been saved. Do you want to update shift for " & CboName.Text & "", vbYesNo)
    If r = vbYes Then
      ssql = "update DayWiseRoster set shift=" & Combo1.Text & "where Date= ‘" & Text1.Text & "’"
      rsdate.Open ssql, conName, adOpenDynamic, adLockOptimistic
    Else
      Exit Sub 'this will immediately exit the sub, leaving the recordset open, and the following line will not execute.
      CboName.SetFocus
    End If
    rsid.Close 'this will close the recordset, but the loop will continue, resulting in an error when executing the MoveNext method.
  End If
  rsid.MoveNext
Loop

---

You may want to use Exit Loop, and use the return value from the message box to determine whether to execute any additional code in the sub.

This secgment of code could be changed

Code:

---

      Else
        Exit Sub
        CboName.SetFocus
      End If
      rsid.close
  End if
  rsid.MoveNext
Loop

---

to something more like

Code:

---

      Else
        rsid.close
        CboName.SetFocus
        Exit Sub       
      End If     
  End if
  rsid.MoveNext
Loop
rsid.close

---

And it should solve at least 2 problems

Also not sure why your using:

Code:

---

rsdate.Open ssql, conName, adOpenDynamic, adLockOptimistic

---

when it would be more efficient to use the Execute command against the connection object.