Failed to executes DLOOKUP in MS-ACCESS VBA

0
votes

I have a VBA code below, but every time when I try to execute the VBA code it gives me error on [Dlookup] I spent hours and hours trying to get the Dlookup right, but no luck. Appreciate if you can have a look of it and provide me with some insights or solutions. Thanks.

Sub Quality()
Dim Sql As String    

StrDate = InputBox("YYYY/MM/DD")
Sql = "Insert Into Roster_QC Select dbo_Recipients.[UniqueID] AS [UID], 
DLookUp("[Address1]", "dbo_NamesAndAddresses",  "[PersonID]=' " & [UID] & " ' AND [PrimaryAddress]=1" ) AS [Address]…FROM…

WHERE…ORDER BY

CurrentDb.Execute Sql
DoCmd.OpenReport "RosterQC_Report", View:=acViewPreview
End Sub

Also, I have changed the Dlookup as of below

DLookUp(""[Address1]"", ""dbo_NamesAndAddresses"",  ""[PersonID]='" & [UID] & "' AND [PrimaryAddress]=1"" ) AS Address

This give rise to error - [UID] External Name not defined.

1
ms-access
The error message is pretty clear. You have no [UID]. If this is run behind a form, use Me!UID.Value. – Gustav
Are you trying to embed this DLookup into SQL statement? Because what you have now will save the same address for however many records are retrieved by the SELECT. Why aren't you just joining dbo_Recipients and dbo_NamesAndAddresses instead of DLookup to retrieve Address1 info? – June7
Why save duplicate data to Roster_QC instead of just using a query joining tables as report RecordSource and apply filter to report? – June7

1 Answers

0
votes

Thanks @June7, joining dbo_Recipients and dbo_NamesAndAddresses fixed my problem.