Prevent Duplicate Records

[jeanhurtado]

Hi, I'm Jean and looking to see how I can prevent duplicate records in
a table. I have a form that is called "Tickets" this form generate the
current date in a date field in a table, using a macro. This macro
execute using a command button. I want to create a method in the
macro code that review first that date that are contain in a textbox
and then check if this date is in the field of the table and then a
message that shows that you cannot duplicate the date in the table
then close and cancel the macro. I have part of the code but is not
complete. Did you have an idea?Thanks you for your support.

*********************************************************************************************************************************
Dim duplicatedate As DAO.Recordset
Dim strSearchName As String

Set duplicatedate = refenrecen to table and date field???????????

strSearchName = Str(Me!TICKETDATE)
duplicatedate.FindFirst txtTicketDate.Value & strSearchName

If duplicatedate.NoMatch = False Then MsgBox _
"The Ticket Date you selected to print has been already printed or
generated. Please select another date.", vbCritical, "Duplicate Date"

***********************************************************************************************************************************


JC

 

[Douglas J. Steele]

Try:

If DCount("*", "NameOfTable", "TicketDate = " & _
Format(Me!TICKETDATE, "\#mm\/dd\/yyyy\#")) > 0 Then
"The Ticket Date you selected to print has been already " & _
"printed or generated. Please select another date.", _
vbCritical, "Duplicate Date"
End If

 

[jeanhurtado]

Try:

If DCount("*", "NameOfTable", "TicketDate = " & _
Format(Me!TICKETDATE, "\#mm\/dd\/yyyy\#")) > 0 Then
"The Ticket Date you selected to print has been already " & _
"printed or generated. Please select another date.", _
vbCritical, "Duplicate Date"
End If

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)










- Show quoted text -

Thanks Mr. Douglas I have made some corrections so the code must be :

If DCount("*", "CycleCount", "TicketDate = " & _
Format(Me!TICKETDATE, "\#mm\/dd\/yyyy\#")) > 0 Then MsgBox _
"The Ticket Date you selected to print has been already " & _
"printed or generated. Please select another date.", _
vbCritical, "Duplicate Date"

But there is an error that it says :

Run-Time error '2465':

Microsoft Access can't find the field 'TICKETDATE' REFERRED TO IN
YOUR EXPRESSION.

zwhy this error comes the field exist and the next staments that work
correctly use this feld. Can you help me?

 

[Guest]

Call me an old-fashoin DBA, but the best way to handle this is with a Unique
constraint on that field either as the primary key or a unique index.

If someone bypasses your form and gets directly into the table, they could
still enter a duplicate. Also your code might not prevent someone from
changing a date thereby causing a duplicate.

I'm not saying that your code a bad idea. It will help prevent dupes by
finding the problem before the entire record is typed in and attempted to
save. It's just that if dupes are never allowed, it's best handled at table
level.

 

[jeanhurtado]

Call me an old-fashoin DBA, but the best way to handle this is with a Unique
constraint on that field either as the primary key or a unique index.

If someone bypasses your form and gets directly into the table, they could
still enter a duplicate. Also your code might not prevent someone from
changing a date thereby causing a duplicate.

I'm not saying that your code a bad idea. It will help prevent dupes by
finding the problem before the entire record is typed in and attempted to
save. It's just that if dupes are never allowed, it's best handled at table
level.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.









- Show quoted text -

The problem is the date is entered automatically in 100 records
interval. The user will don't have access to the table allowing to
modified the date. The issueI want to avoid for example the user hit
in error the command button 2 times or 3 and the date is duplicated
and create 300 records with the same date. That's want I want to
avoid. So want a code so when the user click the button the date is
present in the ticketdate field it will notifiy the user and avoid to
make the action. Hope I can find a solution. Thanks for your extreme
help.

 

[Douglas J. Steele]

Is there a field named TicketDate in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try:

If DCount("*", "NameOfTable", "TicketDate = " & _
Format(Me!TICKETDATE, "\#mm\/dd\/yyyy\#")) > 0 Then
"The Ticket Date you selected to print has been already " & _
"printed or generated. Please select another date.", _
vbCritical, "Duplicate Date"
End If

Thanks Mr. Douglas I have made some corrections so the code must be :

If DCount("*", "CycleCount", "TicketDate = " & _
Format(Me!TICKETDATE, "\#mm\/dd\/yyyy\#")) > 0 Then MsgBox _
"The Ticket Date you selected to print has been already " & _
"printed or generated. Please select another date.", _
vbCritical, "Duplicate Date"

But there is an error that it says :

Run-Time error '2465':

Microsoft Access can't find the field 'TICKETDATE' REFERRED TO IN
YOUR EXPRESSION.

zwhy this error comes the field exist and the next staments that work
correctly use this feld. Can you help me?

 

[jeanhurtado]

Is there a field named TicketDate in your table?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)





Thanks Mr. Douglas I have made some corrections so the code must be :

If DCount("*", "CycleCount", "TicketDate = " & _
Format(Me!TICKETDATE, "\#mm\/dd\/yyyy\#")) > 0 Then MsgBox _
"The Ticket Date you selected to print has been already " & _
"printed or generated. Please select another date.", _
vbCritical, "Duplicate Date"

But there is an error that it says :

Run-Time error '2465':

Microsoft Access can't find the field 'TICKETDATE' REFERRED TO IN
YOUR EXPRESSION.

zwhy this error comes the field exist and the next staments that work
correctly use this feld. Can you help me?

Yes sir the table is CALLED "CycleCount" and the field contained in
the table is called "TICKETDATE". I don't know why is this. Also i
write in lower case and the VBA correct the word to the correct
manner. What is happening?

 

