rebuild the cod to prevent dublicates in more than one record

  • Thread starter FlyingDragon via AccessMonster.com
  • Start date
F

FlyingDragon via AccessMonster.com

hi all
I am new here and i hope to find what i looking for
i used the following code to prevent dublicates in my record SeqNum and it
works perfect
i want to rebuild this code to prevent dublicates in three records together
CrimeNum and CrimeYear and CrimeTypeID


Private Sub SeqNum_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.SeqNum.Value
stLinkCriteria = "[SeqNum]=" & " " & SID
If DCount("SeqNum", "Crimes", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "MyMessage" _
& (SID) & " ............... " _
& vbCr & vbCr & "................................ ", vbInformation _
, "........................."
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub

many thanks for all
sorry about my english
 
S

Steve Sanford

You didn't state what data types CrimeNum, CrimeYear and CrimeTypeID are so I
used number type.

This is how I would prevent duplicates.

NOTE: This is air code:

'----------------------------------------------
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'change these to the proper data type
Dim CTI As Long ' CrimeTypeID
Dim CN As Long ' CrimeNum
Dim CY As Integer ' CrimeYear

CTI = Me.CrimeTypeID
CN = Me.CrimeNum
CY = Me.CrimeYear

Set rsc = Me.RecordsetClone

' use the proper delimiters if variables are not numbers
stLinkCriteria = "[CrimeTypeID]= " & CTI
stLinkCriteria = stLinkCriteria & " AND [CrimeNum] = " & CN
stLinkCriteria = stLinkCriteria & " AND [CrimeYear] = " & CY

rsc.FindFirst stLinkCriteria

If Not rsc.NoMatch Then
Me.Undo
MsgBox "MyMessage" _
& (stLinkCriteria ) & " ............... " _
& vbCr & vbCr & "................................ ",
vbInformation _
, "........................."

Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
'------------------------------------------

If you really wanted to use DLOOKUP(), you could use the same stLinkCriteria.

HTH
 
F

FlyingDragon via AccessMonster.com

sorry >> CrimeNum is number ,CrimeYear is text and CrimeTypeID is number.....
many thanks

Steve said:
You didn't state what data types CrimeNum, CrimeYear and CrimeTypeID are so I
used number type.

This is how I would prevent duplicates.

NOTE: This is air code:

'----------------------------------------------
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'change these to the proper data type
Dim CTI As Long ' CrimeTypeID
Dim CN As Long ' CrimeNum
Dim CY As Integer ' CrimeYear

CTI = Me.CrimeTypeID
CN = Me.CrimeNum
CY = Me.CrimeYear

Set rsc = Me.RecordsetClone

' use the proper delimiters if variables are not numbers
stLinkCriteria = "[CrimeTypeID]= " & CTI
stLinkCriteria = stLinkCriteria & " AND [CrimeNum] = " & CN
stLinkCriteria = stLinkCriteria & " AND [CrimeYear] = " & CY

rsc.FindFirst stLinkCriteria

If Not rsc.NoMatch Then
Me.Undo
MsgBox "MyMessage" _
& (stLinkCriteria ) & " ............... " _
& vbCr & vbCr & "................................ ",
vbInformation _
, "........................."

Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
'------------------------------------------

If you really wanted to use DLOOKUP(), you could use the same stLinkCriteria.

HTH
hi all
I am new here and i hope to find what i looking for
Click to expand...
[quoted text clipped - 26 lines]
many thanks for all
sorry about my english
Click to expand...
Click to expand...
 
F

FlyingDragon via AccessMonster.com

where i put the code and what if i want to use dlookup()

Steve said:
You didn't state what data types CrimeNum, CrimeYear and CrimeTypeID are so I
used number type.

This is how I would prevent duplicates.

NOTE: This is air code:

'----------------------------------------------
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'change these to the proper data type
Dim CTI As Long ' CrimeTypeID
Dim CN As Long ' CrimeNum
Dim CY As Integer ' CrimeYear

CTI = Me.CrimeTypeID
CN = Me.CrimeNum
CY = Me.CrimeYear

Set rsc = Me.RecordsetClone

' use the proper delimiters if variables are not numbers
stLinkCriteria = "[CrimeTypeID]= " & CTI
stLinkCriteria = stLinkCriteria & " AND [CrimeNum] = " & CN
stLinkCriteria = stLinkCriteria & " AND [CrimeYear] = " & CY

rsc.FindFirst stLinkCriteria

If Not rsc.NoMatch Then
Me.Undo
MsgBox "MyMessage" _
& (stLinkCriteria ) & " ............... " _
& vbCr & vbCr & "................................ ",
vbInformation _
, "........................."

Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
'------------------------------------------

If you really wanted to use DLOOKUP(), you could use the same stLinkCriteria.

HTH
hi all
I am new here and i hope to find what i looking for
Click to expand...
[quoted text clipped - 26 lines]
many thanks for all
sorry about my english
Click to expand...
Click to expand...
 
S

Steve Sanford

Here is the code again corrected for data type:

'Still air code!!

'----------------------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'change these to the proper data type
Dim CTI As Long ' CrimeTypeID
Dim CN As Long ' CrimeNum
Dim CY As String' CrimeYear

CTI = Me.CrimeTypeID
CN = Me.CrimeNum
CY = Me.CrimeYear

Set rsc = Me.RecordsetClone

' use the proper delimiters if variables are not numbers
stLinkCriteria = "[CrimeTypeID]= " & CTI
stLinkCriteria = stLinkCriteria & " AND [CrimeNum] = " & CN
stLinkCriteria = stLinkCriteria & " AND [CrimeYear] = '" & CY & "'"

' Note: expanded the previous line looks like:
' " AND [CrimeYear] = ' " & CY & " ' "

rsc.FindFirst stLinkCriteria

If Not rsc.NoMatch Then
Me.Undo
MsgBox "MyMessage" _
& (stLinkCriteria ) & " ............... " _
& vbCr & vbCr & "................................ ",
vbInformation _
, "........................."

Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub
'----------------------------------------------------------

HTH
 
S

Steve Sanford

I would try the Form_BeforeUpdate event.

for DCOUNT(), the code might look something like:

'note the "Form_BeforeUpdate"
'----------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'change these to the proper data type
Dim CTI As Long ' CrimeTypeID
Dim CN As Long ' CrimeNum
Dim CY As String' CrimeYear

CTI = Me.CrimeTypeID
CN = Me.CrimeNum
CY = Me.CrimeYear

Set rsc = Me.RecordsetClone

' use the proper delimiters if variables are not numbers
stLinkCriteria = "[CrimeTypeID]= " & CTI
stLinkCriteria = stLinkCriteria & " AND [CrimeNum] = " & CN
stLinkCriteria = stLinkCriteria & " AND [CrimeYear] = '" & CY & "'"

' Note: expanded the previous line looks like:
' " AND [CrimeYear] = ' " & CY & " ' "

If DCount("SeqNum", "Crimes", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "MyMessage" _
& (stLinkCriteria ) & " ............... " _
& vbCr & vbCr & "................................ ",
vbInformation _
, "........................."

Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub
'----------------------------------------------------------

I wouldn't use DCount(). I think FindFirst is faster.

Also, Allen Browne has a replacement function for DCount(). It is ECount() at
http://allenbrowne.com/ser-66.html

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


FlyingDragon via AccessMonster.com said:
where i put the code and what if i want to use dlookup()

Steve said:
You didn't state what data types CrimeNum, CrimeYear and CrimeTypeID are so I
used number type.

This is how I would prevent duplicates.

NOTE: This is air code:

'----------------------------------------------
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'change these to the proper data type
Dim CTI As Long ' CrimeTypeID
Dim CN As Long ' CrimeNum
Dim CY As Integer ' CrimeYear

CTI = Me.CrimeTypeID
CN = Me.CrimeNum
CY = Me.CrimeYear

Set rsc = Me.RecordsetClone

' use the proper delimiters if variables are not numbers
stLinkCriteria = "[CrimeTypeID]= " & CTI
stLinkCriteria = stLinkCriteria & " AND [CrimeNum] = " & CN
stLinkCriteria = stLinkCriteria & " AND [CrimeYear] = " & CY

rsc.FindFirst stLinkCriteria

If Not rsc.NoMatch Then
Me.Undo
MsgBox "MyMessage" _
& (stLinkCriteria ) & " ............... " _
& vbCr & vbCr & "................................ ",
vbInformation _
, "........................."

Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
'------------------------------------------

If you really wanted to use DLOOKUP(), you could use the same stLinkCriteria.

HTH
hi all
I am new here and i hope to find what i looking for
Click to expand...
[quoted text clipped - 26 lines]
many thanks for all
sorry about my english
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Sanford

Oops....

The first line should be:

Private Sub Form_BeforeUpdate(Cancel As Integer)


not " Form_BeforeInsert"

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Steve Sanford said:
Here is the code again corrected for data type:

'Still air code!!

'----------------------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'change these to the proper data type
Dim CTI As Long ' CrimeTypeID
Dim CN As Long ' CrimeNum
Dim CY As String' CrimeYear

CTI = Me.CrimeTypeID
CN = Me.CrimeNum
CY = Me.CrimeYear

Set rsc = Me.RecordsetClone

' use the proper delimiters if variables are not numbers
stLinkCriteria = "[CrimeTypeID]= " & CTI
stLinkCriteria = stLinkCriteria & " AND [CrimeNum] = " & CN
stLinkCriteria = stLinkCriteria & " AND [CrimeYear] = '" & CY & "'"

' Note: expanded the previous line looks like:
' " AND [CrimeYear] = ' " & CY & " ' "

rsc.FindFirst stLinkCriteria

If Not rsc.NoMatch Then
Me.Undo
MsgBox "MyMessage" _
& (stLinkCriteria ) & " ............... " _
& vbCr & vbCr & "................................ ",
vbInformation _
, "........................."

Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub
'----------------------------------------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


FlyingDragon via AccessMonster.com said:
sorry >> CrimeNum is number ,CrimeYear is text and CrimeTypeID is number.....
many thanks
Click to expand...
Click to expand...
 
F

FlyingDragon via AccessMonster.com

Steve said:
I would try the Form_BeforeUpdate event.

for DCOUNT(), the code might look something like:

'note the "Form_BeforeUpdate"
'----------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'change these to the proper data type
Dim CTI As Long ' CrimeTypeID
Dim CN As Long ' CrimeNum
Dim CY As String' CrimeYear

CTI = Me.CrimeTypeID
CN = Me.CrimeNum
CY = Me.CrimeYear

Set rsc = Me.RecordsetClone

' use the proper delimiters if variables are not numbers
stLinkCriteria = "[CrimeTypeID]= " & CTI
stLinkCriteria = stLinkCriteria & " AND [CrimeNum] = " & CN
stLinkCriteria = stLinkCriteria & " AND [CrimeYear] = '" & CY & "'"

' Note: expanded the previous line looks like:
' " AND [CrimeYear] = ' " & CY & " ' "

If DCount("SeqNum", "Crimes", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "MyMessage" _
& (stLinkCriteria ) & " ............... " _
& vbCr & vbCr & "................................ ",
vbInformation _
, "........................."

Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub
'----------------------------------------------------------

I wouldn't use DCount(). I think FindFirst is faster.

Also, Allen Browne has a replacement function for DCount(). It is ECount() at
http://allenbrowne.com/ser-66.html

HTH
where i put the code and what if i want to use dlookup()
Click to expand...
[quoted text clipped - 48 lines]
Click to expand...
many thanks it worked
can i replace data in one of them withe date ?
 
S

Steve Sanford

Yes, you can replace or add criteria, but you must use the proper delimiters
for the data type.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


FlyingDragon via AccessMonster.com said:
Steve said:
I would try the Form_BeforeUpdate event.

for DCOUNT(), the code might look something like:

'note the "Form_BeforeUpdate"
'----------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'change these to the proper data type
Dim CTI As Long ' CrimeTypeID
Dim CN As Long ' CrimeNum
Dim CY As String' CrimeYear

CTI = Me.CrimeTypeID
CN = Me.CrimeNum
CY = Me.CrimeYear

Set rsc = Me.RecordsetClone

' use the proper delimiters if variables are not numbers
stLinkCriteria = "[CrimeTypeID]= " & CTI
stLinkCriteria = stLinkCriteria & " AND [CrimeNum] = " & CN
stLinkCriteria = stLinkCriteria & " AND [CrimeYear] = '" & CY & "'"

' Note: expanded the previous line looks like:
' " AND [CrimeYear] = ' " & CY & " ' "

If DCount("SeqNum", "Crimes", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "MyMessage" _
& (stLinkCriteria ) & " ............... " _
& vbCr & vbCr & "................................ ",
vbInformation _
, "........................."

Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub
'----------------------------------------------------------

I wouldn't use DCount(). I think FindFirst is faster.

Also, Allen Browne has a replacement function for DCount(). It is ECount() at
http://allenbrowne.com/ser-66.html

HTH
where i put the code and what if i want to use dlookup()
Click to expand...
[quoted text clipped - 48 lines]
many thanks for all
sorry about my english
Click to expand...
Click to expand...
many thanks it worked
can i replace data in one of them withe date ?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AfterUpdate help 5
Run time error 2001 3
Preventing Duplicates from being entered 7
Edit code to use integer instead of text 0
if duplicate goto record 3
Run-time error 3075 3
Preventing duplicate entries 10
Problem with code to catch duplicate Primay Key 27

Top