Adding Records to a table but preventing duplicates + other tests

  • Thread starter Thread starter GLT
  • Start date Start date
G

GLT

Hi,

I have a table which has three fields Type, Server and Service (table and
sample data below):

Type Server Service

DEV TEST12 Performance
PROD ALL Stats
DEV ALL Dummy Data


On my form, I use an option group to select the (a) individual server or (b)
ALL servers (See Server field above). I use the following code to add
records to the table when an 'ADD' button is clicked, which works fine:

Dim strSQL As String

strSQL = "INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type" & _
", IIf([FORMS]![frmAddPermNoMon]![Frame7]=2, 'ALL', " & _
"[FORMS]![frmAddPermNoMon]![fldSelServer]) AS Server" & _
", [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"


DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

Me![tblPermSrvcsIgnore SubForm].Requery

' Set the selected Service to 0 for ALL Servers

DoCmd.RunSQL "UPDATE Services SET Services.Valid = 0 WHERE
(((Services.Service) Like [FORMS]![frmAddPermNoMon]![fldSelSevice]));"

ElseIf Me![Frame7] = 1 Then

' Set the selected Service to 0 for Selected Server only

DoCmd.RunSQL "UPDATE Services SET Services.Valid = 0 WHERE
(((Services.Server) Like [FORMS]![frmAddPermNoMon]![fldSelServer]) AND
((Services.Service) Like [FORMS]![frmAddPermNoMon]![fldSelSevice]));"

End If

DoCmd.SetWarnings True

Me![tblPermSrvcsIgnore SubForm].Requery


The above works great except I can press the 'ADD' button and add as many
duplicates of the same records as I please, so in order to keep this table
slim and relevant, I want to place the following checks in place:

1) If I try to add the same record twice, then reject it

2) If a record where 'Server' = ALL, and Service = TESTDATA, and I try to
add another record for an individual server (e.g. TEST12) which also has a
Service = TESTDATA, then reject it (i.e. while a record exists for ALL
servers with a service name, do not allow records individual servers to be
added with the same service name)

3) If there are individual Servers with the Service = TESTDATA that exist in
the table, and a new record where Server=ALL is added (with the Service =
TESTDATA), then remove all the individual records with that service name
first, before adding the new Server=ALL record.

In essence what I am trying to achieve is to prevent duplicates, but also if
there is an entry where Server=ALL, then that entry overrides and prevents
individual entries where the Service field has the same value.

Any assistance would be greatly appreciated...

Cheers,
GLT.
 
On Sun, 31 Jan 2010 17:00:01 -0800, GLT

If you can currently add several exact duplicate records to the table,
you need a unique index over these fields. That will stop it cold.
Then your error handler will trap those errors, and you can inform the
user.

-Tom.
Microsoft Access MVP

Hi,

I have a table which has three fields Type, Server and Service (table and
sample data below):

Type Server Service

DEV TEST12 Performance
PROD ALL Stats
DEV ALL Dummy Data


On my form, I use an option group to select the (a) individual server or (b)
ALL servers (See Server field above). I use the following code to add
records to the table when an 'ADD' button is clicked, which works fine:

Dim strSQL As String

strSQL = "INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type" & _
", IIf([FORMS]![frmAddPermNoMon]![Frame7]=2, 'ALL', " & _
"[FORMS]![frmAddPermNoMon]![fldSelServer]) AS Server" & _
", [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"


DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

Me![tblPermSrvcsIgnore SubForm].Requery

' Set the selected Service to 0 for ALL Servers

DoCmd.RunSQL "UPDATE Services SET Services.Valid = 0 WHERE
(((Services.Service) Like [FORMS]![frmAddPermNoMon]![fldSelSevice]));"

ElseIf Me![Frame7] = 1 Then

' Set the selected Service to 0 for Selected Server only

DoCmd.RunSQL "UPDATE Services SET Services.Valid = 0 WHERE
(((Services.Server) Like [FORMS]![frmAddPermNoMon]![fldSelServer]) AND
((Services.Service) Like [FORMS]![frmAddPermNoMon]![fldSelSevice]));"

End If

DoCmd.SetWarnings True

Me![tblPermSrvcsIgnore SubForm].Requery


The above works great except I can press the 'ADD' button and add as many
duplicates of the same records as I please, so in order to keep this table
slim and relevant, I want to place the following checks in place:

1) If I try to add the same record twice, then reject it

2) If a record where 'Server' = ALL, and Service = TESTDATA, and I try to
add another record for an individual server (e.g. TEST12) which also has a
Service = TESTDATA, then reject it (i.e. while a record exists for ALL
servers with a service name, do not allow records individual servers to be
added with the same service name)

3) If there are individual Servers with the Service = TESTDATA that exist in
the table, and a new record where Server=ALL is added (with the Service =
TESTDATA), then remove all the individual records with that service name
first, before adding the new Server=ALL record.

In essence what I am trying to achieve is to prevent duplicates, but also if
there is an entry where Server=ALL, then that entry overrides and prevents
individual entries where the Service field has the same value.

Any assistance would be greatly appreciated...

Cheers,
GLT.
Click to expand...
 
Hi Tom,

Thanks for your response.

The only way I can make each record unique is to create a unique ID
consisting of the fields Type, Server, Service added all together.
Considering that the Service field can be up to 160 characters long, that
makes an extremely long unique ID. I understand I need something unique to
compare each addition to; is there any other way I can get around this rather
then adding the three fields together?


