No Duplicates for Combo Field

  • Thread starter Thread starter LDMueller
  • Start date Start date
L

LDMueller

I have a table with which includes two fields, FileNo and PublicationNo.

Normally if I'm referencing one field, I can control it so there can't be
duplicates entered. In this case, I need to control no duplicates for the
combination of these two fields. Basically, one PublicationNo can belong to
several FileNo.

For example:
FileNo PublicationNo
125 123456
126 123456
127 123456
125 456789
127 456789

I need some direction to get started. Any assistance would be greatly
appreciated.

Thanks!
LDMueller
 
Are you entering your data directly into a table with Lookupfields, rather
than a form with proper combo boxes. If yes, you'll need to explain this
when writing in, because doing this hides the real structure of your
database. (It also means you are using the wrong tool for the job)

It *sounds* as if you need this structure

TblFile
FileID (Primary Key - can be an Autonumber)
FileNo (In Table Design, you can use the Index button to make this a Unique
field
other fields which concern only the file and not the publication


TblPublication
PubID (PK)
PublicationNo

TblFilePublication
FilePubID (PK)
FileID (linked from TblFile)
PubID (Linked from TblPublication


To ensure that the same file is not linked twice with the same publication
you can set up a Unique Index.

In Design View of TblFilePublication
click on the Index button on the toolbar (or find where its equivelent is
concealed, if you have WierdAcc 2007)

In the first column type a name for your index eg

FilePub

In the next column choose PubID
In the next row down, put nothing into the first column, but in the second
column choose FileID
Click back on the word FilePub

In the grid below the columns you will see the word Unique
Choose Yes next to that.

Back to your form (this should be based on TblFilePublication).

One combo should be based on TblFile, the other should be based on
TblPublication


Evi
 
Create an index that includes the two fields and specify it must be unique.
Then trap for the error if a user tries to create a duplicate combination. I
don't remember the error number off the top of my head, but you can get it by
purposely making it error out. Then just trap for that error in your error
handling code.
 
Back
Top