ACCESS FEED: RE: [MS_AccessPros] Strange happenings???

Rabu, 28 September 2011

RE: [MS_AccessPros] Strange happenings???

Connie-

The DLookup must be returning a Null (no record found), so you need to assign
the result to a Variant data type (can accept a Null) rather than a string to
avoid the error.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Wednesday, September 28, 2011 7:06 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Strange happenings???

Hi again,

I have a txtbox "InfoSheetLine" on the form "Listings". Upon entering it if it
is null or "" I would like to populate it with a string created from the
telephone numbers and names for the contacts. It will then be changed by the
user as appropriate. The code I have results in an error "Invalid Use of Null".
The line in code that is highlighted upon debugging is strI = DLookup ...

Thanks! Code, SQLs below
Connie

Code on Enter:

'Create the initial infosheet line that can then be altered
Dim strI As String
If ((IsNull(Me.TelCombo)) Or (Me.TelCombo = "")) Then
strI = DLookup("InfoSheetLine", "qListingsInfoSheetLine", "ListID = " &
Me.ListID)
Me.TelCombo.Value = strI
End If

The SQL "qNamNbrTyp" that creates the info that is concated in the next query
is:
SELECT ListingContacts.ListID, [FirstName] & " " & [Abbrev] & " " &
[ContactInfo] AS NamNbrTyp
FROM Contacts INNER JOIN ((Contact_PhoneEm INNER JOIN ListingContacts ON
Contact_PhoneEm.ContactID = ListingContacts.ContactID) INNER JOIN
ContactType_List ON Contact_PhoneEm.ContactTypeID =
ContactType_List.ContactTypeID) ON Contacts.ContactID =
Contact_PhoneEm.ContactID
WHERE (((ContactType_List.ContactType) Like '*phone*'));

The SQL for qInfoSheetListing where ConcatRelated is used:
SELECT Listings.ListID, ConcatRelated("NamNbrTyp","qListingsNamNbrTyp","ListID =
" & [Listings].[ListID]) AS InfoSheetLine
FROM Listings
WHERE (((Listings.CurrentListing)=-1) AND ((Listings.ListingAgency)=1));

------------------------------------

Yahoo! Groups Links