Stop Users from inserting Duplicate values!

[MarkNie]

Hi

Just wondering if anybody can shed some light on this for me.

I have a table which had the following fields:

tblrepdetails:
id = Autonumber
TrackingID = Number
Action = Text with combo box for (Attn, IP, SLA)
Date = Date/Time
Intls = Text
Time to complete = Number

The problem I have is that I have queries which do bulk sign off of the Action field, which allows duplicates as there are teh same sign off's for different accounts.

What I want is for a box to appear when the Action has already been signed off when I run the Bulk sign off querie.

The table looks something like this

Tracking ID Action Date Intls Time to cmplete

731 SLA 01/01/06 MA 03:00
731 Attn 01/01/06 MA 03:00
731 IP 01/01/06 MA 03:00
732 SLA 01/01/06 MA 03:00
732 Attn 01/01/06 MA 03:00
732 IP 01/01/06 MA 03:00

So when I run the querie to sign off 731 SLA it should pop up a message box saying that it has already been signed off any ideas how to do this would be greatly appreciated.

Thanks
Regards
Mark

 

[rjoubert]

Use the DCount function to check if a particular Tracking ID and Action combination already exists in the table. If the DCount function returns 1 or more, pop up a warning message.

If DCount("[Tracking ID]", "YourTableName", "[Tracking ID]=731 AND [Action]='SLA'") > 0 Then
   MsgBox "This Tracking ID/Action has already been signed off."
End If

 

[MarkNie]

Hi rjoubert

Thanks that works great, do you by any chance know how I can make it that the user can't enter an the record.

Thanks again for your help!!

Regards
Mark

 

[rjoubert]

That depends on how you're entering the record. Can you post your code?

 

[MarkNie]

Hi

Don't have code for it I think. (Sorry not very good with code.) I am just working with a table which I have inserted into a form. Then I have a button which runs a macro which imports the Action and Tracking ID into the table from a excel spreadsheet.

Thanks
Regards
Mark

 

[rjoubert]

Then there is code behind your button (which runs the macro). In your form's design view, right click on the button and select the Build Event option. That should give you a view of your code. Please post that code.

 

[lespaul]

in your table design, add an index to the fields - Tracking ID and Action, you can multi select with Shift or Ctrl to select the fields that shouldn't be duplicated and set the index to 'No duplicates'. Access will give an error when trying to add a duplicate value.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[acent]

Another way to stop the duplicate may be to work it at the table level and not the form level. It may be worth it to take a look at using a table index on a field.

Just a thought. It seems like you two have a handle on the problem as is.

"If you say you can, or you say you can't, you're right!"
-- Henry Ford