Prevent entry of duplicates

[RobertG]

Hi all,

I would like to prevent the entry of duplicate records in a table. Sounds
simple enough, but here's the catch:

1. The duplicates I'm trying to avoid are a combination of 5 fields
2. This is database with a SQL backend

I have tried to create a unique index based on these 5 fields; however, when
I use the upsizing wizard, I receive an error and this index does not make it
to the SQL server.

I know that I should try to keep things like this at the lowest level
possible (i.e. table), but was wondering if anyone had any suggestions about
how to do this from the form.

For your reference, these are the fields I am concerned about:

tblUtilization
Project - Text
SubObject - Text
SubSubObject - Text
ProgramIndex - Text
Allocation - Text

If anyone has any ideas about how to get this to work on either the table
level OR form level, I would greatly appreciate it!

Feel free to ask any questions, if you need additional info...

Robert

 

[Ofer Cohen]

Look at this link on "Preventing Duplicates from being entered" for an example

http://www.databasedev.co.uk/duplicates.html

taking the code from the form, and changing it to the fields you have

stLinkCriteria = "[Project ]='" & Me.Project & "' And [SubObject ] = '" &
Me.SubObject & "' And [SubSubObject] = '" & Me.SubSubObject & "' And
[ProgramIndex] = '" Me.ProgramIndex & "' And [Allocation] = '" &
Me.Allocation & "'"


Note: Make sure it in one line

 

[RobertG]

Hi Ofer,

What you've posted makes sense, but I am having one more issue with:

If DCount("WHAT GOES HERE?", "tblUtilization", _
stLinkCriteria) > 0

Any suggestions?

Robert

Look at this link on "Preventing Duplicates from being entered" for an example

http://www.databasedev.co.uk/duplicates.html

taking the code from the form, and changing it to the fields you have

stLinkCriteria = "[Project ]='" & Me.Project & "' And [SubObject ] = '" &
Me.SubObject & "' And [SubSubObject] = '" & Me.SubSubObject & "' And
[ProgramIndex] = '" Me.ProgramIndex & "' And [Allocation] = '" &
Me.Allocation & "'"


Note: Make sure it in one line
--
Good Luck
BS"D

Hi all,

I would like to prevent the entry of duplicate records in a table. Sounds
simple enough, but here's the catch:

1. The duplicates I'm trying to avoid are a combination of 5 fields
2. This is database with a SQL backend

I have tried to create a unique index based on these 5 fields; however, when
I use the upsizing wizard, I receive an error and this index does not make it
to the SQL server.

I know that I should try to keep things like this at the lowest level
possible (i.e. table), but was wondering if anyone had any suggestions about
how to do this from the form.

For your reference, these are the fields I am concerned about:

tblUtilization
Project - Text
SubObject - Text
SubSubObject - Text
ProgramIndex - Text
Allocation - Text

If anyone has any ideas about how to get this to work on either the table
level OR form level, I would greatly appreciate it!

Feel free to ask any questions, if you need additional info...

Robert

 

[RobertG]

Also, since this consists of many fields values, rather than doing this as
the BeforeUpdate of a control, I did it as the OnClick event of a command
button.

Is that the best way?

Look at this link on "Preventing Duplicates from being entered" for an example

http://www.databasedev.co.uk/duplicates.html

taking the code from the form, and changing it to the fields you have

stLinkCriteria = "[Project ]='" & Me.Project & "' And [SubObject ] = '" &
Me.SubObject & "' And [SubSubObject] = '" & Me.SubSubObject & "' And
[ProgramIndex] = '" Me.ProgramIndex & "' And [Allocation] = '" &
Me.Allocation & "'"


Note: Make sure it in one line
--
Good Luck
BS"D

Hi all,

I would like to prevent the entry of duplicate records in a table. Sounds
simple enough, but here's the catch:

1. The duplicates I'm trying to avoid are a combination of 5 fields
2. This is database with a SQL backend

I have tried to create a unique index based on these 5 fields; however, when
I use the upsizing wizard, I receive an error and this index does not make it
to the SQL server.

I know that I should try to keep things like this at the lowest level
possible (i.e. table), but was wondering if anyone had any suggestions about
how to do this from the form.

For your reference, these are the fields I am concerned about:

tblUtilization
Project - Text
SubObject - Text
SubSubObject - Text
ProgramIndex - Text
Allocation - Text

If anyone has any ideas about how to get this to work on either the table
level OR form level, I would greatly appreciate it!

Feel free to ask any questions, if you need additional info...

Robert

 

[RobertG]

Please, disregard my previous two messages. I got everything all figured out.

Thank you very much for your help! It works wonderfully!

Robert

Look at this link on "Preventing Duplicates from being entered" for an example

http://www.databasedev.co.uk/duplicates.html

taking the code from the form, and changing it to the fields you have

stLinkCriteria = "[Project ]='" & Me.Project & "' And [SubObject ] = '" &
Me.SubObject & "' And [SubSubObject] = '" & Me.SubSubObject & "' And
[ProgramIndex] = '" Me.ProgramIndex & "' And [Allocation] = '" &
Me.Allocation & "'"


Note: Make sure it in one line
--
Good Luck
BS"D

Hi all,

I would like to prevent the entry of duplicate records in a table. Sounds
simple enough, but here's the catch:

1. The duplicates I'm trying to avoid are a combination of 5 fields
2. This is database with a SQL backend

I have tried to create a unique index based on these 5 fields; however, when
I use the upsizing wizard, I receive an error and this index does not make it
to the SQL server.

I know that I should try to keep things like this at the lowest level
possible (i.e. table), but was wondering if anyone had any suggestions about
how to do this from the form.

For your reference, these are the fields I am concerned about:

tblUtilization
Project - Text
SubObject - Text
SubSubObject - Text
ProgramIndex - Text
Allocation - Text

If anyone has any ideas about how to get this to work on either the table
level OR form level, I would greatly appreciate it!

Feel free to ask any questions, if you need additional info...

Robert