Solved

Event procedure

Posted on 2014-02-17
6
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 38

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 38

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
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!

 

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 38

Expert Comment

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

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

623 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