Solved Dlookup to prevent duplicate entries

[Pat Hartman]

The suggestions to use the Control's BeforeUpdate event or the Form's BeforeUpdate event should put you on the right track. Validation MUST be done in an event which allows you to cancel the update. Otherwise, you get a lot of warning messages but bad data still gets saved. At the end is a link to a couple of videos as well as a database which will help you to understand how control and form events actually work.

BUT, the problem could be with the date field. How are you populating it? If you are using Now() which populated the date AND the time, you will NEVER succeed with finding a specific date using a time value. WHY? Dates are stored internally as double precision numbers. They are NOT strings. The integer portion of the number is the number of days since 12/30/1899 (for Jet/ACE) and the decimal is the elapsed time since midnight.

Here's an example that shows the problem.
Print now() --- print the value of Now
12/10/2024 2:38:17 PM
print cdbl(#12/10/2024 2:38:17 PM#) --- Print the actual stored value
45636.6099189815
print cdate(45636.6099189815) --- back the other way so you can see it is the same date
12/10/2024 2:38:17 PM
Print cdate(45636.609918) --- slightly modify the date but it comes up the same string
12/10/2024 2:38:17 PM
print cdate(45636.60991855) --- slightly modify the date but it comes up the same string
12/10/2024 2:38:17 PM
print cdate(45636.6099186666) --- slightly modify the date but it comes up the same string
12/10/2024 2:38:17 PM

SO. The realty is there are a lot of values for 2:38:17 PM but none will match. To get a match, you would have to know what the underlying stored value is. You can't search using a string.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[donkey9972]

Well on my form and what is stored on the table are both medium dates. dd/mm/yyyy. I do not use Now() to populate the field, the user has to input it manually. Once I am at my computer i will look at the information you provided Pat.

 

[cheekybuddha]

Also, please confirm the datatype of the D_Date field in your table.

 

[donkey9972]

Ok, so I was going to post a DB on here and I was taking away things that were not needed for this problem I am currently having, then I figured out the problem I was having. Some of the code for something else I was using was preventing the code you provided from working properly. So I am good now. Thank you.

 

[Pat Hartman]

Well on my form and what is stored on the table are both medium dates. dd/mm/yyyy.

NO it is not. If the data type of the field is date, it is stored exactly as I described. You will have much less trouble working with dates if you understand how they are stored so you don't try to treat them as strings.

Some people make the mistake of using Now() when they really want Date(). So, they see the time component and don't want it so they set the format property to short date and now they can't see the time BUT IT IS STILL THERE That is where everything gets a little crazy. I'm pretty sure one of the dents in my forehead occurred at the time I discovered this issue back in the 90's.

I always recommend that you never set the format property of a field at the table level. This does not change what/how the data is stored. All it does is obfuscate the actual stored value.

 

[donkey9972]

Ok, so I have that the field on the table for the D_Date is required. However when a duplicate date is entered it has the msgbox as I wanted stating "That date already exists". Then right after clicking OK another system message pops up because the date is required and it is trying to undo the duplicate date. However, because the system message is popping up it is not allowing the undo to work. How can I suppress the system message and allow it to undo? Do I need to simply change the field from being required to not required, or is there some other way to make this work, because I kind of really need the dates to be required.

 

[Pat Hartman]

1. I'm having trouble with the concept of a unique index by date alone. Are you sure that is what you want? Are you sure it is not date within something else? Like one note per day per "customer"?
2. To avoid the collision you are experiencing, in this case, I would use the date control's BeforeUpdate event to check to see if the date already exists. then you can cancel the update and prevent the issue. You can also give the user a nicer message. If you don't want to write validation code, then, you MUST trap the error in the data error event. In that case, you can also substitute your own error message and you set the Response property to tell Access to NOT send its error message.

 

[Isaac]

A simpler example to follow might be:

DCount("*", "tbl_Coversheet", "D_Date = #" & format(me.txtDate,"mm/dd/yyyy") & "#"

Your last part of Dcount needs to end up looking just like it would look in query screen criteria, which would be

#mm/dd/yyyy# if you are using the mm/dd/yyyy format. Number signs are required to enclose the date.

 

[donkey9972]

1. I'm having trouble with the concept of a unique index by date alone. Are you sure that is what you want? Are you sure it is not date within something else? Like one note per day per "customer"?
2. To avoid the collision you are experiencing, in this case, I would use the date control's BeforeUpdate event to check to see if the date already exists. then you can cancel the update and prevent the issue. You can also give the user a nicer message. If you don't want to write validation code, then, you MUST trap the error in the data error event. In that case, you can also substitute your own error message and you set the Response property to tell Access to NOT send its error message.

1. No, from the information that has to be provided it is a lot of information and it is date specific, we do not have customers or anything else we can target on, just the date.

2 I am using the controls BeforeUpdate. I am using this code here

Private Sub txtDate_BeforeUpdate(Cancel As Integer)

  Cancel = DCount("*", "tbl_Coversheet", "D_Date = " & Format(Me.txtDate, "\#yyyy\-mm\-dd hh:nn:ss\#")) > 0
  If Cancel Then
    MsgBox "That date already exists"
    Me.txtDate.Undo
  End If

End Sub

and I am getting the errors I mentioned in post 26.

 

[donkey9972]

A simpler example to follow might be:

DCount("*", "tbl_Coversheet", "D_Date = #" & format(me.txtDate,"mm/dd/yyyy") & "#"

Your last part of Dcount needs to end up looking just like it would look in query screen criteria, which would be

#mm/dd/yyyy# if you are using the mm/dd/yyyy format. Number signs are required to enclose the date.

When I get back home to my computer I will adjust and see if it helps with the errors I am receiving.

 

[cheekybuddha]

and I am getting the errors I mentioned in post 26.

You are still suffering the poor copy and paste.

& should be &

Revise with:

Private Sub txtDate_BeforeUpdate(Cancel As Integer)

  Cancel = DCount("*", "tbl_Coversheet", "D_Date = " & Format(Me.txtDate, "\#yyyy\-mm\-dd hh:nn:ss\#")) > 0
  If Cancel Then
    MsgBox "That date already exists"
    Me.txtDate.Undo
  End If

End Sub

 

[cheekybuddha]

DCount("*", "tbl_Coversheet", "D_Date = #" & format(me.txtDate,"mm/dd/yyyy") & "#"

is equivalent to:

DCount("*", "tbl_Coversheet", "D_Date = " & Format(Me.txtDate, "\#yyyy\-mm\-dd hh:nn:ss\#"))

 

[donkey9972]

Actuall I do not have the "amp" anywhere in the code. I saw after the last time when you reposted and ensured it isn't in there.

 

[cheekybuddha]

OK, I can't test right now, but since you have the unique index, my guess is that the Form_Error event fires before the control_BeforeUpdate when the index is violated.

See the helpfile here: Form.Error

Add this code to your form error event handler (make sure the form's OnError property sheet has [Event Procedure] too):

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const conDuplicateKey = 3022
    Dim strMsg As String
 
    If DataErr = conDuplicateKey Then
        Response = acDataErrContinue
    End If
End Sub

If that doesn't work, I'll have to test tomorrow if no one can provide the solution before.

 

[donkey9972]

Ok it still isn't working. I am uploading a small DB where I am having the problem.

 

[cheekybuddha]

OK,

I'm not sure why (Pat will likely have a better idea) but control.Undo does not work in the control_BeforeUpdate event - you must undo the whole record which might be a pain if you have already entered a load of other data.

It will work if you get rid of all the Form_Error code and change the txtDate_BeforeUpdate to:

Private Sub txtDate_BeforeUpdate(Cancel As Integer)

  Cancel = DCount("*", "tbl_Coversheet", "D_Date = " & Format(Me.txtDate, "\#yyyy\-mm\-dd hh:nn:ss\#")) > 0
  If Cancel Then
    MsgBox "That date already exists"
    Me.Undo
  End If

End Sub

(My guess that the Form_Error() fired before the control_BeforeUpdate() was wrong)

 

[Minty]

You don't need to undo it, just cancel the before update and it won't save.

 

[cheekybuddha]

You don't need to undo it, just cancel the before update and it won't save.

Ah, OK!

But, IIRC, the invalid date remains in the textbox, no?

 

[Minty]

Yes - so you could undo that specific control and set focus to it and cancel.

 

[Pat Hartman]

No, from the information that has to be provided it is a lot of information and it is date specific, we do not have customers or anything else we can target on, just the date.

But what is the information about? I have to admit, I don't think I've led a sheltered life but I have never in over 50 years of software development had a table with only the date as the primary key. Having date as part of the unique identifier occurs all the time. But, date alone? Feels like something important is missing and maybe you'll discover the issue tomorrow and end up having to change the PK of the table.

Why do you persist in formatting the date to include time when you said it does not include time?

The Control level events work slightly differently from the Form level events because the control still has the focus and there are certain things you can't do to a control while it has the focus. Apparently Me.Control.Undo is one of them. As I explain below, I would never do this so I've never experienced the error.

99.9% of my validation is done in the Form's BeforeUpdate event. It is easier for me if the validation code is all in a single procedure. The ONLY reason I would put validation logic in the control's BeforeUpdate event is if I wanted to stop the user in his tracks and not allow him to enter any other information because I was not going to allow him to save a record if there is an error and I didn't want him to waste his time. So, I would use a control level event for this purpose. BUT, as Minty pointed out, I wouldn't undo the typing. Removing the invalid value is punitive and just makes it harder for your user. It is far better to allow him to see his typo and correct ONE character than to force him to re-enter the entire date.

The reason you are still getting the error is because you did not Cancel the BeforeUpdate event. Cancel is a named argument of the event and it is a Boolean so correct values are 0 or -1. By using it the way you have, you are coming up with 0 or 1. 1 is NOT = -1 and therefore, the event doesn't get cancelled. That is why you are still getting the message from the Error event.