Adding/Removing and Saving an existing query

[alcapps]

this is NOT The answer..


query1
Code:select name from tblStudents where gender = 'Male';

query2
Code:select name from tblStudents where gender = 'Female';

query3
Code:select name from q1
union
select name from q2;


you will always get all fields from tblStudent.
my code.. see * means all fields.
strSQL = "Select * from tblData "

if you wanted male in this case your combo box for gender would have (All) , male, female
if the combo box was selected to Male then the code would generate

Select * from tblStudent where gender = 'Male'

change the recordsource and requery the sub form.

if you wanted male and female leave the combo to (All) and the code would return

Select * from tblStudent
change the record source and requery the sub from.

thats why I said you would make the fields that you wanted to filter combo boxes to have the All option and individual options.. if you wanted compound options then you use a list box and make it multiselct make them into a in() clauses.

This is not hard it is just time consuming to do.

hope this helps

[MondayLon676767]

Thanks Alcapps. I am not sure that would work for me because I need to be able to basically run to queries from separate buttons from the same table and the same control criteria but have the lumped together in the same subform.

Currently a user can already selected All for sex and get both male and female but I need to give them the option to run there query for just males but then add females after if they wanted.

[alcapps]

Ok good luck.

[MondayLon676767]

Thanks. Sorry if that is what you are trying say the code does. I mean I can code a button like 1/2 the time correct. So for me to understand even the basic functions of the code would be a miracle. Sorry but thanks for the attempted help.

[alcapps]

I guess the concept of copying gender of male and the later replacing the data with gender is all. Not understanding why u can't remove the old (male)records and then copy the all records of(male+female) doesn't work. You get all in a new query for output.
I do hope you figure out what you need to do. This website is here to help you get past your sticking points with examples to help. I just think you have a lot of studying to tackle this project.

[MondayLon676767]

Thanks for the help and tips. I keep trying to think out of the box. Couldn't I have the subform display a blank table and then a search button to append table and then a add button to append it again? Is there a opposite of append like unappend?

[June7]

Simplistic examples of SQL actions executed in VBA code:

CurrentDb.Execute "INSERT INTO tablename(fieldname1, fieldname2, etc) VALUES(value1, value2, etc)"

CurrentDb.Execute "DELETE FROM tablename"

The DELETE can include WHERE clause if selective delete is desired.

[MondayLon676767]

Thanks June I am going to work with it when I get home. I take it I can link this code to a button to remove/add criteria to a table.

[June7]

Yep, button Click event is one.

[alcapps]

I guess my point is you don't need a table. you will only be duplicating data in your database. Just use a query to pull the data into a subform. create a query on the fly and update the record source and requery subform and you have your data refreshed every time.

[June7]

Although I accommodated your question with an answer, I agree with al that 'temp' table should not be needed just to filter records.

[MondayLon676767]

Ugh why do you say so? Will it be kinda janky or just processor heavy? I have looked and I can't seem to find an option for a remove from existing subform query I really wish I could.

[June7]

You don't 'remove', you requery. User reselects filter criteria and then clicks button to apply the new criteria.

Yes, probably processor heavy, especially with large datasets. It's just klunky and doesn't really take advantage of the power of relational database queries.

In addition to the example in my database, here is another poster's effort at creating a search/filter form https://www.accessforums.net/program...tes-31832.html

[MondayLon676767]

As always thank June I am going to look into it.

[MondayLon676767]

I guess my point is you don't need a table. you will only be duplicating data in your database. Just use a query to pull the data into a subform. create a query on the fly and update the record source and requery subform and you have your data refreshed every time.

I do have that or maybe I am misunderstanding what you are saying.

I have a form, with criteria controls at the top below that is a subform which displays a query when a requery button is clicked. I can set the criteria and then click the requery button and the table refreshes to display data that matches the criteria controls and then I can change it and requery display people that match the new criteria. I got that but what I want to do is allow people to run a query with the requery button and then change the criteria and click a different button that would add the new data matches to the already existing query.

Just like when you append a table with a query. Hence why I thought I could have the subform display a table that could be made with one button and then appended by another. The thing is I have spent a lot of time and everything works perfect now besides the lack of add/remove buttons that I described but I do not want to do it the wrong way or a way that is less efficient. The last thing I want to do is finish everything and then have to break it open shortly later because of a problem made now.