I have a Access 97 that I am working with (not able to upgrade for politicle reasons) and I am trying to set the Record Source of a report to a text box control on a form and I get that an error saying the string is too long.

If I copy and past the SQL code into the Record Source I get: "the text is too long to be edited"

The SQL code is about 2000 characters in total length.

How the heck do I set the reports record source to my SQL string?

I can't make a query and save it, because the report is based on an SQL statment that I create in code based on a bunch of selections "From" and "Thru" on a form, so it is all generated on the fly?

__________________
Mitch

Set the reports recordsource to an ADO (or DAO) Recordset instead. Use the same string as the source of the recordset.

Hope that works.



Sal

Sal, would I do that in code on the reports On Open event, and have that code reference the from's controls?

On the Report's Open Event. you oipen your recordset and set the recordsource of the report:

me.recordsource = rs
or
me.recordset = rs

one of the two.






Sal

Sal, I get erros either way. Here is what I am trying, the first part is the old way that worked, but not with long strings, the error messages for your suggestions farther down. I used the DAO approach:

'***The below works, but only for smaller strings
'Me.RecordSource = Forms!F_ToolingTypeSelectionForReport!tbxSQL_Strin g
'Me.tbxCriteria.caption = Forms!F_ToolingTypeSelectionForReport!tbxLable_Str ing
'************************************************* **********
Me.tbxCriteria.caption = "TEST"
'MsgBox "The report's record sources is: " & Forms!F_ToolingTypeSelectionForReport!tbxSQL_Strin g

Dim dbs As Database, rst As Recordset, strSQL As String



    Set dbs = CurrentDb()
    strSQL = Forms!F_ToolingTypeSelectionForReport!tbxSQL_Strin g

    Set rst = dbs.OpenRecordset(strSQL)

    If rst.BOF = True Then
            MsgBox "No Detail found!"

    ElseIf rst.BOF = False Then
        'Me.Recordset = rst '**Gives me: Method or data Member Not Found
        Me.RecordSource = rst '**Gives me: Type Mismatch

    End If

    rst.Close
    Set dbs = Nothing

Mitch

You've got two possibilities here:

1. Build and run your query, and turn the results into a list suitable for use as a combo box source. This only works if the resultset of the query is reasonably short.

2. Build your SQL and then temporarily create a saved query based on this.

Let us know which would be best.


Brian Skelton
Braxis Computer Services Ltd.

(Opps, it is not Sal, sorry about that.)
Braxis,

 I think option 2 will work for my needs.

Or maybe build your string in a string Variable. Then use that string variable to open a recordset. Creating a Query at run time can cause problems.



Sal