Subform referencing - causing problems(beginner)

[pauljkeenan]

Hi People

Im having this continuous problem with subform referencing. Let me describe the form fist of all. I have a main form, which is used as a navigaiton window to switch between various forms by just clicking one of the command buttons across the top. (frmMain)

So the user selects "search records" from the main form. This opens the search form (frmSearch)inside the frmMain window.

This search form has search criteria at the top, and contains two subforms, which are not nested but at the same level. frmSearchSub is a datasheet which displays the resutls of the user's search. When the user clicks on one of the fields, more information is displayed in the second subform (frmDetails)

frmMain -> frmSearch -> frmSubSearch
-> frmDetails

When I just open the search form and click on a record to display more record details in the frmDetails it works fine. However, when I open the main form, and within this, attempt to display the details I get an error saying it cannot find frmSearch.

Ive tried messing around with the code, and Ive checked various turoials on subform referncing but still the problem persists. Here's the code, does anyone know how to solve this problem? any help at all will be much appreciated.


Private Sub Reference_DblClick(Cancel As Integer)
Dim strSql As String, strWhere As String

strSql = "SELECT tblRecords.Company, tblRecords.Name, tblRecords.Phone, tblRecords.Sector, tblRecords.Website, tblRecords.Details, tblRecords.IrlCompany, tblRecords.IrlName, tblRecords.IrlPhone, tblRecords.IrlSector, tblRecords.IrlWebsite, tblRecords.IrlDetails " & _
"FROM tblRecords"

strWhere = "WHERE"

If Not IsNull(Reference.Text) Then
strWhere = strWhere & " (tblRecords.Reference) = " & Reference.Text & " AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
Forms!frmSearch!frmDetails.Form.RecordSource = strSql & " " & strWhere & " " 'this is the problematic line of code

End Sub

Thanks lads

Paul

 

[PHV]

Perhaps something like this:
Forms!frmMain!frmSearch.Form!frmDetails.Form.RecordSource = strSql & " " & strWhere

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pauljkeenan]

Hi PH

Thanks for the help, it put me on the right track actually and its working fine now.

Forms!frmMain!frmMainSub!frmDetails.Form.RecordSource = strSql & " " & strWhere

I had been using the subform name, not the control name. an amateur mistake indeed.

thanks again, much appreciated