I need to export form data into a table after a module has been ran

It would be much easier to display the results in a query, ...than to keep having to create new tables...

Can you post the explicit steps to display the results?

I agree with Jeff - what's the reason for keeping filtered results in a separate table? You can always recreate that filtered dataset by issuing exactly the same filters against the original dataset.

There could perhaps be times when you need to store historical or "point in time" data, but those are somewhat rare. If you could tell us what your goal is, we might be able to advise a better way.

...also note that if you saved the results in a table, ...and then if the source data changed, ...the "Tab;e" would not contain the most up to date data.

As far as I can see, ...if you simply run the query: qryFilterFinal, ...you will see the results...

In other words we need to know why you need the results "stored" in a table?


In any event, a brute force solution would be to create a Make Table query from qryFilterFinal:

SELECT qryFilterFinal.* INTO tblResults
FROM qryFilterFinal;

...name this query: qmtFilterFinal

Then run code like this on a button n our form:
    DoCmd.OpenQuery "qmtFilterFinal"

JeffCoachman

All I am looking to do is get the results of the filter to run reports. It seems I'm missing something when I try to build a report from qryFilterFinal. And if possible I would like my reports to change if I change the #s in the forms.
Does that make any sense.

All I am looking to do is get the results of the filter to run reports.

Then let that be your question...
When you say: "I need to export form data into a table"
...then that forces us to only consider that line of reasoning.
Thus, denying you other solutions that may be more efficient.
Make sense?
;-)

Also note that, (as far as I can tell), your export code can be replaced with this simple equivalent:
DoCmd.OutputTo acOutputQuery, "qryFilterFinal", acFormatXLS, "C:\YourFolder\FilterFinal" & Format(Now(), "yyyy_mm_dd hh_nn_ss") & ".xls", True

See the attached, modified sample sample.
;-)

JeffCoachman
Filter-test-02-1-.mdb

I love it,

2 things, my match function/button seems to not be working on yours.

and is there any way to export the query to excel if I wanted to.

the export I have now is exporting the form with all the formatting, which doesn't work with several record, excel crashes around 60,000 records.

Thanks
B

my match function/button seems to not be working on yours.

It looked ok to me, ...you will have to provide a specific example...

is there any way to export the query to excel if I wanted to.

I posed that above:
DoCmd.OutputTo acOutputQuery, "qryFilterFinal", acFormatXLS, "C:\YourFolder\FilterFinal" & Format(Now(), "yyyy_mm_dd hh_nn_ss") & ".xls", True

I have now is exporting the form with all the formatting,

Do you need the formatting?,.  or is getting the correct output the most important thing?
If the data you get is correct, ...you can always apply the formatting later.
Or run Excel code to insert the data into a pre-formatted template (another topic entirely)

excel crashes around 60,000 records.

Not sure, ...but if you are still using the old Excel format, ..then yes, ...the limit might be around 60,000

JeffCoachman

no, I do not care about formatting at all, the data is the most important.

specific example for the match function:
in the example database I posted originally if i put the number 5 in the match input, I would only see the matches for records that matched 5 numbers. when I enter a number in your example its not filtering the lower parameter matches.

I want to put numbers in and run reports based on my inputs, that is basically all.

I also would like to export to excel to run statistics sometimes on what was ever filtered into the query.

Im sorry, sometimes its hard to express what Im actually trying to do in typing communication.



your export function gives me this error:
---see image

Im just not used to access or VBA at all. My background is in back end PHP Mysql web programming.  I just don't know the VBA language.


Thank you so much
B
error-01.png

Damn.
So much text, and the site lost it.

Start with this sample.

Nick67
Filter-test-02-v1.mdb

I love it,

In reading the other peoples answers, Nick, is it better to export to a temporary query?

Let me give you a senerio:

I update the lotto table with the latest results,
Put in my number parameters,
Click a button [Send to Report Temp Query]

When I close the query the result disappear, but while its open I can open several reports based of the query itself.
Then if I change the parameters in the form, it would update the query thus updating the reports.

Maybe like  a reporting station on the form.

Follow?

Thanks
B

Nope that exactly what I needed. I just meant I'm going build the reports with the data I want to see and make a button the form to open it. I think I handle that one.

You are the best Nick!!

One more thing, and if it's a pain forget it.

Let say I have a query based off tempResults table. I would select all and sort draw# by descending.
Is there a way to add a field in the query that would subtract the line below from the line above. Giving me the ticket span between the 2 records. Obviously if I run the query with all the draws it will be 1, but if I run it with say just 5 matches it would change. Sort in descending order would be key.

It's not teleportation so it must be possible!  :-)

Thanks
B

Comparing rows within a query is doable but very, very difficult.  Comparing rows when they are on a form or report is child's play.   Given that we are doing a boatload of work in code already, I could extend the existing form to do the work

Yeah Nick, my end result is to show it in the report, so in a report I would do that in the data expression builder?

I'd do it with code.  On any given record, you have the present draw date, and the present ID.  You fire up a recordset identical to that of the report.  You find the record with the present ID.  You back up one record and DateDiff the record's date with the present date on the report

is there a way to dateDiff the previous DESC record above it? Instead of present date.

if I have a record set of all the 9 number matches, what im looking for is how many tickets apart from the last one.

if there are 17 records, in decending draw# order.  I want to know how many tickets apart the 1st record form the second, the second from the 3rd, the 3rd from the 4th...etc...

That seems like it would be a lot harder.

Want me to post this in a new question?

Ok,

This sample has a report running off the tempResults table.
I messed with the dates of 4 records in that table.
View the last page of the report to see the result.

Will that do 'er?
Filter-test-02-v2.mdb

I'm going to name my first kid Nick, thanks man!!

B