[Douglas J. Steele]

Yes sir the table is CALLED "CycleCount" and the field contained in
the table is called "TICKETDATE". I don't know why is this. Also i
write in lower case and the VBA correct the word to the correct
manner. What is happening?

What's the exact code you're using? (copy and paste, don't rekey it).

Also, is the textbox TICKETDATE on your form bound to the TICKETDATE field
in the recordset? If so, you might try renaming the textbox.

 

[Guest]

If it's the second 100 records with the same date that's the problem,
obviously my idea of a unique constraint won't work.

 

[Aaron Kempf]

GOOD STUFF JERRY

this is a good argument for 'why you should be usnig Access Data Projects'

ADP gives you an additional LAYER of constraints.. called TRIGGERS (triggers
are technically a sproc.. but I think of them as constraints)

Access MDB doesn't have this functionality because MS depecrated MDB a
decade ago

If Jerry knew anythnig about a REAL DATABASE then he might not be a lamer
MDB FanBoy

 

[jeanhurtado]

GOOD STUFF JERRY

this is a good argument for 'why you should be usnig Access Data Projects'

ADP gives you an additional LAYER of constraints.. called TRIGGERS (triggers
are technically a sproc.. but I think of them as constraints)

Access MDB doesn't have this functionality because MS depecrated MDB a
decade ago

If Jerry knew anythnig about a REAL DATABASE then he might not be a lamer
MDB FanBoy









***************************************************************************­*
*****************************************************> > Dim duplicatedate As DAO.Recordset

- Show quoted text -- Hide quoted text -

- Show quoted text -

This is the code complete. The textbox is called "txtticketdate".

********************************************************************************************************************************************************************************

Private Sub cmdOk_Click()
Dim rstTickets As ADODB.Recordset
Dim strSql As String
Dim nTickets As Integer




nTickets = Val(txtticketqty.Value)


Set conDatabase = CurrentProject.Connection
strSql = "SELECT TICKETDATE FROM CycleCount WHERE TICKETDATE Is
Null"

Set rstTickets = New Recordset
rstTickets.Open strSql, conDatabase, adOpenDynamic,
adLockOptimistic


With rstTickets
For x = 1 To nTickets
!TICKETDATE = txtTicketDate
.Update
.MoveNext
Next
End With

rstTickets.Close
conDatabase.Close
Set rstTickets = Nothing
Set conDatabase = Nothing

'Generate Ticket Number code

Dim rst1Tickets As ADODB.Recordset
Dim rst2Tickets As ADODB.Recordset
Dim str1SQL As String
Dim str2SQL As String
Dim lastrec As String
Dim nTicketnumber As Integer
Dim lastticket As String


nTicketnumber = 100


'GET THE LAST TICKET NUMBER FROM THE CYCKETICKETNUMBER TABLE TO BE
USE AS STARTING POINT
'TO GET THE RECORDS.

Set conDatabase = CurrentProject.Connection
str1SQL = "SELECT [Ticket Number] FROM CycleCount WHERE [Ticket
Number] Is Null"

Set conDatabase = CurrentProject.Connection
str2SQL = "SELECT TICKETNUMBER FROM CYCLETICKETNUMBER WHERE
TICKETNUMBER Is NOT Null"

Set rst1Tickets = New Recordset
rst1Tickets.Open str1SQL, conDatabase, adOpenDynamic,
adLockOptimistic

Set rst2Tickets = New Recordset
rst2Tickets.Open str2SQL, conDatabase, adOpenDynamic,
adLockOptimistic

lastrec = DMax("TICKETNUMBER", "CYCLETICKETNUMBER")

With rst1Tickets
For x = 1 To nTicketnumber
![Ticket Number] = (lastrec + x)
.Update
.MoveNext
Next
End With

lastticket = DMax("[Ticket Number]", "CycleCount")

With rst2Tickets
For x = 0 To 1
!TicketNumber = lastticket
.Update
Next
End With

rst1Tickets.Close
rst2Tickets.Close
conDatabase.Close
Set rst1Tickets = Nothing
Set rst2Tickets = Nothing
Set conDatabase = Nothing
Set conDatabase = Nothing


Sleep 500

'DoCmd.OpenForm "TICKETPRINTINGSYSTEM", acNormal


End Sub
********************************************************************************************************************************************************************************

 

[Larry Linson]

GOOD STUFF JERRY

Jean, I'm sorry you have to put up with Mr. Kempf's trolling -- he clearly
has his own axe to grind, for whatever reason. Don't expect him to assist
you with your problem. His "mission" in these newsgroups seems to be just to
spread mis-information, and suggest every user, no matter what their
question, move to client-server .

 

[jeanhurtado]

I still stuck in my issue can someone help me? Here is the issue
again:

Hi, I'm Jean and looking to see how I can prevent duplicate records
in
a table. I have a form that is called "Tickets" this form generate
the
current date in a date field in a table, using a macro. This macro
execute using a command button. I want to create a method in the
macro code that review first that date that are contain in a textbox
and then check if this date is in the field of the table and then a
message that shows that you cannot duplicate the date in the table
then close and cancel the macro. I have part of the code but is not
complete. Did you have an idea?Thanks you for your support.

***************************************************************************­
******************************************************
Dim duplicatedate As DAO.Recordset
Dim strSearchName As String


Set duplicatedate = refenrecen to table and date field???????????


strSearchName = Str(Me!TICKETDATE)
duplicatedate.FindFirst txtTicketDate.Value & strSearchName


If duplicatedate.NoMatch = False Then MsgBox _
"The Ticket Date you selected to print has been already printed
or
generated. Please select another date.", vbCritical, "Duplicate Date"


***************************************************************************­
********************************************************


JC