Checking through queries at run time

[pompeyjim]

How do I check through all the records in a query/table at run time when I go
to the next/previous record in a form.

 

[Duane Hookom]

What do you mean by "check through all the records"?
What is your objective?

 

[pompeyjim]

I’ve got a Main form (frmOrders) with a subform (fsubCategories). The main
form has the customers details, (like name, address etc) date and order
number. The subform has the products and the products categories.
When the user wants to view historical orders, some will include products
that have been deleted. The deleted items are still stored in the tblProducts
table. When the form is loaded or I go to the next record I need to check for
the deleted items (which are checked in a check box in the query/table). If
there are any products that have been deleted, I need to change the Rowsource
property for the combo box cboProduct to qryDeletedProducts so that if I'm
viewing past orders that were placed before the product was deleted then the
deleted product shows up in the cboProduct drop down list. Ordinarily,
cboProduct's Rowsource will be qryProduct which doesn't include the deleted
products in it so they don't show up in the cboProduct list. The LimitToList
property cannot be set to No. The ControlSource remains the same.



This is because if the user is putting in new records then she doesn't want
to view the deleted products in the drop down list but if she is viewing
historical records then she needs to be able to see what product was sold on
a certain date even if it has been deleted.

 

[Paul Overway]

In Form_Current...

If Me.NewRecord Then
Me.cboProduct.RowSource = "qryCurrentProducts"
Else
Me.cboProduct.RowSource = "qryAllProducts"
End if

FWIW...you might need some validation code to ensure that users don't swap a
current product for an obsolete product. Or make sure this view is
read-only.