Retrieving Field Data from Dynamic recordsource

[pilmart]

Hi There

I have a form on which I construct a string containing a dynamic query, the string containing the query is then passed to a report, the report recordsource is then set to the query string and the report opened.

My question is how can I access (Sorry 'bout the pun) individual fields and load them into some unbound text boxes, for instance if it were a recordset I could simply wlak through the Fields Collection something like : -

for each field in myfields

box = field.value

next

Any help would be very much appreciated.

 

[pilmart]

...forgot to add using access 2003 on XP box

 

[midmented]

set recordset

If the string is already being passed, then you should be able to use the same string to retrieve the data. Like, if the query string were call strSQl.

Example: Hope you understand!

Private Sub Report_Open(Cancel As Integer)
Dim strSQl As String, rst As Recordset
strSQl = "SELECT tblTest.Something FROM tblTest;"
Set rst = CurrentDb().OpenRecordset(strSQl)
Me.RecordSource = strSQl
Reports!MyReportName!Text0.ControlSource = "Something"
Set rst = Nothing
End Sub

 

[pilmart]

Hmmm, I think you will have to load the text box from the recordset (rst)

Something like Reports![myReport].text0.controlsource = rst.fields(0).value.

This won't work in my case as I'm loading my report recordsource with a dynamic sql statement not loading a recordset as per your example, I need to know how to get at the underlying data in the report so I can load my text boxes with the field - level data,

 

[pilmart]

Nope the columns are fixed

 

[midmented]

I know you can bind a dynamic recordset on a form but I don't think you can in a report. I ran across this somewhere else on the internet. (udf)
They said you had to bind the recordset by its "Name" property.

Hope this helps.

[FONT=monospace]Private qdf As DAO.QueryDef
Private rs As DAO.Recordset
 
Private Sub Report_Open(Cancel As Integer)
    
    Dim strConnectionString As String
    Dim strSQL As String
    
    strConnectionString = "ODBC;DSN=dbname;UID=sa;PWD=password;DATABASE=dbname"
    
    strSQL = "SELECT field1, field2 FROM dbo.udf_which_returns_recordset(udf_arguments)"
    
    Set qdf = CurrentDb.CreateQueryDef("qdftemp")
    
    With qdf
    
        .Connect = strConnectionString
        .SQL = strSQL
        Set rs = .OpenRecordset(dbOpenSnapshot)
        
    End With
    
    Me.RecordSource = rs.Name
    
End Sub
 
Private Sub Report_Close()
 
    On Error Resume Next
    
    CurrentDb.QueryDefs.Delete ("qdftemp")
    
    rs.Close
    qdf.Close
    
    Set rs = Nothing
    Set qdf = Nothing
 
End Sub

[/FONT]