-
February 17th, 2009, 05:37 AM
#1
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
-
February 17th, 2009, 09:01 AM
#2
Re: condition not working under do loop
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.
Please remember to rate the posts and threads that you find useful.
How can something be both new and improved at the same time?
-
February 17th, 2009, 01:51 PM
#3
Re: condition not working under do loop
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
-
February 18th, 2009, 08:35 AM
#4
Re: condition not working under do loop
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.
If you find my answers helpful, dont forget to rate me
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|