Help with record duplicates! (1 Viewer)

swell

Member
Local time
Today, 18:00
Joined
Mar 10, 2020
Messages
77
Hi,
I am having problems with a data entry form. I am trying to prevent duplicate entries in a table the criteria has to be unique over 4 fields.
Namely "BookNumber", "Date of Activity", "Activity", "Notes" these are the only fields I can combine to form a unique record index.
I have an index of these fields which is set to no duplicates, it doesn't produce the normal error message for duplicates. (this used to work not sure why it has stopped working).
On investigation of the problem I have seen many suggestions how duplicates would be better handled using "BeforeUpdate" in VBA as can be seen from the following snippet of my code I am using the DLookup function as suggested in this forum in testing the code I get the following error.

Run time error 2471
The expression you entered as a query returned the following error: "nul"

I wouldn't be surprised if my construct of the code is my problem.

Is there a better way to handle duplicates?
I thought checking for duplicates before or on entry to the "Hours" field would be a better place to check rather than when the user clicks the "Enter record" button! Thoughts and suggestions on this would also be appreciated.


Duplicates Member activity form.png


Here is a snip of the Table design

Member activity table.png




--
The portion of code in question

Code:
  Answer = DLookup("[Entry ID]", "Member Activity", "[BookNumber]<>" & BookNumber & _
   " AND BookNumber=" & BookNumber & " AND [Date of Activity]=" & [Date of Activity] & _
     " And Notes =" & Notes & " AND Activity=" & Activity)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:00
Joined
May 7, 2009
Messages
19,169
using DLookup will result to Null if it does not find a matching record.
instead use DCount(), it will return 0 if there is no match.
otherwise it will return Numeric value to the number of records that match
the criteria.

You are using Entry ID as Primary key, you use the 4 fields in your table as Primary Key.
 

swell

Member
Local time
Today, 18:00
Joined
Mar 10, 2020
Messages
77
using DLookup will result to Null if it does not find a matching record.
instead use DCount(), it will return 0 if there is no match.
otherwise it will return Numeric value to the number of records that match
the criteria.

You are using Entry ID as Primary key, you use the 4 fields in your table as Primary Key.
Umm! the "nul" in the error message comes from the field "Note" MY bad I should have used a different string to test.

I changed the Primary key as you suggested also I disabled the "BeforeUpdate" event I now get the following error "You can't go to the specified record." I expected the standard duplicates error message.
 

swell

Member
Local time
Today, 18:00
Joined
Mar 10, 2020
Messages
77
Hmm when I closed the form to design view I get the duplicates error message
I re-enabled the "Before Update" procedure also used a different duplicate record entry
"dummy" is the text in the "Note" field

Here is the error message.
Vba error 2471.png


Is the code correct to obtain the result I am trying to achieve?
Here is a snip of the index for "Member Activity" table.
member activity duplicates index.png
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:00
Joined
May 7, 2009
Messages
19,169
change your Note to Short Text.
 

isladogs

MVP / VIP
Local time
Today, 18:00
Joined
Jan 14, 2017
Messages
18,186
@swell
As you have a composite index of 4 fields, there are no duplicates in the data shown in post #1.
For example, entryIDs 589/590 have three identical fields but as the book numbers are different, the records aren't duplicates.
 

cheekybuddha

AWF VIP
Local time
Today, 18:00
Joined
Jul 21, 2014
Messages
2,237
Hi,

In addition to what arnelgp and isladogs say above, you need to check the criteria of your DCount() expression:
Code:
... ,
"[BookNumber]<>" & BookNumber & _
   " AND BookNumber=" & BookNumber & " AND [Date of Activity]=" & [Date of Activity] & _
     " And Notes =" & Notes & " AND Activity=" & Activity)

How can [BookNumber] <> BookNumber and also BookNumber = BookNumber at the same time?!!
I guess [Date of Activity] is date and so the value you pass must be delimited with octothorpes (hash marks).
Notes is a string value and must be delimited with quote marks.

Your code should look more like:
Code:
Answer = DCount("*", "Member Activity", _
          "[BookNumber] = " & BookNumber & _
          " AND [Date of Activity] = " & Format([Date of Activity], "\#yyyy\-mm\-dd\#") & _
          " AND Notes = '" & Notes & "'" & _ 
          " AND Activity = " & Activity)
 

Micron

AWF VIP
Local time
Today, 14:00
Joined
Oct 20, 2018
Messages
3,476
Wasn't sure - haven't created a composite PK for a long time. The idea has more detractors than followers, but when I challenge anyone to show why and not just some site that says don't, I never get that answered. Where I worked, they were used quite a lot in a large corporate IT - built db.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:00
Joined
Feb 19, 2002
Messages
42,971
It is better to have a single field primary key. That will be used for all relationships. Make a unique index to enforce the business rule that you want the four columns as a set to be unique. This is a little tricky since you cannot set this up using the table design. You actually have to open the indexes dialog. Here is a relevant picture. In your case, you would set the primary key value of the multiple index back to no which will remove the key icon and then go to the BookNumber index and set it to primary and unique.
uniqueIDX2.JPG
 

