Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Click day in ActiveX Calendar in Access to call a report

Posted on 2011-03-21
9
Medium Priority
?
301 Views
Last Modified: 2012-08-13
Hello! I'm trying to set up an OnClick action that will allow users to click on a specific day on an ActiveX calendar added to an Access database. The click would call a report that would show that specific day's activities. OnClick doesn't seem to be working. I can get the report to open whenever I open the Calendar form but not by clicking on individual buttons. So, 1) how can I get a report to open by clicking on an individual day? and 2) what is the proper way to reference the specific day that is clicked on in the calendar when using it in the code? Your assistance is greatly appreciated!
0
Comment
Question by:behest
[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
  • 5
  • 4
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35186741
What version of Access please?

You have to use the AfterUpdate event of the calendar control

Something like this to open a report with the same day as the calendar

DoCmd.OpenReport "rptOrders", acViewPreview, , "OrderDate=" & "#" & Calendar6 & "#"

Works fine for me

;-)

JeffCoachman
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 35186774
In retrospect you may be better off putting the code on a button.

Putting it on the After update event will be frustrating if the user picked the wrong date by accident.
It will also not work if you add more report criteria on your form...

Sample attached

;-)

JeffCoachman
Access-EEQ26902276SynchronizeAcc.mdb
0
 

Author Comment

by:behest
ID: 35212646
Jeff, thank you for the response. I tried using the line of code provided but still have the same issue. The daily report opens but is in no way linked to the square/day clicked on the calendar. I've got basic code that will return the day, month and year values for each day of the calendar that is clicked, so I know the data is in there somewhere. Is there something along the lines of Calendar1.value (or ~) that will allow me to link the specific day clicked?

OR

Looking at this from another perspective, could I link Calendar1.value to the specific date square via the expressions on the report, currently using  =Date() ...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35222049
<I tried using the line of code provided but still have the same issue.>
Which line of code?, the code I posted here, ...or the code from the sample Database I posted?

Remember you must change the names in the code to match the names in your specific database.

<I've got basic code that will return the day, month and year values for each day of the calendar>
Well, the calendar will return a single date.
This is all that is needed to filter the report.

Did you verify this functionality in the sample DB I posted?

JeffCoachman
Access-EEQ26902276SynchronizeAcc.mdb
0
 

Author Comment

by:behest
ID: 35243698
Jeff, I just received an inactive question messge. I'm in the middle of another project but will take a look at this again later this evening. Yes, I did test and replaced appropriate names in the code. More later....
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35246521
ok
0
 

Author Comment

by:behest
ID: 35310702
Hi! Back to this project now and working on setting up a new report that will work with your brilliance! Instead of a sales date, I've got start and end dates, so I need to pull anything within those dates. Hoping I can use something as simple as >= on the report. Will be back with the results.
0
 

Author Comment

by:behest
ID: 35311495
Everything is set up, the world should be a happy place to live in...but no. I'm told that I'm missing an operator. error 3075...syntax error (missing operator) in query expression 'Start date=#3/9/2011#'. It appears to want something between the "Start date=" and "#". I'm in 2007, btw. When I run yours it works without a problem. I don't have anything set up with >=...starting off simple to make sure it works then will grow from there. Any words of wisdom?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35347727
1. I thought you were using the calendar control to select the day?
2. Try to avoid using spaces in your names, ...but if your app is already designed with them, try it like this:
    "[Start Date]=" & "#" & Calendar6 & "#"

...This, again, presumes that you are using a calendar control to select the dates
0

Featured Post

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.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
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: …

609 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