Do...Until Loop Code

  • Thread starter Thread starter Robin
  • Start date Start date
R

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
 
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.
 
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.
 
Back
Top