H
haresh7263
Guest
Form.Recordsource :: Runtime Error 2176 " The Setting for this property is too long"
Hello Experts,
I am new at VBA and I was trying modify the Query dynamically using VBA. When my code is executed I am getting Runtime Error 2176 " The Setting for this property is too long"; at the line where I am assigning the STRING (SQL) to the Form.Recordsource.
Me.Parent.Form("subform_qry_issues_and_actions").Form.RecordSource = strFilterSQLz
Anyhelp would be highly appreciated.
The Code goes as follows:
Private Sub btn_RunQuery_Click()
Dim strFilterSQLz As String
Dim strFilterSQL2 As String
'Dim temp1, temp2, temp3, temp4, Date1, Date2, Date3, Date4
'Variable to hold filtered SQL string
'Dim strFilterSQLz As String
Dim str
'Set default record source of form
strFilterSQLz = "SELECT ACTIONS_T.ActionID, ACTIONS_T.AID, ACTIONS_T.IssueID, ACTIONS_T.ActionPointDescription, ACTIONS_T.TrafficLightColour,ACTIONS_T.ActionPointPrimaryResponsibility, ACTIONS_T.ActionPointOwner, ACTIONS_T.DateAssignedToAP_Owner, ACTIONS_T.ProgressToDate,ACTIONS_T.OriginalTargetCompletionDate, ACTIONS_T.ActualCompletionDate, ACTIONS_T.ActionStatus, ACTIONS_T.ExternalLegalCost,ACTIONS_T.HoursEffortSpent, ACTIONS_T.ALT, ACTIONS_T.ELT, ACTIONS_T.IT, ACTIONS_T.CustServ, ACTIONS_T.MaRS, ACTIONS_T.MMB, ACTIONS_T.[C&I]," & _
" ACTIONS_T.[W'sale], ACTIONS_T.Regulatory, ACTIONS_T.Legal, ACTIONS_T.ExtAff, ACTIONS_T.Finance, ACTIONS_T.Agility, ACTIONS_T.Alinta,ACTIONS_T.Audit, ACTIONS_T.[P&C], ACTIONS_T.BillOps, ACTIONS_T.CustTransf, ACTIONS_T.NetwOps, ACTIONS_T.SuppServ, ACTIONS_T.BusSyst,ACTIONS_T.BSCProj, ACTIONS_T.TrafficLightColour, VISUAL_STATUS_T.TrafficLightColour, ACTION_TEAMS_T.ActionTeam, ACTIONS_T.ActionPointPrimaryResponsibility, ACTION_OWNER_T.ActionOwner, ACTIONS_T.ActionPointOwner, ACTIONS_T.ActionStatus, issues_and_actions_subquery.IssueName," & _
" issues_and_actions_subquery.IssueDescription, issues_and_actions_subquery.IssueID, issues_and_actions_subquery.TeamName, issues_and_actions_subquery.ItemStatus, issues_and_actions_subquery.Priority, issues_and_actions_subquery.[Issue Owner], issues_and_actions_subquery.DateIssueRaised,issues_and_actions_subquery.DateIssueClosed, issues_and_actions_subquery.REGISTERS_T.RegisterName, issues_and_actions_subquery.STATE_T.State, issues_and_actions_subquery.FUELS_T.Fuel, issues_and_actions_subquery.SYSTEM_T.SystemName, issues_and_actions_subquery.[CaseManaged?]," & _
" issues_and_actions_subquery.[Audit?], issues_and_actions_subquery.[Regulatory?], issues_and_actions_subquery.NumberOfCustomersImpacted, issues_and_actions_subquery.PotentialImpactOnAGL, issues_and_actions_subquery.PotentialIimpactOnAffectedParties FROM (ITEM_STATUS_T RIGHT JOIN (ACTION_OWNER_T RIGHT JOIN (ACTION_TEAMS_T RIGHT JOIN (VISUAL_STATUS_T RIGHT JOIN ACTIONS_T ON VISUAL_STATUS_T.VisualStatusID = ACTIONS_T.TrafficLightColour) ON ACTION_TEAMS_T.ActionTeamID = ACTIONS_T.ActionPointPrimaryResponsibility) ON ACTION_OWNER_T.ActionOwnerID = ACTIONS_T.ActionPointOwner) ON ITEM_STATUS_T.ItemStatusID = ACTIONS_T.ActionStatus) LEFT JOIN issues_and_actions_subquery ON ACTIONS_T.IssueID = issues_and_actions_subquery.IssueID" & _
" WHERE 1"
'============
If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDOFrom].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.DateAssignedtoAP_Owner >= #" & Format(tb_APDOFrom.Value, "mm/dd/yyyy") & "#"
End If
If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDOTo].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.DateAssignedtoAP_Owner <= #" & Format(tb_APDOTo.Value, "mm/dd/yyyy") & "#"
End If
If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDCFrom].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.ActualCompletionDate >= #" & Format(tb_APDCFrom.Value, "mm/dd/yyyy") & "#"
End If
If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDCTo].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.ActualCompletionDate <= #" & Format(tb_APDCTo.Value, "mm/dd/yyyy") & "#"
End If
' Set record source with filtered SQL
Me.Parent.Form("subform_qry_issues_and_actions").Form.RecordSource = strFilterSQLz
Me.Parent.Form("subform_qry_issues_and_actions").Form.Requery
'DoCmd.OpenQuery (strFilterSQL1)
End Sub
Hello Experts,
I am new at VBA and I was trying modify the Query dynamically using VBA. When my code is executed I am getting Runtime Error 2176 " The Setting for this property is too long"; at the line where I am assigning the STRING (SQL) to the Form.Recordsource.
Me.Parent.Form("subform_qry_issues_and_actions").Form.RecordSource = strFilterSQLz
Anyhelp would be highly appreciated.
The Code goes as follows:
Private Sub btn_RunQuery_Click()
Dim strFilterSQLz As String
Dim strFilterSQL2 As String
'Dim temp1, temp2, temp3, temp4, Date1, Date2, Date3, Date4
'Variable to hold filtered SQL string
'Dim strFilterSQLz As String
Dim str
'Set default record source of form
strFilterSQLz = "SELECT ACTIONS_T.ActionID, ACTIONS_T.AID, ACTIONS_T.IssueID, ACTIONS_T.ActionPointDescription, ACTIONS_T.TrafficLightColour,ACTIONS_T.ActionPointPrimaryResponsibility, ACTIONS_T.ActionPointOwner, ACTIONS_T.DateAssignedToAP_Owner, ACTIONS_T.ProgressToDate,ACTIONS_T.OriginalTargetCompletionDate, ACTIONS_T.ActualCompletionDate, ACTIONS_T.ActionStatus, ACTIONS_T.ExternalLegalCost,ACTIONS_T.HoursEffortSpent, ACTIONS_T.ALT, ACTIONS_T.ELT, ACTIONS_T.IT, ACTIONS_T.CustServ, ACTIONS_T.MaRS, ACTIONS_T.MMB, ACTIONS_T.[C&I]," & _
" ACTIONS_T.[W'sale], ACTIONS_T.Regulatory, ACTIONS_T.Legal, ACTIONS_T.ExtAff, ACTIONS_T.Finance, ACTIONS_T.Agility, ACTIONS_T.Alinta,ACTIONS_T.Audit, ACTIONS_T.[P&C], ACTIONS_T.BillOps, ACTIONS_T.CustTransf, ACTIONS_T.NetwOps, ACTIONS_T.SuppServ, ACTIONS_T.BusSyst,ACTIONS_T.BSCProj, ACTIONS_T.TrafficLightColour, VISUAL_STATUS_T.TrafficLightColour, ACTION_TEAMS_T.ActionTeam, ACTIONS_T.ActionPointPrimaryResponsibility, ACTION_OWNER_T.ActionOwner, ACTIONS_T.ActionPointOwner, ACTIONS_T.ActionStatus, issues_and_actions_subquery.IssueName," & _
" issues_and_actions_subquery.IssueDescription, issues_and_actions_subquery.IssueID, issues_and_actions_subquery.TeamName, issues_and_actions_subquery.ItemStatus, issues_and_actions_subquery.Priority, issues_and_actions_subquery.[Issue Owner], issues_and_actions_subquery.DateIssueRaised,issues_and_actions_subquery.DateIssueClosed, issues_and_actions_subquery.REGISTERS_T.RegisterName, issues_and_actions_subquery.STATE_T.State, issues_and_actions_subquery.FUELS_T.Fuel, issues_and_actions_subquery.SYSTEM_T.SystemName, issues_and_actions_subquery.[CaseManaged?]," & _
" issues_and_actions_subquery.[Audit?], issues_and_actions_subquery.[Regulatory?], issues_and_actions_subquery.NumberOfCustomersImpacted, issues_and_actions_subquery.PotentialImpactOnAGL, issues_and_actions_subquery.PotentialIimpactOnAffectedParties FROM (ITEM_STATUS_T RIGHT JOIN (ACTION_OWNER_T RIGHT JOIN (ACTION_TEAMS_T RIGHT JOIN (VISUAL_STATUS_T RIGHT JOIN ACTIONS_T ON VISUAL_STATUS_T.VisualStatusID = ACTIONS_T.TrafficLightColour) ON ACTION_TEAMS_T.ActionTeamID = ACTIONS_T.ActionPointPrimaryResponsibility) ON ACTION_OWNER_T.ActionOwnerID = ACTIONS_T.ActionPointOwner) ON ITEM_STATUS_T.ItemStatusID = ACTIONS_T.ActionStatus) LEFT JOIN issues_and_actions_subquery ON ACTIONS_T.IssueID = issues_and_actions_subquery.IssueID" & _
" WHERE 1"
'============
If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDOFrom].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.DateAssignedtoAP_Owner >= #" & Format(tb_APDOFrom.Value, "mm/dd/yyyy") & "#"
End If
If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDOTo].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.DateAssignedtoAP_Owner <= #" & Format(tb_APDOTo.Value, "mm/dd/yyyy") & "#"
End If
If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDCFrom].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.ActualCompletionDate >= #" & Format(tb_APDCFrom.Value, "mm/dd/yyyy") & "#"
End If
If [Forms]![frm_Reporting_Panel]![frm_Issues_Action_Args]![tb_APDCTo].Value <> "0" Then
strFilterSQLz = strFilterSQLz & " AND ACTIONS_T.ActualCompletionDate <= #" & Format(tb_APDCTo.Value, "mm/dd/yyyy") & "#"
End If
' Set record source with filtered SQL
Me.Parent.Form("subform_qry_issues_and_actions").Form.RecordSource = strFilterSQLz
Me.Parent.Form("subform_qry_issues_and_actions").Form.Requery
'DoCmd.OpenQuery (strFilterSQL1)
End Sub