Getting sql string from form to report 3

[Egglar]

Hi, ive got an sql string that works fine. The sql string is generated in an afterupdate even of a list box in VB.

Im having a bit of trouble getting it from the form to the report (string name is Reportsql, and report name is rptRemitance). i think its to do with it being global? But when i try and declare reportsql as global it doesnt let me (perhaps because im in a private sub?).

Ive got a piece of code on a command button to open the report: Call DoCmd.OpenReport("rptRemitance", acViewPreview, , ReportSql) that doesnt work, ive also tried setting the me.recordsource reportsql on the report open its self. The immediate window shows that neither the click sub or report open sub`s are getting the sql string into them?

Am i doing something wrong? I dont really want to move my code into a function in a module id rather keep it in the after update event of the list box.

Also completly differnt question, how can i change the contents of a txt box on one form from a sub form after update event? I keep getting the error message "Object not found" Is it something like me.forms.frmPayment.txtSelectedTotal = "x" ?

Thanks for your replies in advance!

 

[tEkHEd]

I would probably create a local query that returns the results I require, and base the RecordSource of the opening report on that query. You then just need to code the change in the SQL, which will update the query, it all depends on if you are working with pass through queries or not?

if you are not, then your local query would have a criteria for one of the fields like:

[Forms]![frmmFormName]![ctlControl]

If you are using a pass through, then you will need to code the variable change.

Dim stSQL as string, ctl as Control

set ctl = [Forms]![frmMyForm]![ctlControl]

stSQL = "SELECT * " _
    & "FROM tblMyTable " _
    & "WHERE fldField = '" & ctl & "';"

the sql will then be updated with the content of the control.

The other option is to do a QDF loop

Dim dbs as Database, qdf as QueryDef, stSQL as string, ctl as Control

set ctl = [Forms]![frmMyForm]![ctlControl]

stSQL = "SELECT * " _
    & "FROM tblMyTable " _
    & "WHERE fldField = '" & ctl & "';"

set dbs = CurrentDb

    For Each qdf In dbs.QueryDefs
        If qdf.Name = "qryMyQuery" Then
            qdf.SQL = stSQL
        End If
    Next qdf

hope this helps..

 

[redapples]

Dim qdUserQuery As QueryDef
Dim db As Database

set db = CurrentDB
Set qdUserQuery = db.CreateQueryDef("MyQuery", ReportSql)
'DAO

Set cnn = CurrentProject.Connection
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn
Set qdReport = New ADODB.Command
qdReport.CommandText = "Some SQl here;"
cat.Views.Append "MyQuery", qdReport

'ADO

Both of these need some kind of error capture to delete the query if it already exists.

'DAO
If Err = 3012 Then 'MyQuery wasn't delete from previous usage
        db.QueryDefs.Delete "MyQuery" 'deletes the previous version from systable
Resume

'ADO
        If Err = -2147217816
        Dim Cm As New ADODB.Command
        Cm.ActiveConnection = cnn
        Cm.CommandText = "DROP VIEW [myquery]"
        Cm.Execute
        Resume

Then use the MyQuery as the source for your report.

One word of caution, in ADO the query is not displayed in the list of Queries displayed in the Access window. This apparently is on purpose(!?) for Access 2K.

Hope this helps.

Steve.

 

[Egglar]

Thanks! I used the dao version steve gave me - it was the simplest to understand!

Thanks for answering one of my threads again tek head too!

Any ideas on the other question?

how can i change the contents of a txt box on one form from a sub form after update event? I keep getting the error message "Object not found" Is it something like me.forms.frmPayment.txtSelectedTotal = "x" ?

thanks, elliot

 

[Egglar]

Also, is there a property to see if a query exists?

eg

IF qryRemitance exists then
db.QueryDefs.Delete "qryRemitance"
else
Set qdUserQuery = db.CreateQueryDef("qryRemitance", ReportSql)

Thanks elliot.

 

[redapples]

Thanks for the star.
You could look in the System tables MsysObjects
the column would be Name but you can narrow this down to queries only with the Type column which is 5 for queries.

set rs = db.openrecordset "MsysObjects"
rs.findfirst ...

I personally just use the error handler to sort it out for me. In that when you add it, if it exists an error is raised where you delete it.

will have think about the other question for you.

You might want to repost it as a question in its own right though as (although potentially bad nettiquette (spelling?) people might not answer it as they will be looking at the title - referring to SQL).

Steve.