I am working with the Calendar control named ActiveXCtl64 on the Holds
Viewing Active form.
1. Open the Form called Holds Viewing Active in Design view.
2. Make the Calendar updateable. Currently, you have the Control
Source set to equal the current date. This prevents it from changing
when you click to select a date. To fix this:
Click on the Calendar control named ActiveXCtl64.
Right-click on ActiveXCtl64 and select Properties.
On the Property Sheet, select the Data tab.
Clear the value in Control Source so that the Control Source is blank.
3. Add the event procedure that requeries the Subform:
From the View menu, choose Code.
When the Visual Basic window opens, paste the following routine into
the module and Save. Also Save the Form.
' Code Begin
' This procedure will run whenever the Calendar is clicked.
Private Sub ActiveXCtl64_Click()
Dim intDays As Integer
Dim datMonday As Date
' Determine what day of the week was selected.
intDays = DatePart("w", ActiveXCtl64.Value)
' Determine how many days it is from the Monday in
' that week.
intDays = intDays - vbMonday
' Set a date variable equal to the Monday
' of the selected week.
datMonday = ActiveXCtl64.Value - intDays
' Set the Filter for the subform to show only those
' records where First Date equals the Monday of the
' selected week.
Me![Holds Viewing].Form.Filter = "[First Date] = #" & datMonday &
"#"
' Apply the filter to refresh the subform
Me![Holds Viewing].Form.FilterOn = True
End Sub
' Code End
Now, when you click on a date in the Calendar, the subform should
automatically refresh to show the Jobs beginning in that week.
If you want the Calendar to automatically start on today's date, try
this:
' Code Begin
Private Sub Form_Current()
' Select today's date.
ActiveXCtl64.Value = Date
' Run the routine to populate the subform.
ActiveXCtl64_Click
End Sub
' Code End
Additional Resources:
--------------------------------
1. Look in Access Help for the DatePart function and the Filter
Property of a Form.
2. There is an excellent Access FAQ from MVPS. It has a lot of good
tips and information on working with Access, as well as lots of links
to other resources.
http://www.mvps.org/access/
3. Try searching Google Groups for information as well as doing a
regular web search. Many times, someone on the newsgroups will have
answered your question.
Good luck with your Access project!
- Hammer |
Clarification of Answer by
hammer-ga
on
30 Dec 2002 17:48 PST
Note: The line of code that sets the Filter Property should NOT wrap.
It should all be on one line. Also, if you're curious, notice that
this line encloses the date between two pound signs. You need to do
this to pass dates.
Also, you may be interested in this FAQ article from the MVPS Access
FAQ. It is a nice reference for how to refer to subforms and
properties in code.
http://www.mvps.org/access/forms/frm0031.htm
- Hammer
|
Request for Answer Clarification by
cheaptrinkets-ga
on
30 Dec 2002 18:53 PST
This looks awesome. One small problem. I have modified the code to
change the name of the active x to this
Private Sub ActiveXCtl105_Click()
Dim intDays As Integer
Dim datMonday As Date
' Determine what day of the week was selected.
intDays = DatePart("w", ActiveXCtl105.Value)
' Determine how many days it is from the Monday in
' that week.
intDays = intDays - vbMonday
' Set a date variable equal to the Monday
' of the selected week.
datMonday = ActiveXCtl105.Value - intDays
' Set the Filter for the subform to show only those
' records where First Date equals the Monday of the
' selected week.
Me![Holds Viewing].Form.Filter = "[First Date] = #" & datMonday &
"#"
' Apply the filter to refresh the subform
Me![Holds Viewing].Form.FilterOn = True
When I run I get an error that the "Variable is not Defined" and it
goes to the line
intDays = DatePart("w", ActiveXCtl105.Value)
ActiveXCtl105 - this is highlighted.
Thanks for your help.
|
Clarification of Answer by
hammer-ga
on
31 Dec 2002 04:38 PST
I changed the name of the Calendar control to ActiveXCtl105, pasted in
the code you posted, and it worked fine.
Check the code in your module. Make sure that you actually typed the
name correctly. It's easy to mix up an l (ell) with a 1 (one), etc.
At the top of your module, add this line:
Option Explicit
...then recompile by selecting Compile from the Debug menu. This
should immediately highlight any misspelled or undeclared variables.
It is a good idea to add Option Explicit to all your modules. It helps
to catch errors before you are actually running code.
- Hammer
|
Request for Answer Clarification by
cheaptrinkets-ga
on
31 Dec 2002 07:11 PST
I figured out why it is not working. On the activeX calendar control I
am using there is no click event. Is there a different calendar
control I should download? Should I use a different event?
|
Clarification of Answer by
hammer-ga
on
31 Dec 2002 07:41 PST
I think Access is lying to you about this. I ran this using the
database I downloaded from you using the Calendar control you already
have. While the Click event does not appear on the Property sheet, it
does appear in the VBA interface. If you are getting into the event
procedure to have the error, is it definitely trying to run. Can you
post your current database where I can download it? It will be much
easier for me to help you troubleshoot this if I can see your Form and
your code. Are you using a different Calendar than the one you
included with your database?
- Hammer
|
Request for Answer Clarification by
cheaptrinkets-ga
on
31 Dec 2002 07:58 PST
Is there a way you can email me your mdb? what is your email address?
|
Clarification of Answer by
hammer-ga
on
31 Dec 2002 08:17 PST
We are not permitted to exchange personal information, however, I have
placed your database where it can be downloaded:
http://www.hammerdata.com/Google/
Have you tried the test I suggested where you use Option Explicit to
locate bad variables? If so, what was the result?
- Hammer
|
Request for Answer Clarification by
cheaptrinkets-ga
on
31 Dec 2002 09:14 PST
The problem was that I created a module to run this, when I run it the
code section of the form it works fine. This fucking rocks! I am
going to have more access questions and I hope that you consider
answering them.
Quick question though, I want to have more than 1 calendar and set
them a month apart. I am using Date + 31. Some months have 28 and
some have 31 days. Is there a way to 1 month to the date() function?
When I put Date() +31. Problem is this, if it is January 30 and I add
31 days then it is March 1. If I am in December 1 and add only 30 days
then the next calendar will still show December! Is there a way to
add 1 month instead of adding days?
|
Request for Answer Clarification by
cheaptrinkets-ga
on
31 Dec 2002 09:17 PST
Also I wanted to ask if there was a way to change the font of the
person's name if the Booked = -1, so this way I can see which jobs
have actually hired the client. If you want me to post this as a
seperate question that is entirely fine with me.
This form shows Holds which means they are considering using a client
and he is available for other jobs. If it is booked then that means
they are not available. Having the color change would make the form
clearer to see.
Thanks.
|
Clarification of Answer by
hammer-ga
on
31 Dec 2002 10:28 PST
"I am going to have more access questions and I hope that you consider
answering them."
Of course.
"Is there a way to add 1 month instead of adding days?"
The DateAdd function does this. Access Help for DateAdd shows
specifics and examples. You can also use DateSerial to build dates.
"Also I wanted to ask if there was a way to change the font of the
person's name if the Booked = -1"
You can change it, but it will change for all the subforms. Take a
look at this MVPS FAQ article regarding conditional formatting in
subforms. There may be something in the sample database that you can
use.
http://www.mvps.org/access/forms/frm0024.htm
By the way, if you do this, check for Booked = True, rather than
Booked = -1. True may not always be -1.
- Hammer
|
Request for Answer Clarification by
cheaptrinkets-ga
on
31 Dec 2002 11:05 PST
If you could modify the form so that the colors change when it is
marked booked and post it again, I would very much appreciate it. Red
is a good color. Thanks. I am having one of those weeks where I am
lucky to make it to the toilet on a regular basis. I very much
appreicate your assistance.
|
Clarification of Answer by
hammer-ga
on
31 Dec 2002 11:12 PST
Since this is a significant amount of work, and well outside the scope
of your original question, why don't you post this as a separate
question? I will warn you that the only way to do what you ask is a
bit kludgy. The sample database that I pointed you to will illustrate
what I mean. I'll keep an eye out for your new question.
- Hammer
|
Request for Answer Clarification by
cheaptrinkets-ga
on
31 Dec 2002 11:48 PST
Actually I just found something that touches on this.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_conformat.asp
I appreciate your time and help and have a few more questions I am
posting this morning.
|
Clarification of Answer by
hammer-ga
on
31 Dec 2002 11:57 PST
cheaptrinkets,
Conditional formatting is fairly simple. Your problem has to do with
the fact that you want to do this on a subform using continuous forms.
You will find that, when you change the font on one subform, they will
*all* change.
Good luck though, and thanks for the good rating and nice tip!
- Hammer
|
,
0 Comments
)