Preventing duplicates of a certain combination

[jonathanchye]

Sorry for all the question but working on a tough project so would really appreciate all the help I can get

I have two fields called FKVersionID and txtMachineName.

What I want to do is to prevent a duplicate of the same FKVersionID and txtMachineName ID combo.

For example there could be only one instance of txtMachineName = "Cutter" and FKVersionID = 1 but you can have txtMachineName = "Cutter" and FKVersionID = 2 etc.

Basically I have (for now) 3 machine types but they can be assigned to many versions. I want to only have one version assigned to the 3 at a time so I don't have 2 Cutters on the same version...

Is there a way to check this via VBA?

 

[jdraw]

You can make a composite key.

A key is set up containing two or more fields (combination) and their combined values must be unique.

just highlight both(all) columns in Table Design View before clicking on the little gold key icon in the toolbar

 

[jonathanchye]

You can make a composite key.

A key is set up containing two or more fields (combination) and their combined values must be unique.

Hmm I don't quite get this idea mate. The table I am referring to has the following fields :

PKMachineID (Autonumber)
FKVersionID (Foreign Key)
txtMachineName(Text field but limited to three possible values)
curHourlyRate(Currency)

PKMachineID is now the Primary Key (with the gold key(

If I highlight PKMachineID, FKVersionID and txtMachineName and click the Primary Key button it just says there could be only one Primary Key?

p/s: Although it gave a warning about the PK already being in a relationship.

 

[jdraw]

Johnathan,

Sorry for the confusion in previous post. Instead of the gold key , I should have said gold lightening flash??? You are absolutely correct that you can only have one primary key. What we are talking about here is a unique composite index.

I've attached a jpg showing
a table with a Primary Key called PKId (autonumber) and
a composite unique key called MyKey made up of
ID + phone1

and

the Index form showing the components and unique criteria.

 

[jonathanchye]

Hi,

Thanks for your reply I get what you mean now but if I set txtMachineName to be a unique index I don't think it would work in my case as I would still need to have more than 1 occurance of a machine called Cutter for example. I've attached a picture below to describe what I mean..



Uploaded with ImageShack.us

I've implemented a code using Dcount instead to check each time. I guess the problem could lie in my table design which might need more normalising. However, if I normalise more it would be very very difficult to implement the checks required for my project.

 

[jdraw]

I'm not sure what you've done with the DCount. But as far as a composite key made up of FKVersionID and txtMachineName you have to create a new key.

In my example the new key was MyKey; and it contained the 2 fields that I wanted to ensure that the combination of these 2 fields would never contain duplicates. That was your initial goal, right?

The key is called MyKey ; it contained fields ID and phone1. MyKey is unique. No duplicate values allowed.

 

[The_Doc_Man]

You need to have a compound key in which two fields participate in the same index, and for the index, the No Duplicates is turned on. You can have as many machines of the same exact name. You can have as many version IDs as you want with the same value. But you can only have one record of a given combination of machine name and version ID. The screen shot doesn't look right because a compound key doesn't have a separate index name in the second row of the compound-key index. All it has is a field name. I see three fields, three names, no compound keys.

 

[jonathanchye]

Ah yes, I didn't create any new index. All I did was highlight what I wanted to be unique and clicked on the "lighting" symbol. This brings up the window shown in the screenshot.

edit: I've created a new index called uniqueCombo like shown in the pictures :



Uploaded with ImageShack.us

However it doesn't appear in the table design itself. Is this correct? Does this guarantee that there won't be any duplicate combo of FKVersionID and txtMachineName appearing in tblMachines?

Thank you.

 

[jdraw]

Yes, that's the purpose of the unique index. It does NOT add a field to your table as such. It does its thiing "behind the scenes "--so to speak.

 

[jonathanchye]

Yes, that's the purpose of the unique index. It does NOT add a field to your table as such. It does its thiing "behind the scenes "--so to speak.

Thanks. I will try to quote out my current code to check and see if it works. What will happen if the user tries to create a duplicate value though? Is there a way to customise a message popup or perhaps some code?

 

[JANR]

You can use Dcount:

If DCount("*", "tblMachines", "FKVersionID=" & Me.x & " AND txtMAchineName = '" & Me.y & "'") > 0 Then
  'Duplicate detected
End If

replace x and y with the name of your formcontrol names.

JR

 

[jonathanchye]

You can use Dcount:

If DCount("*", "tblMachines", "FKVersionID=" & Me.x & " AND txtMAchineName = '" & Me.y & "'") > 0 Then
  'Duplicate detected
End If

replace x and y with the name of your formcontrol names.

JR

That's very close to the code I have now. I might modify it to count all wild card values instead of by unique key.

This is the code I am using as check if that helps anyone :

DCount("[PKMachineID]", "tblMachines", "[txtMachineName]= '" & Forms![frmMachines]![txtMachineName] & "' And [FKVersionID] = " & Forms![frmMachines]![Combo60] & "") > 1 Then
    MsgBox "A machine already exists for this Version Number"

 

[gemma-the-husky]

just to clarify

if you have a key set to unique (no duplicates) then access will give you a warning message and not allow you to add a record that would duplicate this key. it will give you a standard slightly cryptic access error, but you can use the forms "error" event to intercept the access error, and replace it with one of your own

now, using a dlookup in the before update pre-empts this test, by explicitly checking whether the combination exists, and is not strictly necessary.

one reason for having an explicit test though, is the look and feel. if you do the test immediately the key fields are entered (ie not at the time of the fromsd before update event), then the user can be immediately advised of a problem - which may be better than filling out loads of other stuff, and then finding the data is not valid

just one other thing. be careful of nulls. a unique composite key will accept duplicates if part of the key is null, which is not what you would expect.