Option Compare Database
Public ComboSQLSelect As String
Public ComboSQLWhere As String
Public ComboSQLOrderby As String
Public AttSQLSelect As String
Public AttSQLWhere As String
Public AttSQLOrderby As String
Public AGSQLSelect As String
Public AGSQLWhere As String
Public AGSQLOrderby As String
Private Sub CreateFilter()
On Error GoTo Err_CreateFilter
If Me.End_dtFilter.Value = False Then
ComboSQLWhere = " WHERE " & GFilter
GSQLWhere = " WHERE " & GFilter
AGSQLWhere = " "
Else
ComboSQLWhere = " WHERE " & GFilter & " And ((G.End_dt) Is Null)"
GSQLWhere = " WHERE " & GFilter & " AND ((G.End_dt) Is Null) AND ((F.End_dt) Is Null) AND ((A.End_dt) Is Null)"
AGSQLWhere = " WHERE((AG.End_dt) Is Null)"
End If
Me.findgroupcombo.RowSource = ComboSQLSelect & ComboSQLWhere & ComboSQLOrderby
MsgBox Me.findgroupcombo.RowSource
Me.Form.RecordSource = GSQLSelect & GSQLWhere & GSQLOrderby
Me.frmSmallGroupSubform.Form.RecordSource = AGSQLSelect & AGSQLWhere & AGSQLOrderby
Me.findgroupcombo.Requery
Me.Requery
Me.frmSmallGroupSubform.Form.Requery
Me.Refresh
Exit_CreateFilter:
Exit Sub
Err_CreateFilter:
MsgBox Err.Description
Resume Exit_CreateFilter
End Sub
Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim Action As Integer
Dim ActionDuration As Integer
Dim ActionType As String
Dim con As New ADODB.Connection
ActionType = "Staff"
Action = 1
' GAQType_ID is ActionType_ID "Supervisor" was read from table roles
If GlCurrentUserActionPartner(GAQType_ID) <> "Supervisor" Then
MsgBox "You don't have permission to access this form."
DoCmd.OpenForm "frmswitchboard"
DoCmd.Close acForm, "frmSmallGroup", acSavePrompt
DoCmd.Maximize
Exit Sub
End If
Me![FormHeaderLabel].Caption = SName & " " & GGroup & " Growth Groups"
ComboSQLSelect = "SELECT G.Group_ID, G.Code, G.Name FROM tblGroup AS G"
ComboSQLWhere = " WHERE " & GFilter & " And ((G.End_dt) Is Null)"
ComboSQLOrderby = " ORDER BY G.Code;"
GSQLSelect = "SELECT G.Address, F.Address1, F.Suburb, F.Postcode, G.Group_ID, G.Code, G.GroupType_ID, G.Name, G.Description, G.Day, G.Time, G.Frequency, G.Purpose, G.Notes, G.Start_dt, G.End_dt, G.Update_dt, G.Update_by, G.Person_responsible, A.Mobile, A.Email1, G.GrowthGroup, GT.Type_code FROM ((tblGroup AS G INNER JOIN tblFamily AS F ON G.Address = F.Family_ID) INNER JOIN tblAttendee AS A ON G.Person_responsible = A.Attendee_ID) INNER JOIN tblGroupType AS GT ON G.GroupType_ID = GT.GroupType_ID"
GSQLWhere = " WHERE " & GFilter & " AND ((G.End_dt) Is Null) AND ((F.End_dt) Is Null) AND ((A.End_dt) Is Null)"
GSQLOrderby = " ORDER BY G.Code;"
AGSQLSelect = "SELECT AG.AttendeeGroup_ID, AG.Attendee_ID, AG.Group_ID, AG.Start_dt, AG.End_dt, AG.Role, AG.Notes, AG.Update_dt, AG.Updated_by, AG.Attendance, G.Code, A.Mobile, F.HPhone, F.Family_ID, F.DirectoryEntry, AG.LastAttended, AP.Pager_no, A.Employer, A.Grade FROM ((tblAttendeeGroup AS AG INNER JOIN (tblAttendee AS A INNER JOIN tblFamily AS F ON A.Family_ID = F.Family_ID) ON AG.Attendee_ID = A.Attendee_ID) INNER JOIN tblGroup AS G ON AG.Group_ID = G.Group_ID) LEFT JOIN tblAttendeePager As AP ON A.Attendee_ID = AP.Attendee_ID"
AGSQLWhere = " WHERE((AG.End_dt) Is Null)"
AGSQLOrderby = " ORDER BY A.LastName, A.PreferredName;"
CreateFilter
Me.Refresh
DoCmd.Maximize
Me.Requery
Me!findgroupcombo.SetFocus
con.ConnectionString = ADODBConnString
con.Open
If getActVar(con, ActionType, Action, ActionDuration) Then
Me![Days] = ActionDuration
Else
Err.Raise Number:=21110, _
Description:="Failed to get Staff Action 1 time"
End If
con.Close
Set con = Nothing
Exit_Form_Load:
Exit Sub
Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load
End Sub