Hi Forum,
I have an unbound text box control in a Report Group Header and want related data to populate same.

In the Report [ADPK] exits as bound control. (A field in table TBLACCDET)

I want to populate an unbound control with the Employer Name String which is [EDName] from TBLEMDET table.

The link to EDName is via EDPK which is the primary key for TBLEMDET and also a field in TBLACCDET.

DLookup would work as =DLookup("[EDName]","TBLEMDET", "How to join ADPK on the report with ADPK in TBLACCDET to find EDPK?")

This sql lists EDName for ADPK
SELECT TBLACCDET.ADPK, TBLEMPDET.EDName
FROM TBLEMPDET INNER JOIN TBLACCDET ON TBLEMPDET.EDPK = TBLACCDET.EDPK;

I am thinking of creating a Function that will return the string EDName when used as the record source of the report control but unsure exactly how.
Attempt so far just shows the Name for the first record of TBLACCDET where as I would want to filter the data by way of the control [ADPK] on the report.:confused:

If someone could point me the right direction it would be much appreciated.

I don't quite grasp your goal but are you sure you can't do this using a join in the recordsource query and a bound control?

Developers sometimes get too keen on DLookups because they look so simple but they can be quite inefficient. There is often a better way.

Trying to populate a report control where the record is not directly related ie it is one table away.

Here is a Public Function I have created, doesn't work..

Compile Error: sub or function not defined:confused:

Public Function FuncEmployerName() As String 'Return Employer Name to Control in Report

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim EmployerName As String
Dim MemberNumber As Integer 'Variable to hold ADPK from RptStatementNewStyle
Dim sqlString As String
Dim DocName As Report
Set DocName = Reports!RptStatementNewStyle 'Report Name
MemberNumber = Me.ADPK 'Member Number Bound Text Box Control in Report

sqlString = "SELECT TBLACCDET.ADPK, TBLEMPDET.EDName AS EmpName " & vbCrLf & _
"FROM TBLEMPDET INNER JOIN TBLACCDET ON TBLEMPDET.EDPK = TBLACCDET.EDPK " & vbCrLf & _
"WHERE (((TBLACCDET.ADPK)=MemberNumber));"

'Open Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(sqlString)

EmployerName = rst!EmpName 'Put Result of sql as Variable EmployerName

'Give Result of SumOverDue to Function CurrentBalanceAllSum
FuncEmployerName = EmployerName

'Close database variables
rst.Close
dbs.Close

End Function

The last part of the sql - MemberNumber is from my editing to get the value from the report.

I edited the code to make MemberNumber = 1956 and still got the error message (1956 being a valid member number)

Any suggestions much appreciated.

This works.:)

Appreciate any critique on code.

I guess I could convert this to a Select Case to resolve other Report Control's data in the one procedure.

Private Function EmployerName() As String ' Find Employer Name when MID (ADPK) is used as Variable
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim sqlString As String
Dim MemberID As Integer 'sqlVariable

MemberID = Me.Report.ADPK 'Member ID from Report

'Find Employer Name (EDName) from Employer Table (TBLEMPDET) where Member ID (ADPK) exists on Report RptStatmentNewStyle
sqlString = "SELECT TBLACCDET.ADPK, TBLEMPDET.EDName AS EmpName" & vbCrLf & _
"FROM TBLEMPDET INNER JOIN TBLACCDET ON TBLEMPDET.EDPK = TBLACCDET.EDPK " & vbCrLf & _
"WHERE (((TBLACCDET.ADPK)=" & MemberID & "));"

'Open Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(sqlString)

EmployerName = rst!EmpName 'Put Result of sql (EmpName) Function EmployerName() Result

'Close database variables
rst.Close
dbs.Close

End Function