Form.Recordsource :: Runtime Error 2176 " The Setting for this property is too long" (1 Viewer)

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

You can use aliases for your (long) tablenames.
eg. issues_and_actions_subquery can be replaced by ias as an alias. Thus reducing the length of the query greatly.

Alternatively you can base your form of a query and put the SQL in the query.
Currentdb.querydefs("Query name").sql = SQLSTRING

Second alternative may be to look into the FILTER property in the form. Thus only re-writing that part (the where clause) rather than the full SQL.

Regards & GL

Re: Form.Recordsource :: Runtime Error 2176 " The Setting for this property is too lo

Hi
I'm trying to apply a filter but the filter's length is about 7000 chars.
I get the same error when I execute:
MyForm.Filter = NewFilter 'where new Filter contains the 7000 chars

Any way past that?

Re: Form.Recordsource :: Runtime Error 2176 " The Setting for this property is too lo

Kowalski, this is a very old thread. You could have just created your own.

One advice is if you're using lots of OR, change it to the IN operator. We would need to see your NewFilter string to be able to give suggestions.