Tom van Stiphout said:
On Sun, 31 Jan 2010 17:00:01 -0800, GLT

If you can currently add several exact duplicate records to the table,
you need a unique index over these fields. That will stop it cold.
Then your error handler will trap those errors, and you can inform the
user.

-Tom.
Microsoft Access MVP

Hi,

I have a table which has three fields Type, Server and Service (table and
sample data below):

Type Server Service

DEV TEST12 Performance
PROD ALL Stats
DEV ALL Dummy Data


On my form, I use an option group to select the (a) individual server or (b)
ALL servers (See Server field above). I use the following code to add
records to the table when an 'ADD' button is clicked, which works fine:

Dim strSQL As String

strSQL = "INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type" & _
", IIf([FORMS]![frmAddPermNoMon]![Frame7]=2, 'ALL', " & _
"[FORMS]![frmAddPermNoMon]![fldSelServer]) AS Server" & _
", [FORMS]![frmAddPermNoMon]![fldSelSevice] AS [Service Name];"


DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

Me![tblPermSrvcsIgnore SubForm].Requery

' Set the selected Service to 0 for ALL Servers

DoCmd.RunSQL "UPDATE Services SET Services.Valid = 0 WHERE
(((Services.Service) Like [FORMS]![frmAddPermNoMon]![fldSelSevice]));"

ElseIf Me![Frame7] = 1 Then

' Set the selected Service to 0 for Selected Server only

DoCmd.RunSQL "UPDATE Services SET Services.Valid = 0 WHERE
(((Services.Server) Like [FORMS]![frmAddPermNoMon]![fldSelServer]) AND
((Services.Service) Like [FORMS]![frmAddPermNoMon]![fldSelSevice]));"

End If

DoCmd.SetWarnings True

Me![tblPermSrvcsIgnore SubForm].Requery


The above works great except I can press the 'ADD' button and add as many
duplicates of the same records as I please, so in order to keep this table
slim and relevant, I want to place the following checks in place:

1) If I try to add the same record twice, then reject it

2) If a record where 'Server' = ALL, and Service = TESTDATA, and I try to
add another record for an individual server (e.g. TEST12) which also has a
Service = TESTDATA, then reject it (i.e. while a record exists for ALL
servers with a service name, do not allow records individual servers to be
added with the same service name)

3) If there are individual Servers with the Service = TESTDATA that exist in
the table, and a new record where Server=ALL is added (with the Service =
TESTDATA), then remove all the individual records with that service name
first, before adding the new Server=ALL record.

In essence what I am trying to achieve is to prevent duplicates, but also if
there is an entry where Server=ALL, then that entry overrides and prevents
individual entries where the Service field has the same value.

Any assistance would be greatly appreciated...

Cheers,
GLT.
Click to expand...
.
Click to expand...
 
Hi Tom,

Thanks for your response.

The only way I can make each record unique is to create a unique ID
consisting of the fields Type, Server, Service added all together.
Considering that the Service field can be up to 160 characters long, that
makes an extremely long unique ID. I understand I need something unique to
compare each addition to; is there any other way I can get around this rather
then adding the three fields together?
Click to expand...

It is not necessary (nor is it even a good idea!) to create a new field.

Tom is suggesting that you use the Indexes tool to create a unique Index on
the three fields. An index can consist of up to ten fields, and 160 bytes is a
largish index but not unreasonable.
 
Hi John,

Thanks for your reply, sorry just need to clarify as I have never used
indexing other than for the primary key.

Are you saying if I set the 'Indexed' property to 'Yes - No Duplicates' for
each of the three feilds, that this will stop only duplicates where the three
feilds are exactly the same?
 
Hi Gents,

I just set each of the three fields to indexed - No duplicates and this does
nt work, each of the feilds can have duplicates... its just the three fields
added together cannot be duplicate... hope this makes sense
 
Ok worked it out, had to remove the ID primary key.

Thanks for your assistance John / Tom, and for anyone else who wants to use
this feature here it is:

Prevent duplicate values from being entered into a combination of fields

Create a multiple-field index using the fields you want to prohibit
duplicate values for. Leave the Indexes window open when you have finished
defining the index.
How?

Open the table in Design view.
Click Indexes on the toolbar.
In the first blank row in the Index Name column, type a name for the index.
You can name the index after one of the index fields, or use another name.
In the Field Name column, click the arrow and select the first field for the
index.
In the next row in the Field Name column, select the second field for the
index. (Leave the Index Name column blank in that row.) Repeat this step
until you have selected all the fields you want to include in this index.
Note The default sort order is Ascending. Select Descending in the Sort
Order column of the Indexes window to sort the corresponding field's data in
descending order.

In the upper portion of the Indexes window, click the new index name.
In the lower portion of the Indexes window, click the Unique property box,
and then click Yes.

Thanks to Scottgem for the above info...
 
Ok worked it out, had to remove the ID primary key.
Click to expand...

If the ID is the concatenated redundant field, that's true. However your table
cartainly SHOULD have a primary key; it could be a three-field primary key if
that's what you want. To get that, you could open the table in design view,
ctrl-click all three fields (so they're all highlighted), and click the Key
icon.
Thanks for your assistance John / Tom, and for anyone else who wants to use
this feature here it is:
Click to expand...

Sorry you had to go digging - I should have explained this technique in
detail, it's certainly not obvious from the Indexes window!
 
Back
Top