swell

Member
Local time
Today, 18:00
Joined
Mar 10, 2020
Messages
77
@swell
As you have a composite index of 4 fields, there are no duplicates in the data shown in post #1.
For example, entryIDs 589/590 have three identical fields but as the book numbers are different, the records aren't duplicates.
The data that produced the error is shown in the snip of the form which is a duplicate of entryID 591. ("BeforeUpdate event).
I need to get a warning that the user is about to enter a duplicate record.
 

swell

Member
Local time
Today, 18:00
Joined
Mar 10, 2020
Messages
77
Hi,

In addition to what arnelgp and isladogs say above, you need to check the criteria of your DCount() expression:
Code:
... ,
"[BookNumber]<>" & BookNumber & _
   " AND BookNumber=" & BookNumber & " AND [Date of Activity]=" & [Date of Activity] & _
     " And Notes =" & Notes & " AND Activity=" & Activity)

How can [BookNumber] <> BookNumber and also BookNumber = BookNumber at the same time?!!
I guess [Date of Activity] is date and so the value you pass must be delimited with octothorpes (hash marks).
Notes is a string value and must be delimited with quote marks.

Your code should look more like:
Code:
Answer = DCount("*", "Member Activity", _
          "[BookNumber] = " & BookNumber & _
          " AND [Date of Activity] = " & Format([Date of Activity], "\#yyyy\-mm\-dd\#") & _
          " AND Notes = '" & Notes & "'" & _
          " AND Activity = " & Activity)

I used your code and it worked ok except when I close the form (even after a correct entry) I get the following error msg.
vba error 3075.png


Should I put quotes around the Activity field?
 

cheekybuddha

AWF VIP
Local time
Today, 18:00
Joined
Jul 21, 2014
Messages
2,237
Try changing the last part of the criteria to:
" AND Activity = " & Nz(Activity, 0))

hth,

d
 

swell

Member
Local time
Today, 18:00
Joined
Mar 10, 2020
Messages
77
Gentlemen thank you for your assistance and direction on this problem.
I implemented the changes cheekybuddha gave me.
The further problem that that created and another error I was getting have disappeared by moving the code to "On Got Focus" event of the "Hours" field. The duplicate record check is performed clear the data then set focus to the first field of the form.
 
Last edited:

swell

Member
Local time
Today, 18:00
Joined
Mar 10, 2020
Messages
77
Try changing the last part of the criteria to:
" AND Activity = " & Nz(Activity, 0))

hth,

d
oops I typed without checking I will see if that change makes a difference (for my further education ).
But thank you for the edit and correction you gave me earlier.
 

swell

Member
Local time
Today, 18:00
Joined
Mar 10, 2020
Messages
77
Try changing the last part of the criteria to:
" AND Activity = " & Nz(Activity, 0))

hth,

d
I implemented this change in the "Before Update" event which fixed that error but the other error I was getting (which I didn't mention) ("You can't go to the specified record.") was still being produced
Moving the code actually fixed both errors and also the warning message, about duplicate record, happens at a point more appropriate in the flow of data input.

At this point I have to express my ignorance.
The field "Activity" is non zero so what does this change actually do?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:00
Joined
May 7, 2009
Messages
19,169
add Nz to all criteria just to be sure:

Answer = DCount("*", "Member Activity", _
"[BookNumber] = " & Nz(BookNumber, -1) & _
" AND [Date of Activity] = " & Format(Nz([Date of Activity], 0), "\#yyyy\-mm\-dd\#") & _
" AND Notes = '" & Nz(Notes, "@#$%^") & "'" & _
" AND Activity = " & Nz(Activity, -1)
 

swell

Member
Local time
Today, 18:00
Joined
Mar 10, 2020
Messages
77
add Nz to all criteria just to be sure:

Answer = DCount("*", "Member Activity", _
"[BookNumber] = " & Nz(BookNumber, -1) & _
" AND [Date of Activity] = " & Format(Nz([Date of Activity], 0), "\#yyyy\-mm\-dd\#") & _
" AND Notes = '" & Nz(Notes, "@#$%^") & "'" & _
" AND Activity = " & Nz(Activity, -1)
Thanks for your response, I don't understand what these changes do to the logic of the code particularly why -1 in place of 0 for & Nz(Activity, -1) ?
 

Micron

AWF VIP
Local time
Today, 14:00
Joined
Oct 20, 2018
Messages
3,476
EDITED
In Access 0 is always false. True is typically -1 (but in some situations, True can be any number other than -1). Obviously that excludes 0 and if that's not clear, review the first sentence!
So -1 and 0 are numeric representations of T and F.
Yes/No and On/Off are textual representations of -1 and 0
 

Users who are viewing this thread

Top Bottom