Solved

Event procedure

Posted on 2014-02-17
6
294 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

929 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

8 Experts available now in Live!

Get 1:1 Help Now