DCOUNT - Prevent duplicate

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

W3Schools.com

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?

 

[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