Solved Dlookup to prevent duplicate entries

[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.

 

[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.

 

[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.

 

[donkey9972]

Well, the data being reported up is all about date specific. There is essentially nothing else tying the data together. I might just be in a unique situation where the date has to be the primary focus. The form BeforeUpdate event does not work correctly for me as far as I can tell. However, I have decided on a new course of action which is solving my issue. I have removed the requirement for the date to be required. Turned it to no. I have decided to disable all controls on the form except the date until a date is entered. Once the date is entered, they become enabled. Its a workaround, but it solves my issue I was having. Thank you all for the help and the chance to learn some things.