refer to a query in vba to change its sql?

[Ruegen]

Note: If I make the changes using the vba then I close the mainform and reopen it - it requeries correctly. Just not while I have the form loaded.

[June7]

Lost me with post 15.

#13 says qryLibrarySchool has the parameter

#15 shows qryListSchools is name of the query that returns the fields shown in the query in #6

Don't know what you mean by 'they are unticked'.

Still don't understand why you don't use a reference to combobox as dynamic parameter.

[Ruegen]

Lost me with post 15.

#13 says qryLibrarySchool has the parameter

#15 shows qryListSchools is name of the query that returns the fields shown in the query in #6

Don't know what you mean by 'they are unticked'.

Still don't understand why you don't use a reference to combobox as dynamic parameter.

oh crap, sorry June - didn't notice that.

qryListSchools is the saved query.

Then there is the form

I don't even know where qryLibrarySchool came from - my mind must have wandered..

The combo box changes the string which I then make the vba write over the sql in the saved query qryListSchools , which then in turn gets used for the unmatched query which the record source of the form uses to run the continuous form that the combo box happens to be listed on.

I don't want the combo box to filter the current list (the form continuous list) I want it to filter qryListschools.

You see the form, change the show on the unbound combo box and it is supposed to exclude any of the schools on the form that have a booking in qryListSchools.

Also when you make a query using the builder and are in design mode you can check or uncheck a field from view yes? well I have ShowsID set to unchecked as I don't care to show it when viewing the list (I just show schoolsid, showtitle and booking date) - which is what I meant to say I guess.

[June7]

"I don't want the combo box to filter the current list (the form continuous list) I want it to filter qryListschools."

What?

qryListShools is part of the form RecordSource. Doesn't matter how you alter qryListSchools - through QueryDef or dynamic parameter - the form's dataset is impacted. Why not use the simpler method?

[Ruegen]

"I don't want the combo box to filter the current list (the form continuous list) I want it to filter qryListschools."

What?

qryListShools is part of the form RecordSource. Doesn't matter how you alter qryListSchools - through QueryDef or dynamic parameter - the form's dataset is impacted. Why not use the simpler method?

I would happily use a simpler method

I began with this:

https://www.accessforums.net/program...her-39807.html

[Ruegen]

"I don't want the combo box to filter the current list (the form continuous list) I want it to filter qryListschools."

What?

qryListShools is part of the form RecordSource. Doesn't matter how you alter qryListSchools - through QueryDef or dynamic parameter - the form's dataset is impacted. Why not use the simpler method?

The qryListSchools makes a list of schools that have that particular booking (as there are many different types). With that list of schools, using an unmatched query I remove them from the forms list of schools. There is no view of that list on the form that shows the bookings - simply the schools. If I show the bookings then I get duplicates because one school may have more than one booking and of different shows.

[June7]

Sorry, I am totally lost.

Post 15 shows a query that is form RecordSource. That query includes qryListSchools. You modify qryListSchools through QueryDef code, therefore the form RecordSource is altered.

[Ruegen]

Sorry, I am totally lost.

Post 15 shows a query that is form RecordSource. That query includes qryListSchools. You modify qryListSchools through QueryDef code, therefore the form RecordSource is altered.

Yeah you would think so - but it doesn't change... I tried requery but that also doesn't change.

queryDef does alter qryListSchools - but the form does not update even after requery.

I have opened up qryListSchools to see the changes made.

If I close and open the form - it has the proper results - but I would prefer not having to do this.

[June7]

Have you even tried the dynamic parameter with Requery code? If that doesn't work then I have no idea what else to use.

[Ruegen]

Have you even tried the dynamic parameter with Requery code? If that doesn't work then I have no idea what else to use.

I believe my understanding of what you are asking is completely wrong.

What do you mean by dynamic parameter just to clarify.

I've added a picture of the form I am building if that makes it easier in any way...



update - that should say qrySchoolsList not tblSchoolsList! sorry

[June7]

From post 6:

How would I replace 61 with the value of the combo box txtSHOW?

My reply in post 7 was:

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 my suggestion is instead of code using QueryDef (which seems does not work satisfactorily), use a reference to combobox in the saved query object.

[Ruegen]

Yeah that is what I thought was my understanding.

From post 6:

So my suggestion is instead of code using QueryDef (which seems does not work satisfactorily), use a reference to combobox in the saved query object.

how would you reference it? I can't add that field to the recordsource else it gives me duplicates. Can I still ask it to change the number i.e. 61 in that qrySchoolsList (as seen in Post #15?)

[June7]

I really don't understand why there is an issue.

Do you have an Access query object with the sql structure shown in post 6?

Does that query properly retrieve records?

Do you want to replace static 61 with reference to combobox? (I am assuming it is an unbound combobox used to enter filter parameter.)


Fields used to filter a query do not have to be 'ticked' - they do not have to show.

[Ruegen]

I really don't understand why there is an issue.

Do you have an Access query object with the sql structure shown in post 6?

Does that query properly retrieve records?

Do you want to replace static 61 with reference to combobox? (I am assuming it is an unbound combobox used to enter filter parameter.)


Fields used to filter a query do not have to be 'ticked' - they do not have to show.

in post 6 - that sql structure is the same structure as qrySchoolsList (it baisically replaces qrySchoolsList when the vba is finished)

It displays all the bookings with showsid 61 (or other) correctly (this is qrySchoolsList/VBA string)

Yes unbound control

Think of it that I started off with a continuous forms that just listed all the schools, then to remove some schools I added qrySchoolsList to it to filter out those few schools with 61

[June7]

So your answers are:

There is an Access query object with that SQL statement.

You want code to replace the sql of the query object to include a parameter from combobox.


My response:

Do not use VBA.

Use a reference to combobox in the Access query object.

If the dynamic parameter in query object does not work then there is something else seriously wonky about the query and no amount of VBA will help it anyway.