Filter query if item is sold in another table.

[smile]

So here goes

I have made this example database to show you my problem, I have 2 tables here:

1. "items" table that works like warehouse listing items in stock and has these fields:

ID autonumber
Example name TEXT
Sold Yes/No

2. "sales" table has these fields:
ID Autonumber
Example name created using Lookup in Design view

I created Query called "sales Query" that I use to enter data and my problem is I need only to be able to enter items that are not checked in "Items table".

In other words I need to be able to select in the query dropdown items currently in stock.

How to solve this the easy way? I tried using criteria "False" but dropdown still lists checked items WHY?

 

[ajetrumpet]

If you haven't got it yet, there is a query and a form in this file where you can select them...

 

[smile]

If you haven't got it yet, there is a query and a form in this file where you can select them...

Thanks very much I added "add new record" button and it did not worked until I changed ROW source

from :

SELECT items.[Example Name] FROM items WHERE items.sold=no;

To:

SELECT items.ID, items.[Example Name] FROM items WHERE items.sold=no;

Now it works OK.

 

[smile]

Just remembered to implement this sollution in one of my projects and got a few questions:

See form and answer these questions?

1.how to make SOLD checkbox default to TRUE when I fill new record?
2.Why incorrect record number is shown below?

I think I need a code to check the sold checkbox on the form when I click save button without user intervention.
Also I need to NOT so sell any items if the form is closed - what code I need to make it work?
The checkbox could be made invisible. But what code I need?

See attached database Rename to RAR

 

[smile]

I tried to make default value to YES but the I get error. Anyone can have alook at this ?

 

[smile]

Decided to post my sollution please do not hesitate to comment:

To check the checkbox automatically add a save record button and befor save command like paste this line of code:

Me.Sold = True

Sold is the name of checkbox.

To show correct record count add a textbox to form and set controlsource to:
=DCount("*","Not sold")

Not Sold - is a query name that list not sold items.

You can also disable navigation buttons by in Format tab view selectig No.