troubleshooting Question

Determine subform name using controls to pass name

Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America asked on
Microsoft AccessMicrosoft Development
28 Comments1 Solution340 ViewsLast Modified:
I have a Mainform containing 5 subforms - and I need to set the recordsouce for each subform on selection of a drop down field.  I need to look at each control (ctl) determine the name of the ctl(subform) and if equals a particular name then set the query name accordingly.

see the code that starts with :
" If sfrm.Name = "frmAssignSchoolPrg_sub" Then
                  nQry = "QrySchoolPrograms""

I am currently not getting the sfrm value - what am I missing?

K
Private Sub cboSearch_AfterUpdate()
    Dim rs As Object
    Dim nRecID As String
    Dim strSQL As String
    Dim nQry As String
    Dim curDB As DAO.Database
    Dim frm As Access.Form
    Dim sfrm As Access.SubForm
    Dim ctl As Access.Control
   
    On Error GoTo cboSearch_AfterUpdate_Error
    
    Set curDB = CurrentDb()

    
    nRecID = Me.cboSearch
    Set frm = Forms![frmPrograms]
        For Each ctl In frm.Controls
            If ctl.ControlType = acSubform Then
                If sfrm.Name = "frmAssignSchoolPrg_sub" Then
                  nQry = "QrySchoolPrograms"
                  GoSub Process
                ElseIf sfrm.Name = "frmlPrgContacts_sub" Then
                  nQry = "qryPrgContacts"
                  GoSub Process
                ElseIf sfrm.Name = "frmPrgCert_sub" Then
                  nQry = "qryPrgCertificates"
                  GoSub Process
                ElseIf sfrm.Name = "frmGrants_Assign_sub" Then
                  nQry = "qryGrantAssignments"
                  GoSub Process
                ElseIf sfrm.Name = "frmAttachments_sub" Then
                  nQry = "qryPhotoAttachments"
                  GoSub Process
                End If
Process:
                If nRecID = 0 Then
                    strSQL = "Select * from " & nQry & ""
                Else
                    strSQL = strSQL & " WHERE ProgramRecID = " & nRecID & ""
                End If
                
                strSQL = "SELECT * FROM  " & nQry & ""
                ctl.frm.sfrm.RecordSource = strSQL
Return:
            End If
       Next ctl
       Set ctl = Nothing
    '            Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", strSQL)
    '            Set qdf = curDB.QueryDefs("TempQry")
    '            qdf.SQL = strSQL
    '            qdf.Close
                    
                  ' End If
   
    On Error GoTo 0
   Exit Sub

cboSearch_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboSearch_AfterUpdate of VBA Document Form_frmPrograms"

End Sub
ASKER CERTIFIED SOLUTION
IrogSinta

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 28 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 28 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros