Solved

Event procedure

Posted on 2014-02-17
6
303 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 36

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 36

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
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 

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 36

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article, I'll explain how to setup a Plex Media Server (https://plex.tv/) on a Redhat (Centos) 7 based NAS with screenshots to help those looking for assistance.  What is Plex? If you aren't familiar with Plex, it’s a DLNA media serv…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
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…

679 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