Adding/Removing and Saving an existing query

[MondayLon676767]

HI. I am in the process of making a database and basically I have everything done but this saving, loading and adding/removing part.



Would it be possible for me to allow end-users to run queries that they could save, load, add to the existing table or remove from the existing table?

When I say Add/Remove what I mean is could a user of the database run a search of every customer in the state and then remove everyone in County X or on the other hand could they run a search of every customer in the state and then add every customer in a neighboring state. Kinda like layers and again I have all of the criteria set up I just need the Save, Load and Add/Remove buttons on the form. I should add that I do not want users to save over the main table I want them to save it as there own text file they can upload later.

[June7]

Yes, but that is very complicated and elaborate code. I have a form that allows users to do some custom filtering of the form records. They can save the constructed filter to a table as a text string then the filter can be re-run at will. If you want to view this, download file from: https://www.box.com/shared/r8nea07sng

Hold down the shift key when opening the frontend, reset links to backend tables, open Login form then from MainMenu form select Manage Samples. This is old version of my db but should give you an idea of what is involved.

[MondayLon676767]

Bummer. So I couldn't just allow people to save there query table as a text file and then they could load the same text file back into the database? Much like how we can import and export text files into Access?

[June7]

That sounds like saving the actual data out to a text or Excel file then importing it back in as a new table. Not something I would allow.

If you just want to save the SQL statement of a query into a table and then allow code to pull up that string and create a query object, yes can be done.

But again, the hard part in any case is constructing the sql string. My db constructs filter criteria string, saves to table and then user can call up that filter and apply to the form.

[MondayLon676767]

Right, I thought about that. I mean it is in my best interest to not allow people to be able to actually export the data and get there hands on it but I have to allow folks to do mail merges so I figure people need to be able to get a copy of it to do that. If I end up needing people to be able to export and import there own text files is there a way to do that? And I do not so much want people to be able to make new tables when they import, only temporary files so they can't actually save it within the Access Database but they could save it as a text file or load it as a text file so they have the last query they worked with since closing it. I do not want people to save tables and cluter the hell out of everything.

Saving the SQL of the query sounds like a good option but sounds really complicated, are there any guides you would recommend? I would probably have to take it real slow.

[June7]

Access and Word can work together for mail merge functionality http://office.microsoft.com/en-us/ac...010285219.aspx

Making the data in text or Excel file available for viewing by users requires either linking or importing. A process could create the table link or import, allow user to work with it and then delete it when the process is completed.

You can review the database I offered in earlier referenced link. Also review
http://allenbrowne.com/ser-28.html
http://allenbrowne.com/ser-50.html
http://allenbrowne.com/ser-62.html

This is all very complicated and requires more than elementary knowledge of VBA.

[MondayLon676767]

OK. Thank you for your advice.

I am not sure if this is kosher question or ask here on not but is there a place I can go to pay someone to do something like that? I mean I am sooooo close to being done and at this point I would really not be above paying someone a little cash to add a save and load button to the form.

[June7]

Some have posted thread in General Chat expressing offer to pay for programming assistance. Don't know that anyone has ever responded.

You could advertise on some job search site, local newspaper, recruit from university, search yellow pages for consultants.

[MondayLon676767]

Ok so the saving and loading is something I will have to read up about a ton. Thanks for the leads.

How about the adding and removing from the query results? I thought about it more and basically what I need is a "Not" button query button, maybe.

As in I run my query from the form like normal and then if a user wants to narrow the results by a criteria they could click a narrow button which would remove people from the existing query results. Maybe it would update the query by adding a "NOT" before the existing criteria with the new criteria the user has listed. Is that how it would be done or am I on the wrong track here again.

[alcapps]

how many fields are you trying to filter?

[alcapps]

usually you can have the form filters at the top of the form. show the records below in a datasheet veiw. then have a button to provide the filtering. Then a button to export it to excel with a basic textbox for the file name for output.

[June7]

Monday, that is more complication would have to deal with in VBA. This is way more than I have tackled. Good luck.

[MondayLon676767]

Hi. I was just able to find a pretty good video on youtube for the saving part, it was 100x more easy then I thought: http://www.youtube.com/watch?v=MAp5J7xwFNo

My form is a lot like what you described. I have some filter controls at the top, a requery button, save button and a subform below that displays the query. I need to add a add and a remove button from the current query based on the filter controls at the top. Then a load button or some way for a users to tell the database where they left off; what they had on the subform last.

For me I realized it is not a big deal to allow users to export the actual subform query just as long as I do not allow them to export the whole table. Now I am just working on the uploading part which will be tougher. I think I can set up a button that would allow a user to upload the exported file back onto a temporary table, every time the form is closed a macro deletes the temp table. Then I would need a macro that would automatically copy what is on the temporary table onto my actual query subform thus leaving the user would be where they left off. Next the add and remove buttons.

[alcapps]

not really sure after that explanation of what you're trying to accomplish.
but it can be as direct as a bunch of filter fields for the table. with a button to run the filter. which builds a where clause to the table you are pulling data from. dynamically changes the rowsource of the subform on the form. Then you requery the subform. Another button on the form calls a docmd. that transfer the query to excel for output. Thats what I had in mind. Temp tables and macro deletes automatic copy to the temp tables add and remove.. not sure about all that.

me.requery with a blank recordsource gives you no records. clicking the filter button would fill the query with the new data to refill the subform.

on filter click event would have something like
dim strSQL as string
dim strWhere as string
'root table data
strSQL = "Select * from tblData "

strWhere = ""
if me.combox1 <> "(All)" then
strWhere = " and field1 = '" & me.combox1 & "' "
end if
'0 means all in this case..
if me.combox2 <> 0 then
strWhere = strWhere & " and field2 = " & me.combox2 & " "
end if
'check to see if you need the where
if strWhere <> "" then
'get rid of the first and
strWhere = "WHERE " & mid(strWhere,5)
end if

strSQL = strSQL & strWhere

'update the record source of the subform
me.YourSubformName.form.recordsource = strSQL
me.YourSubFomrName.requery

so you can use the me.YourSubFomrName.form.Recordsource to send the data to excel as well..

that is really the bulk of the coding..
how to determine what is all for each combo box for each field is usually the most pain of vba coding.
each combo box needs an all added to it.
example.. rowsource for combo 1
select Field1 from tblData group by Field1 Union "(All)" as x from tblData
having the ( in the all allow it go to the top of the list.
if you have ID's or lookups..
make the union support that as well..
Union 0 as ID, "(All)" as x from tblData
in vba strings need single quotes on each side to work on a dynamic query
dates need ##
numbers don't need anything..
I gave you some food for thought..

I'll leave it to you to figure the rest out.. not to hard to do.. just takes some time..

hope this helps

[MondayLon676767]

Thanks! I am going to check this out but honestly I am very new at any coding. I mean it is very tough for me I know you got to start somewhere I am not sure where. You kinda lost me above a bit, are you saying this code above would allow me to requery on top of my existing query in my subform?

As in if someone sets all of the desired criteria controls to where they want and runs a query and then all of sudden they realize they forgot to include CountyX so they reset the criteria for CountyX and then hit this button and it will just add the new queried data ontop of the existing query?

I think I might have found a solution:

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;

I am just struggling on how to link all of this together on my form and have it display the query3 on the subform. I currently have the refresh query button that refreshes my subform. I would need to point the subform to query3 at all times and then have a search button that populates query1 and then an add button that can place another layer of criteria ontop of query1.