DCOUNT - Prevent duplicate (1 Viewer)

[ayam]

Hi every Boady;
i have problem with dcount prevent duplicate ?
first field book_no (table ) maintable
id,book_no with number ,date_series short date
i want to prevent duplicate book_no with the same date/time

Private Sub Text74_BeforeUpdate(Cancel As Integer)
If DCount("*", "maintable", "[book_no]= " & Me.[book_no] & " and [date_series]= #" & Me.[date_series] & "#") > 0 Then
MsgBox " it is duplicate ", vbExclamation
Me.Undo
Cancel = True
End If
End Sub

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[The_Doc_Man]

First, I'm going to move this to the General forum. Please only use the intro forum for introductions. You can select any appropriate topic forum as needed for new problems. Since you are new and probably don't fully understand how we do things, we can easily forgive a minor placement error. Heck, we don't always know where to put things either. So we instantly forgive you.

Second, DCount by itself cannot prevent duplicates, but if you build a named query that includes a SELECT DISTINCT clause, you can DCount the QUERY rather than the TABLE, and that would block counting of duplicates. You have to build a query and name it because domain aggregate functions do not take SQL directly in the 2nd argument, the data source or "domain" of the aggregate.

SQL SELECT DISTINCT Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Gasman]

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct.
Then when correct, I can use that in the function.
Plus, if you cannot see the error, you can copy and paste here or eslewhere, so someone can point out the error.?

Plus you might have to format your date correctly as either mm/dd/yyyy or yyyy-mm-dd ?
A debug.print will show what you actually have, not what you *think* you have.

 

[bastanu]

You are not saying what your problem is: are you getting a runtime error (if yes then on which line and what is the error number and description),you are getting incorrect results, etc.

Cheers,

 

[ayam]

You are not saying what your problem is: are you getting a runtime error (if yes then on which line and what is the error number and description),you are getting incorrect results, etc.

Cheers,

first of all thank you for your response,
the problem is at the first time enter book_no =125 and date=1/2/2023 it take it after that i'm trying to prove its not allowed duplicate the same no and date by enter no=125 and date=1/2/2023 it accepted.
but when entering no=125 and date =2/1/2023 the message appear it is duplicate
but at the same time when the date is 12/12/2022 with the same no the second time refused
it is seems clear my explanation?

 

[Pat Hartman]

Given your criteria, I'm going to take a stab at the problem. Some people, even experts, make the mistake of using Now() when they really want Date(). For your purpose, time does not make any sense and so you should be using Date() to populate the date field.

Now, you have to look at your form and table designs and make sure that you are not obfuscating the actual data by providing a format such as "short date" for the date field. This will hide the time element but it does not eliminate it and that would cause the problem I am assuming you are experiencing. Rows that "look" like they have duplicate dates do not actually have a duplicate date. You have hidden the actual problem with the format.

Although, using formats on forms does make sense, it does NOT make sense at the table level. All that does is hide from you, the programmer, the ACTUAL data value. So, do NOT format any column in a table definition. You should also not format columns in queries UNLESS the query is being used to export data to Excel or Word. In that case, especially with dates, you end up with a time element of 00:00 even when none of the rows contains any time other than midnight (00:00) which is the default and which you almost never want to have displayed.

Carrying the discussion to another level. DateTime data is NOT stored as a string. It is stored as a double precision number. The integer portion of the field is the number of days since 12/30/1899 (SQL Server, and other Office products use a different origin date so this is just the Access value, not that it matters because the ODBC driver handles the translation. The decimal part is the time of day.

 

[MajP]

If DCount("*", "maintable", "[book_no]= " & Me.[book_no] & " and [date_series]= #" & Format(Me.[date_series],"mm/dd/yyyy") & "#") > 0 Then

 

[ayam]

If DCount("*", "maintable", "[book_no]= " & Me.[book_no] & " and [date_series]= #" & Format(Me.[date_series],"mm/dd/yyyy") & "#") > 0 Then

thank you very much it is solved