CODEWITHSUNDEEP
vbams-access
mightymax
mightymax

Reputation: 431

DLookup function not working in subform, MS Access VBA

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

Upvotes: 0

Views: 929

Answers (2)

mightymax
mightymax

Reputation: 431

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")

Upvotes: 0

June7
June7

Reputation: 21370

Recommend naming the subform container control different from the object it holds, such as ctrSWList. Then code in a main form control:

=DLookUp("Version","EmpSWSum","Software = '7-Zip' AND EmpID=" & [ctrSWList]![EmpID])

However, if this expression is in a control on the subform and you want to reference another control on the subform, no need for the form names and classes prefix.

=DLookUp("Version","EmpSWSum","Software = '7-Zip' AND EmpID=" & [EmpID])

That should also work if the subform is opened as a standalone.

Upvotes: 0

Related Questions