Prevent Duplicate Records

[Guest]

I'm putting together a small database to keep track of process flow diagrams.

Each diagram has a unique diagram "number", e.g.

R-15100-500-O

The R-15100-500 is the number and the -O bit is the revision; O for original
and then A, B, C etc as the diagram evolves.

I need to store a record of all revisions for a given diagram and I enter
this through a simple form that has a control for the number and a drop-down
combo for the revision.

What I want to prevent is the same, combined, number being used. i.e I can
have many diagrams R-15100-500 but only one with the unique suffix -O, -A etc.

I can join these two together on the form and have toyed with how to get
that, now unique, identifier back to a field in my table but posts I have
read point out that this is not good practice.

I could change the way I enter the identifier and get rid of the drop down
revision box but it would be easier for my users to leave as is.

Has anyone any pointers? Appreciate any help.

Andy

 

[Douglas J Steele]

Create a unique index on the combination of fields. You can have up to 10
fields in a single index.

 

[Guest]

Hi Andy,

Store the identifier in two fields: one for the main number and one for the
suffix. In table design view, click on View > Indexes to open the Indexes
window. Create a multi-field index that is unique on the two fields taken
together. To do this, assign an Index Name in the left-hand column. Pick the
first field in the Field Name column. Then pick the suffix field in the very
next row, *without* assigning an Index Name. In the lower portion, select Yes
for the middle choice that reads Unique.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Douglas

Thank you - appreciate your help.

Andy

 

[Guest]

Tom

Many thanks for you help - much appreciated.

Andy

 

[Guest]

Hi Andy,

You're welcome. I'm glad it worked for you.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________