Printing Reports from a Form filtered by changing the RecordSource

[bboycvd]

Hi all,

I am fairly new to Access and have gotten a lot of help just by reading through various forums, so I figured I might try my hand at posting since I'm having trouble finding an answer to my specific problem.

I have created a form in an Access 2000 DB with a button that filters data in two ways. If the user has selected a field that is on the form, it just applies a normal filter; if the user has selected a field that is in a subform, it runs some code that changes the recordsource of the form to a SQL string, then stores that string as a variable. All this works properly (I think, anyway, it's returning the right results on the form).

Now, I have a button on the form to generate a report. I have written it so it generates a new query each time it is pressed, and that query is the recordsource of the report. The problem is that while it works for items filtered normally from the main form, items that have been filtered via subform with the recordsource change generate a blank report. 1 page, with all the labels, but no information.

Here is the code for the report button, m_sSQL is the recordsource change variable, m_strSQL is the normal form filter variable:

Dim stDocName As String
    Dim qryDef As QueryDef
    Set qryDef = New QueryDef
    stDocName = "TechnicalReport"
     
        CurrentDb.QueryDefs.Delete "TestingQuery"
     
        qryDef.Name = "TestingQuery"
        qryDef.SQL = m_sSQL
        CurrentDb.QueryDefs.Append qryDef
        qryDef.Close
     
        DoCmd.OpenReport stDocName, acPreview, , m_strSQL
        
        Set qryDef = New QueryDef
        
        CurrentDb.QueryDefs.Delete "TestingQuery"
     
        qryDef.Name = "TestingQuery"
        qryDef.SQL = "Select * FROM Applications"
        CurrentDb.QueryDefs.Append qryDef
        qryDef.Close

I apologize if any of this is unclear or if my terminology is wrong, please let me know and I will do my best to clarify if necessary. Any help anyone can offer would be appreciated immensely.

 

[ssecca]

This is untested but how about something like this...

Create a global variable

Global gbl_sSQL as string

On the form button that calls the report use the following...

Private Sub Command4_Click()
gbl_sSQl = Me.RecordSource

DoCmd.OpenReport "report1", acViewPreview
End Sub
'***********END CODE*******

On the report open event use the following code...

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = gbl_sSQl
End Sub
'***********END CODE*******

That should do the trick.

Goos Luck