Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-13-2005, 11:23 PM   #1
haresh7263
Guest
 
Posts: n/a
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").F orm.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.ActionPoint PrimaryResponsibility, ACTIONS_T.ActionPointOwner, ACTIONS_T.DateAssignedToAP_Owner, ACTIONS_T.ProgressToDate,ACTIONS_T.OriginalTargetC ompletionDate, ACTIONS_T.ActualCompletionDate, ACTIONS_T.ActionStatus, ACTIONS_T.ExternalLegalCost,ACTIONS_T.HoursEffortS pent, 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.RegisterNa me, 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.NumberOfCustomersImpac ted, issues_and_actions_subquery.PotentialImpactOnAGL, issues_and_actions_subquery.PotentialIimpactOnAffe ctedParties 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").F orm.RecordSource = strFilterSQLz
Me.Parent.Form("subform_qry_issues_and_actions").F orm.Requery
'DoCmd.OpenQuery (strFilterSQL1)
End Sub

  Reply With Quote
Old 12-14-2005, 01:25 AM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 798 Times in 785 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
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
namliam is offline   Reply With Quote
Old 10-19-2011, 12:14 AM   #3
Kowalski
Newly Registered User
 
Join Date: Jul 2007
Posts: 112
Thanks: 7
Thanked 1 Time in 1 Post
Kowalski is an unknown quantity at this point
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?

Kowalski is offline   Reply With Quote
Old 10-19-2011, 03:12 AM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,421 Times in 2,387 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
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.

vbaInet is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Winamp and Access raindrop3 Modules & VBA 24 04-04-2006 03:57 PM
Setting table field default property in VBA David Thorpe Modules & VBA 4 10-03-2005 03:59 PM
Setting Table Format Property swbrodie Tables 2 05-15-2003 11:34 AM
Setting locked property dan-cat Modules & VBA 1 06-27-2002 02:06 PM
Setting Locked Property dan-cat Forms 2 06-04-2002 02:27 AM




All times are GMT -8. The time now is 03:39 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World