HELP! Duplicating Data

[Jenny Lee via AccessMonster.com]

Hi,

I have created a database for an Art Gallery.They sell Artworks by
consignment, when an Atworks is sold the Artist recieves payment minus
commission. A customer may purchase a piece of artwork outright or put it
on layby. I have an Invoice form which has an artwork subform which uses
the Artwork_ID as a look up and all fields in the subform are automatically
filled in. I also have a Layby form which very simmilar to Invoice.
The Problem: When the user clicks on the Artwork_ID in the ARTWORK subform
a list of all Artwork_IDs is displayed. The problem is that even if an
Artwork has already been sold or put on Layby the user can enter it into
the system again.

What to do????

I have no idea how to solve this problem. Any Suggestions would be
appreciated!

Cheers
Jen

 

[John Vinson]

When the user clicks on the Artwork_ID in the ARTWORK subform
a list of all Artwork_IDs is displayed. The problem is that even if an
Artwork has already been sold or put on Layby the user can enter it into
the system again.

What to do????

Base the Combo on a Query which *excludes* sold and layby items; use
the Unmatched Query Wizard to create this query, or post back with a
description of the items, sale and layby tables.


John W. Vinson[MVP]

 

[Jenny Lee via AccessMonster.com]

Thank You!!! It worked a treat!!!

 

[Jenny Lee via AccessMonster.com]

Sorry! MAybe I jumped the gun!

I used the wizard to find records without a mathing invoice this worked
fine and I used the wizard to find records without a matching Layby record
and this worked fine. But how do I get the wizard to find records without a
matching invoice AND Layby. The problem is in the InvoiceLine subform still
shows records on Layby and the LaybyDetail subform still shows records on
Invoice.

Thanks

Jen

 

[John Vinson]

I used the wizard to find records without a mathing invoice this worked
fine and I used the wizard to find records without a matching Layby record
and this worked fine. But how do I get the wizard to find records without a
matching invoice AND Layby. The problem is in the InvoiceLine subform still
shows records on Layby and the LaybyDetail subform still shows records on
Invoice.

You'll need a query using a Frustrated Outer Join to *both* Invoice
and Layby tables. Since I don't know the structure of your tables, nor
how your forms are set up, I can't be specific; but it would be
*something like*

SELECT <whatever>
FROM Items
(LEFT JOIN Invoices ON Invoices.ItemID = Items.ItemID)
LEFT JOIN Layby ON Layby.ItemID = Items.ItemID
WHERE Invoices.ItemID IS NULL
AND Layby.ItemID IS NULL
ORDER BY <something reasonable>

This query will return only those records in the Items table which
have no matching record in *either* of the other two tables, and can
be used as the Rowsource for a combo box on either subform.

John W. Vinson[MVP]