Dlookup Duplicate syntax question

J

JOM

I am trying to enter information on a form. I have a txt box that if I enter
information should first check for duplicates and if there is a duplicate,
alert me, but if not a duplicate allow me to continue entering information.
I did find some valuable infor in the newsgroup but when I tried it, its not
working well for me.

Whenever I enter the rqstNo, whether its a duplicate or not, the duplicate
popup keeps showing. How will take care of that


he is my code......
Private Sub RqstNo_BeforeUpdate(Cancel As Integer)

Dim iAns As Integer
If Not IsNull(DLookup("RqstNo", "TblRqst", "RqstNo = " & RqstNo)) Then
iAns = MsgBox("Duplicate Number!" & vbCrLf & " Click OK to try a
different one," _ & " Cancel to erase the entire record and try over",
vbOKCancel,)
Cancel = True
If iAns = vbCancel Then
Me.Undo
Else
Me!RqstNo.Undo
'Me!RqstNo.SetFocus

End If
End If
 
G

George Nicholson

Remove the "Not".

If DLookup can't find anything meeting the criteria, it returns Null
"If Not IsNull(DLookup("RqstNo", "TblRqst", "RqstNo = " & RqstNo)) Then"
Tests for the opposite condition. You will get a message box every time
DLookup *does* find something.

In addition, I would suggest
1) you enclose field names in brackets.
2) that you specify the control value (Me.RqstNo) rather than the field
value since we are in BeforeUpdate for a control.

If IsNull(DLookup("[RqstNo]", "TblRqst", "[RqstNo] = " & Me.RqstNo))
Then

HTH,
 
J

JOM

the syntax is still not working, it mesgbox keeps popin up all the time, is
there something that I could be missing?

George Nicholson said:
Remove the "Not".

If DLookup can't find anything meeting the criteria, it returns Null
"If Not IsNull(DLookup("RqstNo", "TblRqst", "RqstNo = " & RqstNo)) Then"
Tests for the opposite condition. You will get a message box every time
DLookup *does* find something.

In addition, I would suggest
1) you enclose field names in brackets.
2) that you specify the control value (Me.RqstNo) rather than the field
value since we are in BeforeUpdate for a control.

If IsNull(DLookup("[RqstNo]", "TblRqst", "[RqstNo] = " & Me.RqstNo))
Then

HTH,
--
George Nicholson

Remove 'Junk' from return address.


JOM said:
I am trying to enter information on a form. I have a txt box that if I
enter
information should first check for duplicates and if there is a duplicate,
alert me, but if not a duplicate allow me to continue entering
information.
I did find some valuable infor in the newsgroup but when I tried it, its
not
working well for me.

Whenever I enter the rqstNo, whether its a duplicate or not, the duplicate
popup keeps showing. How will take care of that


he is my code......
Private Sub RqstNo_BeforeUpdate(Cancel As Integer)

Dim iAns As Integer
If Not IsNull(DLookup("RqstNo", "TblRqst", "RqstNo = " & RqstNo)) Then
iAns = MsgBox("Duplicate Number!" & vbCrLf & " Click OK to try a
different one," _ & " Cancel to erase the entire record and try over",
vbOKCancel,)
Cancel = True
If iAns = vbCancel Then
Me.Undo
Else
Me!RqstNo.Undo
'Me!RqstNo.SetFocus

End If
End If
Click to expand...
Click to expand...
 
G

George Nicholson

If RqstNo is a numerical field within tblRqst then try:
"RqstNo = " & clng(Me.RqstNo)
You want "Request = myNum" making sure myNum isn't being passed as text.

If RqstNo is a Text field, try:
"RqstNo = '" & Me.RqstNo & "'"
You want to end up with "Request = 'myNum'", making sure myNum is enclosed
in single quotes.

If neither seem to work, try putting a breakpoint on that line and run your
form. When the breakpoint is reached, try hovering your cursor over that
phrase (or copying & pasting it into the Immediate window, preceded by a ?)
and see if it is evaluating as you expect. It might give you a clue as to
what's going on.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


JOM said:
the syntax is still not working, it mesgbox keeps popin up all the time,
is
there something that I could be missing?

George Nicholson said:
Remove the "Not".

If DLookup can't find anything meeting the criteria, it returns Null
"If Not IsNull(DLookup("RqstNo", "TblRqst", "RqstNo = " & RqstNo))
Then"
Tests for the opposite condition. You will get a message box every time
DLookup *does* find something.

In addition, I would suggest
1) you enclose field names in brackets.
2) that you specify the control value (Me.RqstNo) rather than the
field
value since we are in BeforeUpdate for a control.

If IsNull(DLookup("[RqstNo]", "TblRqst", "[RqstNo] = " & Me.RqstNo))
Then

HTH,
--
George Nicholson

Remove 'Junk' from return address.


JOM said:
I am trying to enter information on a form. I have a txt box that if I
enter
information should first check for duplicates and if there is a
duplicate,
alert me, but if not a duplicate allow me to continue entering
information.
I did find some valuable infor in the newsgroup but when I tried it,
its
not
working well for me.

