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

I put numbers into the inputs of the lotto form and it filters it for me. Im looking to have a button on the form export the results into a new table. Table of results.
Filter-test-02.mdb
bdgbrickAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
...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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bdgbrickAuthor Commented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
bdgbrickAuthor Commented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
bdgbrickAuthor Commented:
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
0
Nick67Commented:
Damn.
So much text, and the site lost it.

Start with this sample.

Nick67
Filter-test-02-v1.mdb
0
bdgbrickAuthor Commented:
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
0
Nick67Commented:
'export to a temporary query'
No such thing, really.

The 'Requery & Save to tempResults' button populates the form AND sends all the results to table tempResults.
You could build reports off queries of that new table.
Each time you click the button, all the data in tempResults gets thrown away and refreshed with your new parameters from the form, so reports based off tempResults would then change accordingly.

You speak of reports, but what you mean by that, I don't know.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bdgbrickAuthor Commented:
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!!
0
bdgbrickAuthor Commented:
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
0
Nick67Commented:
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
0
bdgbrickAuthor Commented:
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?
0
Nick67Commented:
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
0
bdgbrickAuthor Commented:
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?
0
Nick67Commented:
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
0
bdgbrickAuthor Commented:
I'm going to name my first kid Nick, thanks man!!

B
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.