DLookup function not working in subform, MS Access VBA

Question

I have a main form "fmEmpHWList2" inside it there are two subforms. In the subform "frmSWList2" I have textfields that I'm using Dlookup functions to get values from a table. When I tested this on the subform alone everything worked. But when I brought the subform into the main form I get the "#Name?" error.

Here is my current code within the mainform that gives me the error and I need to fix:

=DLookUp("Version","EmpSWSum","Software = '7-Zip' AND EmpID=" & [Forms]![fmEmpHWList2]![frmSWList2].[Form]![EmpID])

In the subform alone this code works

=DLookUp("Version","EmpSWSum","Software = 'ADG R4i CSDB Client' AND EmpID=" & [Forms]![frmSWList2]![EmpID])

Things to note: EmpID is a number

I appreciate your taking the time to help me solve this

Hi. This link might be helpful. access.mvps.org/access/forms/frm0031.htm. It sounds like you need to do the following: Me!Subform1.Form!ControlName — Ryan Wildry
I tried that and it didn't work. I've also referenced that form multiple times and tried man different combinations without success. — mightymax
@RyanWildry Me is not a valid reference inside a Control Source property... at least Access always automatically wraps it like [Me] and then returns an error. Is the table you linked to for referencing within VBA modules? — C Perkins
I suggest simplifying the debugging process by 1) Placing a new textbox on the main form. 2) Set the new textbox Control Source property to =[subformCONTROLname].[Name]. 3) View the main form. The new textbox should simply show the name of the subform control. If that works, then add a 2nd new textbox with =[subformCONTROLname]![EmpID]. — C Perkins

mightymax mightymax · Accepted Answer · 2017-07-12T19:53:49

I took a different approach and made a function instead of doing a DLookUp. I think it's a better approach. I'm loading the function in the form open even.

1 Function RunParameterQuery_DAO(Asset As String, Software As String, sTextBox As String) As String
2   ' Comments: Runs a query containing parameters
3   Const cstrQueryName As String = "qrySWVers"
4   Dim dbs As DAO.Database
5   Dim qdf As DAO.QueryDef
6   Dim rst As DAO.Recordset
7   
8   Set dbs = CurrentDb()
9   Set qdf = dbs.QueryDefs(cstrQueryName)
10   qdf.Parameters("asset") = Asset
11   qdf.Parameters("name") = Software
12   ' Open recordset on the query
13   Set rst = qdf.OpenRecordset()
14   Do While Not rst.EOF
15     Me.Controls(sTextBox).Value = rst![SWVer]
16     rst.MoveNext
17   Loop
18    MsgBox (Me.Controls(sTextBox).Value)
19   rst.Close
20   qdf.Close
21   dbs.Close
22 End Function

To run it

Call RunParameterQuery_DAO("L52651", "TortoiseSVN", "TortoiseSVN")

DLookup function not working in subform, MS Access VBA

2 Answers