condition not working under do loop

[devilz]

Hello reader(s),

Here is my 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

[WizBang]

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

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.

[DataMiser]

This secgment of code could be changed

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

to something more like

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

And it should solve at least 2 problems

[Bill Crawley]

Also not sure why your using:

rsdate.Open ssql, conName, adOpenDynamic, adLockOptimistic

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