Solved

Ms Access - VBA DLookup returning Null Values

Posted on 2008-10-16
2
932 Views
Last Modified: 2013-12-25
Afternoon,

I have a set of DLookups which populate a text field accoring to what ID number someone puts in another text field. Can someone tell me how I would output a message box if that ID retrn no records? My current code is;

strSPNValue = txtSPN.Value
txtSPNName = DLookup("[spn_name]", "Sel_Historical_SPN_Lookup", "SPN ='" & strSPNValue & "'")

Thanks
0
Comment
Question by:andyb7901
2 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 22730761
Use an If-then block.  If Dlookup returns a Null, then there are no records found.

Dim varLU as Variant
strSPNValue = txtSPN.Value
VarLU =  DLookup("[spn_name]", "Sel_Historical_SPN_Lookup", "SPN ='" & strSPNValue
if isNull(VarLU) then
    MsgBox "No records found"
   ' etc
end if
txtSPNName = VarLU
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 22730834
Or:

txtSPNName = Nz(DLookup("[spn_name]", "Sel_Historical_SPN_Lookup", "SPN ='" & strSPNValue & "'"), "")
If txtSPNName = "" Then
    'do something
End If
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question