DLookup( ) function

[Playa]

Can you not use the DLookup function on a Primary Key field? Every time I
push the save button on a data entry form the Access Error '3202'
.....duplicate values in primary key...... appears instead of my message box
from my if statement which is suppose to be checking for the duplicate
values. My if statement is....

Dim varResult As Variant

'strWhere = "DrawingNum = " & Me.DrawingNum
Me.DrawingNum.SetFocus
varResult = DLookup("DrawingNum", "tbl_DrawingsAndData", _
"DrawingNum = '" &
Me.DrawingNum & "'")
If Not IsNull(varResult) Then
strMsg = "Drawing " & varResult & " Already Exists."
End If

I have been on this problem for two days now and for the life of me can't
figure out what is going on. I am not that dumb and everyone says to use the
DLookup to check for the duplicates so I am thinking maybe you can't for
Primary Keys.

 

[Paul B.]

Just a thought, but I was told to try DLast instead of DLookup and although I
won't pretend to understand your specific problem, DLast might work because
it is supposed to return the last record meating your criteria and since you
are looking for a specific drawing number, would there not only be one
instance of that number? therefor returning only one record, hopefully
eliminating your error?

Cheers

 

[Dirk Goldgar]

Can you not use the DLookup function on a Primary Key field? Every
time I push the save button on a data entry form the Access Error
'3202' ....duplicate values in primary key...... appears instead of
my message box from my if statement which is suppose to be checking
for the duplicate values. My if statement is....

Dim varResult As Variant

'strWhere = "DrawingNum = " & Me.DrawingNum
Me.DrawingNum.SetFocus
varResult = DLookup("DrawingNum", "tbl_DrawingsAndData", _
"DrawingNum = '" &
Me.DrawingNum & "'")
If Not IsNull(varResult) Then
strMsg = "Drawing " & varResult & " Already Exists."
End If

I have been on this problem for two days now and for the life of me
can't figure out what is going on. I am not that dumb and everyone
says to use the DLookup to check for the duplicates so I am thinking
maybe you can't for Primary Keys.

Sure you can. What type of field is DrawingNum, in tbl_DrawingsAndData?
Is it a text field, or a number or autonumber field? If it's a number
or autonumber field, then you should leave out the single-quotes around
the value in the criterion:

varResult = DLookup("DrawingNum", "tbl_DrawingsAndData", _
"DrawingNum = " & Me.DrawingNum)

If that's not the problem, then something else is wrong, but it's not
what you're thinking. Maybe you've misspelled the name of the field in
the table or something.

 

[Van T. Dinh]

From the 2 threads you posted, I am guessing that you check for duplicate
key too late and before your code is executed, JET gets in first and gives
you the JET error message.

Which Event are you using to run the validation / duplication check?

BTW, I would use DCount() rather than DLookUp() to check for existence of a
value.

 

[Playa]

I am using this code in the On Click Event Procedure for my Save button.
Should I be using it somewhere else????

 

[Van T. Dinh]

Post the code for the CommandButton_Click Event.

--
HTH
Van T. Dinh
MVP (Access)

 

[Playa]

Here is the code I am using in the Event....All Data Validation in this code
works except when I check for duplicate data in the DrawingNum Text field.

Private Sub cmdSave_Click()

Dim varResult As Variant

Me.DrawingNum.SetFocus
varResult = DCount("DrawingNum", "tbl_DrawingsAndData", "DrawingNum = '" &
Me.DrawingNum & "'")
If Not IsNull(varResult) Then
strMsg = "Drawing " & varResult & " Already Exists."
End If


Me.TypeID.SetFocus
If Me.TypeID.Text = "" Then
MsgBox "You Must Enter A Type"
Exit Sub
End If

Me.EmpID.SetFocus
If Me.EmpID.Text = "" Then
MsgBox "You Must Enter An Employee"
Exit Sub
End If

Me.ManagerID.SetFocus
If Me.ManagerID.Text = "" Then
MsgBox "You Must Enter An Approval Authority Type"
Exit Sub
End If

On Error GoTo Err_cmdSave_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "frm_DrawingControls_Add", acSaveNo

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:

MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

 

[Van T. Dinh]

1. Check Access / Access VB Help on the usage of DCount(). If you don't
know anything check Help before using it rather than guessing. DCount
always return a numeric value so your code is incorrect at the moment.

2. However, I guess there is something else happens before your Event
Procedure. Try putting a break on the Procedure declaration and see if:

* You get the error before you get to the procedure.
* If you get to the procedure, which line of code gives you the JET
message.