Karen Schaefer
asked on
Refreshing subform based on selection from another subform
My form contains Mainform, Sub1, Sub2. On load of the Main form both subforms are hidden. On click from Listbox, Sub1 becomes visible and list the appropriate data based on selection.
Step 2. On Click of the Managers' name then Sub 2 should become visible and the data should be all employees assigned to the selected Manager.
However, what is happening is the Sub2 is either blank or will not requery on change of Manager's names. See my code and let me know what I am missing.
When I return to the form from Design view it seems to be working correctly - What is preventing it from opening the sub2 form correctly with the correct data on start of the form - I am getting the error until I change the view from live to design and back again.
Karen
Step 2. On Click of the Managers' name then Sub 2 should become visible and the data should be all employees assigned to the selected Manager.
However, what is happening is the Sub2 is either blank or will not requery on change of Manager's names. See my code and let me know what I am missing.
When I return to the form from Design view it seems to be working correctly - What is preventing it from opening the sub2 form correctly with the correct data on start of the form - I am getting the error until I change the view from live to design and back again.
Karen
'******** On Load of Main Form ************************
Private Sub Form_Load()
On Error GoTo Form_Load_Error
If Forms!Switchboard.Visible = True Then
Forms!Switchboard.Visible = False
End If
Forms![frmTrainStatus].Section(0).Visible = False
Me.sub1.Visible = False
Me.sub2.Visible = False
Me.lblSub2.Vertical = False
If IsLoaded("frmTrainStatus_Sub") Then
DoCmd.DoMenuItem acFormBar, 4, 0, , acMenuVer20
Me.LblMgrHdr.Visible = True
Else
DoCmd.Restore
Me.LblMgrHdr.Visible = False
End If
On Error GoTo 0
Exit Sub
Form_Load_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load of VBA Document Form_frmTrainStatus"
End Sub
'**************** On Selection of Listbox*****************
Private Sub lstUnitChief_AfterUpdate()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSql1 As String
Dim strSQL2 As String
Dim MyString As String
Set db = CurrentDb()
MyString = vbNullString
With Me.lstUnitChief
If .ItemsSelected.Count > 0 Then
For Each i In .ItemsSelected
MyString = .ItemData(i) & "," & MyString
Next i
MyString = Left(MyString, Len(MyString) - 1)
End If
End With
Forms![frmTrainStatus].Section(0).Visible = True
Me.sub1.Visible = True
strSQL = "Select * from qryEmpTrain_byMGRSummary Where UCBEMS IN(" & MyString & ")" & _
"Order by MgrName"
Set qdf = db.QueryDefs("qrytemp")
qdf.SQL = strSQL
qdf.Close
Forms![frmTrainStatus]![sub1].Form.RecordSource = "qryTemp"
If Me.LblMgrHdr.Visible = False Then
Me.LblMgrHdr.Visible = True
End If
Me.sub1.Requery
On Error GoTo 0
Exit Sub
cboUnitChief_AfterUpdate_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboUnitChief_AfterUpdate of VBA Document Form_frmTrainStatus"
End Sub
'********************************************************
Private Sub Manager_Click()
On Error GoTo Manager_Click_Error
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSql1 As String
Set db = CurrentDb()
gBEMS = Me.ManagerID
gORG = Me.OrgCtr
gMgrEmail = Nz(DLookup("StableEmail", "tblEmployee", "BEMS = " & gBEMS))
strSQL = "Select * from qryMgrTrainTotals"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Forms![frmTrainStatus].Section(2).Visible = True
Forms![frmTrainStatus]![sub2].Visible = True
Forms![frmTrainStatus]![lblSub2].Visible = True
Forms![frmTrainStatus]![sub2].Requery
Me.Refresh
DoCmd.Maximize
Else
Call _
MsgBox("Please make another selection, the manager you selected currently does not any employees assigned to them. Please update the Employee Info database and return upon completion.", _
vbCritical, "No Data Found")
End If
On Error GoTo 0
Exit Sub
Manager_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Manager_Click of VBA Document Form_frmMgrTrainStatus_sub"
End Sub
Try Forms![frmTrainStatus]![su b2].Form.Requery
ASKER
Tried it ran into the same issue when the mdb is open for the first time, until it is opened into design view then it works correctly. That is why I am so stumped - I have tried various versions running into the same issue.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please see previous comment