Older record disappearing when filtered

[yohansetiawan]

My database contain these:


- Products Table = [ID].AutoNumber, [Product].Text, [Active].Yes/No
- Orders Table = [ID].AutoNumber, [Product].Number, [Date].Date/Time, [Payment].Text
- Orders Form = [ID], [Product], [Date], [Payment]

I have [Product] in Orders Table and Orders Form as Combo Box for showing Product from Products Table.
And within product field on Orders, I want to make 'Active' product only showing up when choosing from combo box.
I made some records to test in Orders Form.
Then I filtered Product from Orders Form with query:

SELECT Products.ID, Products.Product, Products.Active
FROM Products
WHERE (((Products.Active) Like Yes));

Then I changed some product 'Active' to "No"
And the old record disappearing.
How to make older record not disappearing when I filter some of field not being available to choose (because Active product uncheck)

more in-depth, I attached my database, have a look at it Database1.zip

[June7]

That is the nature of comboboxes with filtered lookups. If you are going to review old orders that relate to inactive product record, can't be with a filtered combobox that excludes the inactive products. Could use VBA code to reset the combobox rowsource for orders with inactive product links.

Try this:

1. RecordSource of Orders: SELECT Orders.*, Products.Active FROM Products RIGHT JOIN Orders ON Products.ID = Orders.Product;

2. Code in the OnCurrent event of the form:
If Me!Active = No Then
Me.Product.RowSource = "SELECT Products.ID, Products.Product, Products.Active FROM Products;"
Else
Me.Product.RowSource = "SELECT Products.ID, Products.Product, Products.Active FROM Products WHERE (((Products.Active) Like Yes));"
End If
Me.Product.Requery

[yohansetiawan]

yes it works... but when I make datasheet type form, the code perform only when I clicked the row.. when that row (inactive product) not clicked, that row wouldn't display (like disappearing again)... maybe some code for datasheet form??

I've attached them, have a look

[June7]

Nope, that's what happens with datasheet. You can include the Product name field from Products table in the form for display in a textbox. Set textbox as Locked Yes and TabStop No.

You have the wrong join type in the query. Look at my suggestion again.

[yohansetiawan]

thanks June7