Trapping a DLookup error (1 Viewer)

P

Peter Bellamy

Registered User.
Local time
Today, 09:08
Joined
Dec 3, 2005
Messages
295
Is there a neat way of trapping the error and carrying on with the code from Dlookup when it finds the field it is looking for is empty (not null but empty)?

Up till now I have trapped it by its error number then returned back to the code, but there must be better way?

Cheers
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:08
Joined
Aug 30, 2003
Messages
36,133
In my view, there are two general schools of thought regarding error trapping. One is "code to prevent foreseeable errors and let the error trap catch unforeseen errors", the other is "let the error trap handle everything". I generally belong to the former, so I would have:

Variable = DLookup(...)
If Len(Variable & vbNullString) = 0 Then

where the If statement will catch the result either being Null or a ZLS.
 
P

Peter Bellamy

Registered User.
Local time
Today, 09:08
Joined
Dec 3, 2005
Messages
295
Thanks both.
I agree with you Paul however my code is still not trapping and I am getting an 'Invalid use of Null' error from the Dlookup line when there is an empty field being requested.

Private Sub BtnRetRec_Click()
On Error GoTo Err_BtnRetRec_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim StrGdsRec As String

StrGdsRec = DLookup("[goods_returnno]", "Goods", "[goods_aserialno] = '" & Me![condensing_serialno] & "'")

'Debug.Print StrGdsRec
If Len(StrGdsRec & vbNullString) = 0 Then
MsgBox " No record found"
Else
stDocName = "Return record"
'stLinkCriteria = "[return_key] = " & "'" & StrGdsRec & "'"
stLinkCriteria = "[return_no] = " & StrGdsRec
'Debug.Print stLinkCriteria
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_BtnRetRec_Click:
Exit Sub

Err_BtnRetRec_Click:
MsgBox Err.Description, vbExclamation, "Error #: " & Err.number
Resume Exit_BtnRetRec_Click
End Sub

This works fine if there is a [goods_aserialno] but errors when it has not been filled.
One way around it would be set its default value to null, but that seems a fudge!
 
V

vbaInet

AWF VIP
Local time
Today, 09:08
Joined
Jan 22, 2010
Messages
26,374
Use the Nz() function below:

StrGdsRec = DLookup("[goods_returnno]", "Goods", "[goods_aserialno] = '" & Nz(Me![condensing_serialno], "") & "'")

By the way, I noticed you've got "
Code: 
" on that line! Typo?

What is the data type of condensing_serialno?
 
P

Peter Bellamy

Registered User.
Local time
Today, 09:08
Joined
Dec 3, 2005
Messages
295
Thanks for the Nz suggestion but it still error, 'Invalid use of Null'
[condensing_serialno] is a String
Yup a typo, I was trying so highlight the Dlookup line in red for the post. D'oh !
 
DCrake

DCrake

Remembered
Local time
Today, 09:08
Joined
Jun 8, 2005
Messages
8,632
What you need is the Nz() outside of the DLookup()

Code: 
X = Nz(Dlookup(),0)
If X = 0 Then
  Null, False or ZLS
Else
  Value Returned
End If


David
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:08
Joined
Aug 30, 2003
Messages
36,133
David got you sorted out, but just for general information your error was because StrGdsRec was declared as a String, which can't handle a Null. Changing that to a Variant would have avoided the error as well.
 
You must log in or register to reply here.

Similar threads

N
Trapping System Errors in a Global Module
2
Replies
24
Views
1,389
ebs17
E
C
Solved Key Violation Error Trapping During TransferText Operation
2 3
Replies
58
Views
1,910
Pat Hartman
P
N
Solved How to convert "" to null in MS Access VBA
2
Replies
22
Views
554
NauticalGent
NauticalGent
R
Solved Access gives Null error
Replies
6
Views
315
R-Berg
R
T
VBA for looking up a record between Start and End Data.
Replies
6
Views
313
tucker61
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom