Do...Until Loop Code

[Robin]

I have a main form called SalesOrders and a subform within called
OrderDetailsSubForm, which is a datasheet and normally contains multiple
records for each record in the main form.

I need to create a 'Do Until Loop' procedure in the main form to check each
subform datasheet record to see if a particular value exists in the
'ProductID' field.

Any code suggestions will be appreciated...

Rgds
Robin

 

[Dale Fye]

How do you intend to implement this?

Rather than looping through the recordset, use the FindFirst method.

Lets assume you have a command button on your main form, and you want to get
the value of ProductID via an input box, then the code might look like:

Private Sub cmdContainsProduct_Click

Dim rs as DAO.Recordset
Dim lngProductID as long, strMsg as string

lngProductID = clng(inputbox("What product ID are you looking for?"))
Set rs = me.OrderDetailsSubform.Form.Recordsetclone

rs.findfirst "[ProductID] = " & lngProductID
if rs.nomatch then
strMsg = "Not found"
else
strMsg = "Found"
endif
msgbox strMsg
rs.close
set rs = nothing

End Sub

If you want to use a loop then try:

Set rs = me.OrderDetailsSubform.Form.Recordsetclone
strMsg = "Not Found"
DO while not rs.eof
if rs("ProductID") = lngProductID then
strMsg = "Found"
exit Do
endif
rs.movenext
Loop
msgbox strMsg
rs.close
set rs = nothing

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Mike Painter]

Dales method will work but there is another way if you just want to see it.
Instead of using a datasheet view, create the form in a continuous form and
make it look like a data sheet. It's a bit of busy work but worth it.
In the form header put your search function if needed.
Use conditional formatting to set a color based on the ID you want to see.
Forms!yourform!yoursearchfield

I know this works in 2007 and should in other versions.