Full Version: Do not allow duplicates in a group of records
UtterAccess Forums > Microsoft® Access > Access Forms
Palyn
I have a form that adds records of weighed bales for a delivery
Each record in the bales table has a unique auto number
then a clients reference bale number (as instructed by the client) and this number must be unique for each bale in this delivery only.
So for each delivery the bales have a unique clients bale number - i.e. bales 1, 2, 3, 4 and this number for this group of records must not be duplicated.
The next delivery can have another set of bales with the clients bale numbered 1,2,3,4
Thanks in advance for any help.
Lyn
RuralGuy
If you have a field that indicates the delivery then combine this field and the bale number in a unique index so access will throw an error (3022) when you duplicate. Then you can catch it in your error handler.
Palyn
Thankyou for your reply.
Could you help me a little more on how to do this.
Lyn
RuralGuy
Do you have a field in your record that indicated the delivery? Maybe a date or something. Do you know how to create an index on two fields combined?
Palyn
Yes each delivery has a unique number. I don't know how to create an index though.
RuralGuy
From the design mode of the table go to View>Indexes. Use your delivery number as the index and drop to the second line and select the BaleNumber field in the middle column as the second element of your index. You will see that the default is a unique index, which is what you want. The rest is in your error handling code when adding the bales.
Palyn
Thankyou for your reply.
Would you mind helping me with error handling code also.
Lyn
RuralGuy
That really depends on how you are adding the record. Have you implemented any error handling on your form yet? You may be able to just put code in the OnError event of the form.
Palyn
I am adding the record in a continuous subform of the delivery form. I have just the default onError at the moment.
I want put a message on the onerror event of the subform.
Thankyou so much for your help.
I will try that.
Lyn
RuralGuy
The SubForm Error event will look something like:
CODE
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
   MsgBox "This Bale Number has already been used!" & vbCrLf & vbCrLf & _
          "Please enter a different Bale Number."
   Response = acDataErrContinue
   Me.txtBaleNumber.SetFocus
End If
End Sub

Using your name for the BaleNumber control of course.
Palyn
Thankyou so much.
Youare awesome.
Ohaven't time to do this now but will do tonight.
Thanks
Lyn
Palyn
At last I have had time to do this.
Unfortunately I can't get it to work.
I have created the index
DocketClientBaleNumber
field names
Docket #
BALE_NUM
and set unique to yes
I have done something wrong as when I add a bale I don't get an error
Any help would be greatly appreciated
Thanks
Lyn
RuralGuy
It sounds like you have done it correctly Lyn. Any chance you could post a stripped down version of your db that demonstrates the problem? Remove any sensitive data but leave enough sample data so we can see the problem.
Palyn
Thanks for the offer but I just wouldn't know where to begin.
This thing has grown like topsy over many years and its huge, it was a project I inherited and this is a small part of it.
The delivery is even more complicated than I have described.
Is there another way I could go about this?
Perhaps with record sets? (I don't know anything much about these either)
Thanks again
Lyn
RuralGuy
I'm not sure why it is not working. What type of fields are [Docket #] and [BALE_NUM]? You could do the lookup yourself in the BeforeUpdate event of the BaleNumber control as another means of accomplishing the task. Actually, doing the lookup might tell us why the indexing method isn't working.
Palyn
I am such an idiot, I indexed the wrong field - it should have been [BALE_CNUM] it wasn't till I saw it in your post I realised it.
So sorry for wasting your time with the last post.
I have tried changing the index but I get this message
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. (Error 3022)"
which of course is the error we are trying to create.
I should have posted at the begining that this is a feature the storeman has just requested and there is years of data that has been allowed to have dulpicates.
I am not sure how many records this applies to (there are 79000 bales), is there a quick way for me to find these duplicate records.
Thanks agin for your help and apologies again for my error
Lyn
RuralGuy
There is a "Find Duplicates Query Wizard". Give it a try and have fun. Post back when you need further assistance.
Palyn
Thankyou so much for your patience.
I will try that now, the first thing I have found is that some bales are entered into the system without a docket number (Repressed from odds and ends in the store) and so the docket # is "0"
I have grasped the principle though so will work through it.
Many thanks again
Lyn
Palyn
Okay I am there.
I have created a new field to record the docket number so We can start this from a certain date.
Many thanks
Lyn
RuralGuy
Glad to help Lyn. Much success with the rest of the project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.