Error setting rowsource

[SirPoonga]

I am gettinga n error box:
Run-time error '2176':
The setting for this property is too long.

I am generating a sql statement, it is rather large. The code has been
working but I recently had to make a structure change.

I am making a custom filter form. Based on selections form drop down
boxes I generate a SQL statement then assign the following RowSource to
it.
Me.sfResults.Form.RecordSource = sqlcmd

The subform is just a form in datasheet view to output the results of
the filter.

Putting a bookmark at that line in the Immediate Window I types 'print
sqlcmd'. I copied the command to a query builder. The query builder
ran it just fine.
So is there a text limit for rowsource? Anything I can do about it?

 

[SirPoonga]

Also, the len(sqlcmd) = 2112
If I take the order by clause off it is 1792

The basic idea of the sql statement is assign the SELECT and JOIN
clauses. Then based on if a drop down box was used (IE the * wildcard
item isn't selected) add that to the WHERE clause to the appropiate
field. Then attach an ORDER BY clause.

My function basically does this
Sub filterInput()
Dim tempwhere As String
Dim orderby As String
orderby = " ORDER BY tblMachineParts.ID, tblMachine.make,
tblMachineType.order, tblMachine.model, tblMachineParts.sticklength,
tblMachineParts.quicklatchID, tblMachineParts.bucketID,
tblMachineParts.width, tblMachineParts.toothID,
tblMachineParts.number_of_teeth, tblMaterialClampParts.clamp_style,
tblTineAssembly.tine_style DESC"
tempwhere = ""
....
'for all the drop down boxes do something simular to this
If StrComp(cboType, "*") <> 0 Then
tempwhere = tempwhere & " AND tblMachineParts.machine_type='" &
cboType & "'"
End If
....
sqlcmd = 'Some long SELECT and INNER JOIN string
sqlcmd = sqlcmd & tempwhere
sqlcmd = sqlcmd & orderby
Me.sfResults.Form.RecordSource = sqlcmd

 

[SirPoonga]

The limit 2048 characters. I tested it. I took a string I knew didn't
work (IE the length was too long)/ I divided it by 4 because most
sizes are multiples of 4 due how long a byte is. The closest multiple
of 2/4/8/16/32/64/128/256/512/... is 512. 512*4 is 2048. I tried a
string of length 2048 and 2049, 2049 failed.

Time to go consult the knowledge base on this one...

 

[SirPoonga]

Heh, I guess I just needed to see my problem written out rather than
thinking about it.

I didn;t realize there is an orderby property for a form and that the
Filter property is just a where clause.
Problem solved

Me.sfResults.Form.RecordSource = sqlcmd
Me.sfResults.Form.orderby = orderby
Me.sfResults.Form.Filter = tempwhere

 

[SirPoonga]

Well, it's not working now. When I get tot he orderby and filter line I
get the error "You can;t assign a value to this object"