Solved Dlookup to prevent duplicate entries

[donkey9972]

Hi, I have been reading up on what I can, but Dlookup confuses me, but then again what doesn't. I have a table called tbl_Coversheet and on that table I have a field called D_Date with the indexed set to Yes (No Duplicates), then on the form there is a field called txtdate for thie field on the table. I am trying to use this code

DLookup("D_Date", "tbl_Coversheet", "D_Date = '" & Me.txtDate & "'")

MsgBox "That date already exists"

But I am not sure what goes where and I am sure I must be missing something for it. I have already read up on the MS website regarding this, but it is still confusing me. Can anyone help me and simplify this for me?

 

[cheekybuddha]

If D_Date is a DateTime datatype field then you must use octothorpes (#) to denote you are passing a date.

Also, checking existence is more easily done using DCount() rather than DLookup() since you don't have to handle Null returns.

So try:

If DCount("*", "tbl_Coversheet", "D_Date = " & Format(Me.txtDate, "\#yyyy\-mm\-dd hh:nn:ss\#")) > 0 Then
  MsgBox "That date already exists"
Else
  ' Date not present - Do your insert
End If

 

[donkey9972]

Thank you for the information, but I am still confused on something, this part:
"\#yyyy\-mm\-dd hh:nn:ss\#")) > 0
is this just formatting it or is the criteria fr something, and what does the 0 do?

 

[cheekybuddha]

Yes, it is formatting for the criteria.

Don't mess with it.

SQL expects dates to be passed in a non-ambiguous format which translates to either:

mm/dd/yyyy (US date format) or
yyyy-mm-dd (ISO date format)

This is irrespective of your local regional date format settings.

DCount() returns the count of how many records in the table meet your criteria. So, if the return is 0 then no matching record exists. If it equals 1 or more, then that date is already in the table

 

[donkey9972]

Ok, sorry for the delayed response. I tried the code you had provided, which btw thank you for the help and the explanation. However, what is happening is I am putting the code on the afterupdate for the txtDate box on the form. SO what is happening is when I purposefully enter a date that already exists, and try to leave the form using a button on the form it does not give me a message box stating it already exists, it simply exits the form. Then I go to the table to see if the new date was input and it isn't. So the only thing wrong is no message box is popping up with a warning. On the flip side if I try to exit the form using the X then it gives me the default system message saying the control has a duplicate value in the index...., so a question, am I putting this in the right location?

 

[cheekybuddha]

so a question, am I putting this in the right location?

No!

You need either the control's BeforeUpdate if you want to validate immediately after entering the date, or the form's BeforeUpdate if you want to validate before exiting the form or record.

Will post an example when I get back to a computer

 

[donkey9972]

Ok, I am still lost on this. I will have to wait for your example, I did try to move the code to the different areas you said to test out, one then the other. But started giving me errors.

 

[cheekybuddha]

Yes, unless you know how to use those events, errors might be expected!

Search the forum or the Access help files for BeforeUpdate to try and get an idea of how they work.

I'll be back to a computer in a short while. Someone else might chip in with an example in the meanwhile

 

[donkey9972]

I will look around, thank you again.

 

[cheekybuddha]

OK, if you want to validate the date immediately upon entry (or rather as soon as you leave the txtDate control) use:

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

If you don't want to perform the validation until the user moves to another record or closes the form then place the code in the form's BeforeUpdate event:

Private Sub Form_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

You can validate other controls at the same time in this event using whatever logic is required - see a basic example in the Access help

 

[donkey9972]

Well I have tried to input this code. It highlights the Cancel = Dcount line in red, and I am still getting the same results. I know the idea of this is beyond what I can do. I just thought it would be something decent to prevent duplicate enties.

 

[cheekybuddha]

Sorry, copy and paste on this site suddenly seems to be translating special characters to HTML entities

Try instead:

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

Or:

Private Sub Form_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]

I know the idea of this is beyond what I can do.

No it's not beyond you - just take a breath and try and understand what each part of the code is doing!

If you don't understand, just ask.

 

[donkey9972]

I appreciate the encouragement. I do not understand the cancel part or what the "*" is doing, I do understand the idea behind the rest of it.

I also tried both of those that you posted and here are my results...the Form_BeforeUpdate works, but when the msgbox pops up it is after the form has been exited or tried to move to a new record, which the way it looks that is how it is supposed to work. However, I think I prefer the idea of the txtDate_BeforeUpdate. It doesn't seem to work though. I can enter the date and move to another field or checkbox that is on the form and nothing happens. It is like I said before it simply just doesn't input the information.

 

[cheekybuddha]

It is like I said before it simply just doesn't input the information.

What is the ControlSource of txtDate ?

How do you tell that it hasn't input the information?

 

[donkey9972]

The control source is the D_Date for the query associated with table. Ok, lets take Dec 9, 2024. I already have that date input on the form, stored in the table. Then I go back to the form and reinput Dec 9, 2024. I then click on a button that takes me to a different form, no error, no message pop nothing. I then close that form and open the table and only see 1 date for Dec 9, 2024 listed.

 

[cheekybuddha]

What happens if you have no code at all in the txtDate_BeforeUpdate event?

Does the date get duplicated in the table?

Also is this a continuous form?

 

[donkey9972]

Right now no because I have it indexed not to accept duplicates. Does that need to change?

 

[donkey9972]

So, I turned the indexed to no . Reran the code and duplicates are appearing now and still no msgbox.

 

[cheekybuddha]

Leave the index - that's a good thing!

Do you have some code in your Form_Error event handler?