Problem with Loop

[Vack58]

I have the following piece of code. I am prompting the user for a Ship to code, then checking the code to make sure it is in the POSHPFIL_SQL. If the no records are returned I want a message to dispay "Ship To Not On file" and keep prompting the user. I realize I need some sort of loop but I am new to VB and can't seem to figure out the logic.

ShipTo = InputBox(Message2, Title2, "",100, 100)

strSQL10 = "Select ship_to_cd from POSHPFIL_SQL where ship_to_cd = '" & ShipTo & "'"

set rs10=createobject("ADODB.Recordset")
with rs10
.activeconnection=ConWSH
.CursorType=adOpenForwardOnly
.CursorLocation=3
.open strSQL10
End with



if rs10.recordcount = 0 then
Wscript.Echo "Ship To Not on File"
ShipTo = InputBox(Message2, Title2, "",100, 100)
set rs10=createobject("ADODB.Recordset")
with rs10
.activeconnection=ConWSH
.CursorType=adOpenForwardOnly
.CursorLocation=3
.open strSQL10
End with
end if

 

[mrmovie]

'somethjing like? it presumes some of the varibles used in the Sub are declared globally which isnt the best thing but...


Dim ShipTo, intRCCount, objRS
Do
intRCCount = 0
ShipTo = InputBox(Message2, Title2, "",100, 100)
strSQL10 = "Select ship_to_cd from POSHPFIL_SQL where ship_to_cd = '" & ShipTo & "'"
Call GetRecordSetCount(strSQL10, intRCCount, objRS)
If intRCCount > 0 Then
Exit Do
End If
Loop


Sub GetRecordSetCount(ByVal strSQL, ByRef intRCCount, ByRef objRS)
Dim rs10
intRCCount = 0
Set rs10=createobject("ADODB.Recordset")
With rs10
.activeconnection=ConWSH
.CursorType=adOpenForwardOnly
.CursorLocation=3
.open strSQL10
End with
intRCCount = rs10.RecordCount
Set objRS = rs10 '? cause you need it later on
Set rs10 = Nothing
End Sub

 

[mrmovie]

im sure that post was rather arse about face...
this make more sense? i am hoping that you dont have to put the Set rs10=createobject("ado...line inside the Do Loop otherwise that will make things rather ugly

Dim rs10
intRCCount = 0
Set rs10=createobject("ADODB.Recordset")
Do
ShipTo = InputBox(Message2, Title2, "",100, 100)
strSQL10 = "Select ship_to_cd from POSHPFIL_SQL where ship_to_cd = '" & ShipTo & "'"
With rs10
.activeconnection=ConWSH
.CursorType=adOpenForwardOnly
.CursorLocation=3
.open strSQL10
End with
intRCCount = rs10.RecordCount
If intRCCount > 0 Then
Exit Do
End If
Loop
'''do lots of stuff with your recordset?


Set rs10 = Nothing

 

[mrmovie]

yawn, yawn, sometimes was fingers engage quicker than my brain



Dim rs10
Set rs10=createobject("ADODB.Recordset")
Do
ShipTo = InputBox(Message2, Title2, "",100, 100)
strSQL10 = "Select ship_to_cd from POSHPFIL_SQL where ship_to_cd = '" & ShipTo & "'"
With rs10
.activeconnection=ConWSH
.CursorType=adOpenForwardOnly
.CursorLocation=3
.open strSQL10
End with
'
If rs10.RecordCount > 0 Then
Exit Do
End If
Loop
'''do lots of stuff with your recordset?


Set rs10 = Nothing

 

[mrmovie]

hmm ultimately i still prefer the first post, perhaps renaming the function to GetValidRecordSet would be better.
i know the last post looks neater but it mixes GUI and function together, buried in a loop, not great in the long run, e.g. when someone says, 'to be honest Johnny, we dont really like the random popups, cant you put it on a nice little webpage or hta form?'

 

[Vack58]

Thanks a lot mrmovie, you are the man.