Get all filtered results over to query

[LFC]

Hi,

I have two questions in regards to filtering a query based on my form. I currently have this "working", but not the way I would like. I have the criteria of a field in my query set to "LIKE Forms!Form!Subform!Field" & *. This works, but only returns the first filtered value from the form. Is there a way to get all the filtered results into the query? The second question which could very well answer the one above is this. My form that I want to filter the query has multiple subforms that are filtered based on a search form that has multiple options to search with. Is there a way to change which fields in the query have criteria and change the content of the criteria?

Thanks

 

[vbaInet]

I suppose your subform is linked to your parent form (via Link Master/Child) so it will only display records related to the ID on the main form that meet the criteria.

 

[LFC]

I actually don't have a link between subform and main form. My main form doesn't have information. I have 4 subforms that all contain different data that I wanted the user to be able to see at the same time. Linking by the ID would work perfect, but that would still go back to my previous question, how would I get more than just the first record of the filtered subform into the query?

Thanks for the response.

 

[vbaInet]

Are you aware of how the wildcharacter function? If say you entered "Brow" as the search word and you have two records, "John Brow" and "Brown Benson" it will only return "Brown Benson". So if your question is you want to search in any part of the string, then you need to include another asterisk before the search criteria, i.e. just after LIKE.

 

[LFC]

Yeah. I'll just try and give an example. Say I have the query filter off the subforms ID and in the filtered subform there are records with ID's 2, 5, 10, 55, 110. How would I go about getting all of those into the query? Currently it will only show the record where ID = 2.

 

[vbaInet]

It is very unusual to want to filter records in the main form by IDs from records in a subform. What's the idea behind this?

 

[LFC]

I want to have the qry doing this so that I can easily output all the filtered subforms to a single excel spreadsheet. I haven't found an easy way to do this yet, so I'm trying this way. If I just do DoCmd.OutputTo etc., from the form it doesn't keep the filter with it. Is there a better way than what I am trying?

 

[vbaInet]

Have you tried Docmd.Transferspreadsheet?

 

[LFC]

I should have said to a single excel workbook with each subform being on a different sheet. I'm doing DoCmd.OutputTo acOutputQuery, "Search_qry", acFormatXLS, strPath. Once I get this working then I'm going to work on the getting them into the same workbook.

 

[LFC]

I was looking at that a while ago, but now I can't remember why I didn't use it. I thought that you couldn't have filters with DoCmd.TransferSpreadsheet. Is that true?

 

[vbaInet]

It's fine with criteria, as long as the subform is open it will work.

 

[LFC]

Alright, I have to go do something, but I'll have a crack at it later today or early tomorrow.

THANKS

 

[vbaInet]

Just let us know how you get on.

Have fun!

 

[LFC]

i've been playing with it a little bit. It won't let me use a subform as an object, it has to be a table or query. Is that right? If so, do I need to use SQL in the table or stick with the query method. If I'm sticking with the query, then I'm still not sure how to get more than just the first record off the filtered subform. What I had was, DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "SearchResults_Warranty_subform", strPath, True. Is what I have correct?

 

[vbaInet]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Name of Query the subform is linked to", strPath, True

 

[LFC]

It works...but it's still the same problem I had originally. If I have 'ID' as my field linked to the query I can only get the first record from the filtered subform. Is there something I can put in criteria to make it go through all the filtered records?

 

[vbaInet]

You mentioned in your second post that your subform is not linked to anything.

If you want all the records, simply create another query that doesn't have any criteria and export that.

 

[LFC]

I'll try to explain this better. I have a search form. A user will input whatever they want to search into textboxes and click a button. When the button is clicked it will open into the form with four subforms and filter based on what was entered on the search form. Then if the export to excel button is clicked I would like to have all filtered data exported. So I still need criteria otherwise I'll just be exporting the over 3,000 records contained in my subform, right? Currently I just get the first filtered record. I can get it to return more than one filtered record, but only if I switch the criteria in the qry to the field that I searched with, which a user will not be able to do. That is why I would like to be able to use the ID field to return all filtered records and not just the first filtered record. The subform is based off a table, not a query. I then have a query that is pulling the data from the subform.

 

[vbaInet]

In that case:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "[B]SELECT * FROM tableName WHERE ID=[/B]" & Me.ID, strPath, True

You can use a Select clause in there too.

 

[LFC]

IT WORKS! I ended up having to do something a little different, but you certainly got me on the right track with the SQL stuff. THANKS