refer to a query in vba to change its sql?

[Ruegen]

Can you refer to a query in vba to change its sql?

I want to change the criteria of the query after a forms combo box updates

I was hoping to refer to a saved query so

qryListOfSchools.sql = mySQL

any tips?

[pbaldy]

You would use a DAO QueryDef.

[Ruegen]

You would use a DAO QueryDef.

I've been reading into it. My understanding is that you:

1. access dao
2. make a recordset?
3. use define a query using querydef
4. save the querydef (replace the one I have saved)

is this correct? I'm shooting blind here I think...

To create a querydef

1. Construct a CDaoQueryDef object.
2. Call the querydef object's Create member function.In the Create call, pass a user-defined name for the querydef and a string that contains the SQL statement on which the querydef is based. You must define the SQL string for a recordset, and write the SQL string for a querydef yourself. (You usually use class CDaoQueryDef directly rather than deriving your own querydef classes from it.)
   
3. Save the querydef object in the database by calling its Append member function, unless you want to work with a temporary (unsaved) querydef. (See Using a Temporary Querydef.)
4. Either create a recordset based on the querydef or call the querydef object's Execute member function.

Close the querydef when you finish with it. Call its Close member function. For more information, see the detailed instructions under CDaoQueryDef::Create in the MFC Reference.
Querydef objects have several properties you can set — primarily for querydefs to be used with ODBC data sources.

copied from the web

I read it as making a query def then saving it - using Append member function?

[pbaldy]

That sounds like creating one. To modify an existing query's SQL, using your example:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryListOfSchools")

qdf.SQL = mySQL

qdf.Close
Set qdf = Nothing

[Ruegen]

That sounds like creating one. To modify an existing query's SQL, using your example:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryListOfSchools")

qdf.SQL = mySQL

qdf.Close
Set qdf = Nothing

Yup that was pretty much what I was thinking (only I didn't consider creating qdf, I thought that db.queryDefs would have been enough but clearly I have to learn more).

[Ruegen]

I have

Dim showchosenid As String
Dim mysqlreplace As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef


showchosenid = Me.txtSHOW


mysqlreplace = "SELECT tblSchools.NewSchoolsID, tblBookings.BookingDate, tblShows.ShowTitle " & _
"FROM tblTourOrganiser RIGHT JOIN (((tblAreas INNER JOIN tblSchools ON tblAreas.AreasID = tblSchools.AreaID) INNER JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) INNER JOIN (tblShows INNER JOIN (tblBookings INNER JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID) ON tblTourOrganiser.TourOrganiserID = tblAreas.TourOrganiserID " & _
"WHERE (((tblSchools.NewSchoolsID)<>9389) AND ((tblBookings.BookingDate)>Date()) AND ((tblShows.ShowsID)=61) AND ((tblBookings.ShowsID)<>5) AND ((tblTeacher.JoiningDateRemoved) Is Null));"




Set db = CurrentDb
Set qdf = db.QueryDefs("qryListSchools")


qdf.SQL = mysqlreplace


qdf.Close
Set qdf = Nothing
Me.Requery

At first I swapped 61 for showchosenid however when I opened the form or query of course it asks for the parameter showchosenid. How would I replace 61 with the value of the combo box txtSHOW?

[June7]

Concatenation of variable.

"WHERE (((tblSchools.NewSchoolsID)<>9389) AND ((tblBookings.BookingDate)>Date()) AND ((tblShows.ShowsID)=" & Me.txtShow & ") AND ((tblBookings.ShowsID)<>5) AND ((tblTeacher.JoiningDateRemoved) Is Null));"

[Ruegen]

Concatenation of variable.

"WHERE (((tblSchools.NewSchoolsID)<>9389) AND ((tblBookings.BookingDate)>Date()) AND ((tblShows.ShowsID)=" & Me.txtShow & ") AND ((tblBookings.ShowsID)<>5) AND ((tblTeacher.JoiningDateRemoved) Is Null));"

So if I concatenation it will save a number rather than a dim name?

[Ruegen]

This does update the first query however when I requery the form that I am on it doesn't update the record source to include the updated qry unless I close the form and reopen...

[June7]

Why do you need to modify query anyway? Why not just build it with a parameter that references the combobox to begin with?

I never use dynamic parameterized queries. I use VBA to build filter string and set the form Filter property.

[Ruegen]

Why do you need to modify query anyway? Why not just build it with a parameter that references the combobox to begin with?

I never use dynamic parameterized queries. I use VBA to build filter string and set the form Filter property.

It's updating and replacing a query in the database that is to form an unmatched query.

So

The first query gets updated (qryListSchools)

then the form record source is an unmatched query that filters out the records that are in qryListSchools.

I am updating the first query on that mainform then updating the mainform recordsource in doing so.

but if you are saying I can change the mainform to filter the unmatched qry without having to replace or at least change the criteria in the first query - that would be possible too I guess?

[June7]

Which is the query in the code? Which query has the static 61 parameter that needs to be replaced with dynamic reference?

[Ruegen]

qryLibrarySchool is the query with the parameter

I have a select unmatched query on the form (I don't have it pointing to a saved query).

[June7]

Sorry, the query name doesn't really help. I was just wondering which is the 'first' or 'second' query, which has the parameter.

But the form uses saved queries in its SQL statement?

Still not quite following why you are resorting to QueryDefs instead of a parameterized query if all the code does is change the parameter. If it doesn't change query structure (tables, joins, fields), seems the code is overkill.

[Ruegen]

oh the first has the parameter.

I then replace the first with the sql string

I believe it could be overkill

I don't have the fields that are in the first query as they are unticked - seen here (this is the form record source)