Help with record duplicates!

[swell]

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.

Here is a snip of the Table design

--
The portion of code in question

  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]

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]

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]

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.

Is the code correct to obtain the result I am trying to achieve?
Here is a snip of the index for "Member Activity" table.

 

[arnelgp]

change your Note to Short Text.

 

[isladogs]

@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]

Hi,

In addition to what arnelgp and isladogs say above, you need to check the criteria of your DCount() expression:

... ,
"[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:

Answer = DCount("*", "Member Activity", _
          "[BookNumber] = " & BookNumber & _
          " AND [Date of Activity] = " & Format([Date of Activity], "\#yyyy\-mm\-dd\#") & _
          " AND Notes = '" & Notes & "'" & _ 
          " AND Activity = " & Activity)

 

[Deleted member 147267]

@isladogs , isn't that a composite primary key, not a composite index?

 

[isladogs]

Hi Micron
Yes, you are indeed correct.

 

[Deleted member 147267]

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]

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.

 

[swell]

@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]

Hi,

In addition to what arnelgp and isladogs say above, you need to check the criteria of your DCount() expression:

... ,
"[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:

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.

Should I put quotes around the Activity field?

 

[cheekybuddha]

Try changing the last part of the criteria to:
" AND Activity = " & Nz(Activity, 0))

hth,

d

 

[swell]

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.

 

[swell]

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]

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?

 

[arnelgp]

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]

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) ?

 

[Deleted member 147267]

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