Duplicates allowed yes or no:

[Guest]

I have an invoice database and I had no duplicates allowed on the invoice
numbers until 2 of my vendors ended up with like invoice numbers. I was
going to allow duplicates but I want to make sure that each vendor is only
getting paid once. How do I set up my table to look at the vendor name AND
invoice number when determining if it is truly a duplicate invoice or just a
"like" invoice number by another vendor?

 

[Jeff Boyce]

Tracey

Open the table in design view. Click on the Indexes button. Create a new
index (name it) and select both fields (vendor, invoice#) - use two rows to
do this, but only one "name" for the index. Highlighting the row in which
it is named, change the property to "No duplicates".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

You can set up a multi-field index that will only allow one of each
combination of vendor and invoice number.

To create a multiple field unique index (Compound index)
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter Name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are
included.
--Close the index window and close and save the table

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I thought it worked at first, and it did in just the invoice table but the
table has a subdatasheet that is tied by invoice numbers. I there a way to
correct this, too?

When I went to enter an invoice number that was the same as another vendors,
it let me add it but it dropped down the invoice coding that was tied to the
original entry.

:-( Can that be fixed?

 

[John W. Vinson]

I thought it worked at first, and it did in just the invoice table but the
table has a subdatasheet that is tied by invoice numbers. I there a way to
correct this, too?

When I went to enter an invoice number that was the same as another vendors,
it let me add it but it dropped down the invoice coding that was tied to the
original entry.

:-( Can that be fixed?

You'll need to add the company to the "child" table as well, and use
both fields in the Join for the relationship.

I'd strongly urge using a Form with a Subform, rather than a table
datasheet and subdatasheet for data entry and editing. Datasheets are
*very* limited.

John W. Vinson [MVP]