Whenever I enter the rqstNo, whether its a duplicate or not, the
duplicate
popup keeps showing. How will take care of that


he is my code......
Private Sub RqstNo_BeforeUpdate(Cancel As Integer)

Dim iAns As Integer
If Not IsNull(DLookup("RqstNo", "TblRqst", "RqstNo = " & RqstNo)) Then
iAns = MsgBox("Duplicate Number!" & vbCrLf & " Click OK to try a
different one," _ & " Cancel to erase the entire record and try over",
vbOKCancel,)
Cancel = True
If iAns = vbCancel Then
Me.Undo
Else
Me!RqstNo.Undo
'Me!RqstNo.SetFocus

End If
End If
Click to expand...
Click to expand...
Click to expand...
 
F

fredg

I am trying to enter information on a form. I have a txt box that if I enter
information should first check for duplicates and if there is a duplicate,
alert me, but if not a duplicate allow me to continue entering information.
I did find some valuable infor in the newsgroup but when I tried it, its not
working well for me.

Whenever I enter the rqstNo, whether its a duplicate or not, the duplicate
popup keeps showing. How will take care of that

he is my code......
Private Sub RqstNo_BeforeUpdate(Cancel As Integer)

Dim iAns As Integer
If Not IsNull(DLookup("RqstNo", "TblRqst", "RqstNo = " & RqstNo)) Then
iAns = MsgBox("Duplicate Number!" & vbCrLf & " Click OK to try a
different one," _ & " Cancel to erase the entire record and try over",
vbOKCancel,)
Cancel = True
If iAns = vbCancel Then
Me.Undo
Else
Me!RqstNo.Undo
'Me!RqstNo.SetFocus

End If
End If
Click to expand...

If you cannot have a duplicate value, then all you need do is display
the message if it is a duplicate and return to the control using
Cancel = True for re-entry.
Simply by counting, using your criteria, you can see if the value
already exists.

RqstNo is a Number datatype?

Private Sub RqstNo_BeforeUpdate(Cancel As Integer)
If DCount("*", "TblRqst", "RqstNo = " & RqstNo) > 0 Then
MsgBox "A record with this number already exists"
Cancel = True
End If
End Sub
 
J

JOM

Thanks that worked, I had already duplicates in the table so that is why it
was not initially working....
 
J

JOM

Thanks that worked, I had already duplicates in the table so that is why it
was not initially working....



George Nicholson said:
If RqstNo is a numerical field within tblRqst then try:
"RqstNo = " & clng(Me.RqstNo)
You want "Request = myNum" making sure myNum isn't being passed as text.

If RqstNo is a Text field, try:
"RqstNo = '" & Me.RqstNo & "'"
You want to end up with "Request = 'myNum'", making sure myNum is enclosed
in single quotes.

If neither seem to work, try putting a breakpoint on that line and run your
form. When the breakpoint is reached, try hovering your cursor over that
phrase (or copying & pasting it into the Immediate window, preceded by a ?)
and see if it is evaluating as you expect. It might give you a clue as to
what's going on.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


JOM said:
the syntax is still not working, it mesgbox keeps popin up all the time,
is
there something that I could be missing?

George Nicholson said:
Remove the "Not".

If DLookup can't find anything meeting the criteria, it returns Null
"If Not IsNull(DLookup("RqstNo", "TblRqst", "RqstNo = " & RqstNo))
Then"
Tests for the opposite condition. You will get a message box every time
DLookup *does* find something.

In addition, I would suggest
1) you enclose field names in brackets.
2) that you specify the control value (Me.RqstNo) rather than the
field
value since we are in BeforeUpdate for a control.

If IsNull(DLookup("[RqstNo]", "TblRqst", "[RqstNo] = " & Me.RqstNo))
Then

HTH,
--
George Nicholson

Remove 'Junk' from return address.


I am trying to enter information on a form. I have a txt box that if I
enter
information should first check for duplicates and if there is a
duplicate,
alert me, but if not a duplicate allow me to continue entering
information.
I did find some valuable infor in the newsgroup but when I tried it,
its
not
working well for me.

Whenever I enter the rqstNo, whether its a duplicate or not, the
duplicate
popup keeps showing. How will take care of that


he is my code......
Private Sub RqstNo_BeforeUpdate(Cancel As Integer)

Dim iAns As Integer
If Not IsNull(DLookup("RqstNo", "TblRqst", "RqstNo = " & RqstNo)) Then
iAns = MsgBox("Duplicate Number!" & vbCrLf & " Click OK to try a
different one," _ & " Cancel to erase the entire record and try over",
vbOKCancel,)
Cancel = True
If iAns = vbCancel Then
Me.Undo
Else
Me!RqstNo.Undo
'Me!RqstNo.SetFocus

End If
End If
Click to expand...
Click to expand...
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

Before update on form 5
Msgbox prompt for existing record 3
dlookup help 0
DLookup duplicate question 3
triggers duplicate msg if change is made 2
if duplicate goto record 3
Finding identical addresses 3
Verifying the Ward counter in VBA code 1

Top