Prevent Duplicate Records

On May 3, 6:53 pm, "Aaron Kempf" wrote:
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

"Jerry Whittle" wrote in message

...



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.

" wrote:

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- Hide quoted text -

- 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
************************************************** ************************************************** ************************************************** **************************

"Aaron Kempf" wrote:

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 .

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