Solved

Event procedure

Posted on 2014-02-17
6
289 Views
Last Modified: 2014-02-17
Hi,

My query runs fine in access but I want to put it in event procedure code and I am not sure how.



SELECT Count(*) AS [Count], AUD_MOS_IVR_LOG.DATA_3
from AUD_MOS_IVR_LOG
WHERE (((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=#2/14/2014# And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=#2/15/2014#) AND ((AUD_MOS_IVR_LOG.unique_key_id) In (select unique_key_id
    from AUD_MOS_IVR_LOG where
((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=#2/14/2014# And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=#2/15/2014#)
 And AUD_MOS_IVR_LOG.record_type = 'ME' And
AUD_MOS_IVR_LOG.data_1 = '1241_AskLOB_MPSERS_YesNo_DM'
 )) AND ((AUD_MOS_IVR_LOG.record_type)='TE'))
GROUP BY AUD_MOS_IVR_LOG.DATA_3;


the query above I need to put it in event procedure.

Thanks,
Lulu
0
Comment
Question by:lulu50
  • 3
  • 3
6 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39865066
It is a select query.  We don't normally run them from event code since they don't do anything.  What do you want to happen?

You can bind the query to a report.
You can bind the query to a form.
You can add code in a form event to take the count and put it in a control.
You can open the query and view it.  -- DoCmd.OpenQuery "yourqueryname"

Keep in mind that you have hard-coded dates in the query so it will only show records for Feb 14, 2014.  Since everything is hard-coded it is even more curious that you want to automate running it.
0
 

Author Comment

by:lulu50
ID: 39865164
Hi Pat, Thank you for your reply.

oh, I was hoping I can put it in the event procedure so I can prompt the user with date range.

the user should be prompted with a date range from that query.

as you can tell, I am not an access programmer.

can I run some event prompt the user with date range => than run the query based on the user date range => than insert the output into a temp table?
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39865298
You're still in that prompting mode I see.  As some of us told you in your other threads, this is not an optimal solution.  The prompts happen multiple times and you can't alter that.  This is simply annoying to the user plus it gives him the opportunity to make a mistake by entering different dates at each prompt.  With controls on a form, you can validate the data - I posted code for you and ensure you have a valid date range.

Queries and tables are interchangeable for most purposes in Access so you almost never have to make a temp table.  You can export a query to Excel or a text file in the same way you can export a table.

The exact same solution I recommended in your other posts works here.  Change the query to reference controls on a form.  Add a button to the form.  In the click event of the button, run the code to open the query or export it to Excel or a text file.  Do not create a temp table with it.

A sound solution has multiple uses.  Once you get one of them working, you can expand your knowledge base and use the solution in other situations.

For most of my applications I create forms that are intended to run reports and/or exports.  They have listboxes that allow them to select one or more reports/exports and controls that are used to hold various selection criteria.  So, the user can enter a single date range and then choose 3 reports to run.  And with my solution, he won't be prompted up to 12 times to enter the dates.  With yours he will be.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:lulu50
ID: 39865325
Pat,

I said prompt because I am used to the word but, what I meant is to create a form and have a controls on the form for the date of range.  

Sorry, again for saying prompt. I meant something else.  lol

ok,

Thank you for pointing me in the right direction.
0
 

Author Closing Comment

by:lulu50
ID: 39865326
Thank you
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39865440
You're welcome.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Like many organizations, your foray into cloud computing may have started with an ancillary or security service, like email spam and virus protection. For some, the first or second step into the cloud was moving email off-premise. For others, a clou…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now