[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-04
19
Medium Priority
?
130 Views
Last Modified: 2014-08-14
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
0
Comment
Question by:bdgbrick
  • 9
  • 5
  • 4
  • +1
19 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40240362
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
 
LVL 85
ID: 40240855
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40241185
...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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:bdgbrick
ID: 40242547
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40242857
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
 

Author Comment

by:bdgbrick
ID: 40247749
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40247811
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
 

Author Comment

by:bdgbrick
ID: 40247931
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40252422
Damn.
So much text, and the site lost it.

Start with this sample.

Nick67
Filter-test-02-v1.mdb
0
 

Author Comment

by:bdgbrick
ID: 40256719
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 40260885
'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
 

Author Comment

by:bdgbrick
ID: 40261141
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
 

Author Comment

by:bdgbrick
ID: 40261294
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40261313
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
 

Author Comment

by:bdgbrick
ID: 40261333
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40261349
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
 

Author Comment

by:bdgbrick
ID: 40261375
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40261453
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
 

Author Comment

by:bdgbrick
ID: 40261460
I'm going to name my first kid Nick, thanks man!!

B
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question