preventing duplicates but not totally blocking

C

chynewalker

I created this code to pop a box up that will let me know when i have a
duplicate number, however, I want to still be able to write a duplicate entry.

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
MsgBox "This oem number already exists!!!"
Cancel = True
Else 'Do nothing
End If
End Sub
 
M

Mike Painter

chynewalker said:
I created this code to pop a box up that will let me know when i have
a duplicate number, however, I want to still be able to write a
duplicate entry.

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
MsgBox "This oem number already exists!!!"
Cancel = True
Else 'Do nothing
End If
End Sub

If msgbox("This oem number already exists allow?",vbYesNo, "allow") = False
then
Cancel = True
end if
 
P

PieterLinden via AccessMonster.com

chynewalker said:
I created this code to pop a box up that will let me know when i have a
duplicate number, however, I want to still be able to write a duplicate entry.

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
MsgBox "This oem number already exists!!!"
Cancel = True
Else 'Do nothing
End If
End Sub

You need to put the Cancel = True inside another If statement.

e.g.
If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?", vbYesNo)
= vbNo Then
Cancel = True
Else...
 
D

Douglas J. Steele

Mike Painter said:
If msgbox("This oem number already exists allow?",vbYesNo, "allow") =
False then
Cancel = True
end if

No, you'd need

If msgbox("This oem number already exists allow?",vbYesNo, "allow") = vbNo
then
Cancel = True
end if

Both vbYes and vbNo, the two possible values the msgbox function would
return in this case, are non-zero, so they'd both be treated as True.
 
M

Mike Painter

Douglas said:
No, you'd need

If msgbox("This oem number already exists allow?",vbYesNo, "allow")
= vbNo then
Cancel = True
end if

Both vbYes and vbNo, the two possible values the msgbox function would
return in this case, are non-zero, so they'd both be treated as True.

I'll get back to you as soon as I think of an excuse.

Thanks.
 
C

chynewalker

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?", vbYesNo)
= vbNo Then
Cancel = True
Else 'Do nothing
End If
End Sub


I just get an compile error when i use this



PieterLinden via AccessMonster.com said:
chynewalker said:
I created this code to pop a box up that will let me know when i have a
duplicate number, however, I want to still be able to write a duplicate entry.

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
MsgBox "This oem number already exists!!!"
Cancel = True
Else 'Do nothing
End If
End Sub

You need to put the Cancel = True inside another If statement.

e.g.
If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?", vbYesNo)
= vbNo Then
Cancel = True
Else...

--



.
 
D

Douglas J. Steele

Don't know whether you've got a problem with word-wrap or not.

For the line

If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?", vbYesNo)
= vbNo Then

everything between "If" and "Then" should be on a single line.


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

chynewalker said:
Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?",
vbYesNo)
= vbNo Then
Cancel = True
Else 'Do nothing
End If
End Sub


I just get an compile error when i use this



PieterLinden via AccessMonster.com said:
chynewalker said:
I created this code to pop a box up that will let me know when i have a
duplicate number, however, I want to still be able to write a duplicate
entry.

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
MsgBox "This oem number already exists!!!"
Cancel = True
Else 'Do nothing
End If
End Sub

You need to put the Cancel = True inside another If statement.

e.g.
If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?",
vbYesNo)
= vbNo Then
Cancel = True
Else...

--



.
 
C

chynewalker

I fixed the word wrap, but it still get the error 'MB91443-20600"compile
error: block if without end if"

Douglas J. Steele said:
Don't know whether you've got a problem with word-wrap or not.

For the line

If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?", vbYesNo)
= vbNo Then

everything between "If" and "Then" should be on a single line.


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

chynewalker said:
Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?",
vbYesNo)
= vbNo Then
Cancel = True
Else 'Do nothing
End If
End Sub


I just get an compile error when i use this



PieterLinden via AccessMonster.com said:
chynewalker wrote:
I created this code to pop a box up that will let me know when i have a
duplicate number, however, I want to still be able to write a duplicate
entry.

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
MsgBox "This oem number already exists!!!"
Cancel = True
Else 'Do nothing
End If
End Sub

You need to put the Cancel = True inside another If statement.

e.g.
If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?",
vbYesNo)
= vbNo Then
Cancel = True
Else...

--



.


.
 
D

Douglas J. Steele

You've got two If statements, but only one End If. (Sorry I missed that
before)

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then

If MsgBox("This OEM already exists!!!" & vbcrlf & _
"Add it anyway?", vbYesNo) = vbNo Then
Cancel = True
Else 'Do nothing
End If

End If

End Sub

or

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then

Cancel = ("This OEM already exists!!!" & vbcrlf & _
"Add it anyway?", vbYesNo) = vbNo

End If

End Sub



--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

chynewalker said:
I fixed the word wrap, but it still get the error 'MB91443-20600"compile
error: block if without end if"

Douglas J. Steele said:
Don't know whether you've got a problem with word-wrap or not.

For the line

If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?",
vbYesNo)
= vbNo Then

everything between "If" and "Then" should be on a single line.


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

chynewalker said:
Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?",
vbYesNo)
= vbNo Then
Cancel = True
Else 'Do nothing
End If
End Sub


I just get an compile error when i use this



:

chynewalker wrote:
I created this code to pop a box up that will let me know when i have
a
duplicate number, however, I want to still be able to write a
duplicate
entry.

Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer)

If DCount("[OEM_NUMBER]", _
"[ATLANTIS]", _
"[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then
MsgBox "This oem number already exists!!!"
Cancel = True
Else 'Do nothing
End If
End Sub

You need to put the Cancel = True inside another If statement.

e.g.
If MsgBox("This OEM already exists!!!" & vbcrlf & "Add it anyway?",
vbYesNo)
= vbNo Then
Cancel = True
Else...

--



.


.
 
C

chynewalker

Ok, so the problem with this code is that every number i type in the oem
number field is a duplicate. What did i do wrong?
 

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

Top