Calendar Control

[angie]

Hi I have updated to access 2013 and before I had a Calendar Control that filter a form that will no longer work.


I used the following VB code on the load event for the form that was controlled by the calendar.
Private Sub Form_Load()
Me.ctlCalendar.Value = Now()
Me.cboServicecall = "ALL"
Me.cboServiceCallTechnician = "ALL"
Me.chkShowAllDates = False
FilterForm
End Sub
Sub FilterForm()

Dim varCriteria As Variant

Select Case Me.chkShowAllDates
Case True
If Me.cboServicecall = "ALL" And Me.cboServiceCallTechnician = "ALL" Then
Me.FilterOn = False
ElseIf Me.cboServicecall = "ALL" And Me.cboServiceCallTechnician <> "ALL" Then
Me.Filter = "[Service Call Technician] = '" & Me.cboServiceCallTechnician & "'"
Me.FilterOn = True
ElseIf Me.cboServicecall <> "ALL" Then
Me.Filter = "[Service Call] = '" & Me.cboServicecall & "'"
Me.FilterOn = True
Else
Me.Filter = "[Service Call Technician] = '" & Me.cboServiceCallTechnician & "' and '" & "[Service Call] = " & Me.cboServicecall & "'"
Me.FilterOn = True
End If
Case False
If Me.cboServicecall = "ALL" And Me.cboServiceCallTechnician = "ALL" Then
Me.Filter = "[Service call Date] = #" & Me.ctlCalendar & "#"
Me.FilterOn = True
ElseIf Me.cboServicecall = "ALL" And Me.cboServiceCallTechnician <> "ALL" Then
Me.Filter = "[Service Call Technician] = '" & Me.cboServiceCallTechnician & "' and [Service call Date] = #" & Me.ctlCalendar & "#"
Me.FilterOn = True
ElseIf Me.cboServicecall <> "ALL" And Me.cboServiceCallTechnician = "ALL" Then
Me.Filter = "[Service Call] = '" & Me.cboServicecall & "' and [Service call Date] = #" & Me.ctlCalendar & "#"
Me.FilterOn = True
Else
Me.Filter = "[Service Call Technician] = '" & Me.cboServiceCallTechnician & "' and '" & "[Service Call] = " & Me.cboServicecall & "' and [Service call Date] = #" & Me.ctlCalendar & "#"
Me.FilterOn = True
End If
End Select

End Sub

When I would click on the date it would filter the form and show only the service calls for that date. I am now trying to use the new Date Time Picker that access 2013 is using. But I am not sure if I can do this. Any Ideals on how to get this to work with my form. Thanks Angie

[ItsMe]

The way I understand it is the Calendar Control is an ActiveX (OLE) object. So it is an Add-In or Plug-In. In order to use third party Add-Ins you need to be able to reference the library(s) offered by the third party vendor. Similarly, with the Calendar Control, you need the supporting library referenced. Microsoft did this for you in previous versions of Access. I do not believe this is an option on newer versions.

Id do not see much difference, if any, in using an unbound textbox formatted as date and date picker enabled. The other option would be to use a series of combos for Month/Date/Year.

[angie]

I forgot to mention that the form is based on a query if that helps. I just need the new datepicker to filter the form by the date selected. It would be the service call date that would be filtered. There would be a number of records that would have the same date and when I select the date in the datepicker it would filter the form so only the customer with that date of service would show. I hope this might help. Thanks Angie

[June7]

I also don't see why the code shouldn't work with reference to textbox. Have you tried?

[angie]

I created a unbound text box as a date picker. Name it ctlcalendar ( just thinking it would be easier) but when I click on the date it does not filter the form. Also I have it formatted mm/dd/yyyy but it is showing the time also

[ItsMe]

Maybe you can tell us how you are calling FilterForm. What event do you have firing this code? One thing with the date picker is that you need to set focus elsewhere in order for the AfterUpdate event to fire.

[angie]

The event is on the main form on load, the event is above it is used to filter the form by selecting the date

[angie]

ANGIES TO SEND.zip

I am attaching a copy of part of my db so you can see. If you click on the schedule button and then check the all check box it should show you all service calls below in the form. I will be using access 2013 and it no longer surports the mscal. for the calendar control so I have to change to the date picker. I have installed the mscal on my machine and registered it so I can use the calendar control but I would have to do this to all of our computers in our office and will have to fix this problem sooner or later. I don't think Microsoft will add the control back in anytime soon. The event is on load for the main form that is linked to the ctlcalendar. hope this helps Thanks Angie

[June7]

The db you posted still has calendar control and code still references it, therefore am getting errors. I thought you were revising to eliminate the calendar control? Will be happy to help with debugging revised code but you haven't done it yet.

[angie]

angies db.zip

Sorry about that , I think I have fixed it so you can look at. Thanks

[June7]

The [Todays Service Calls] form is saved with criteria in the Filter property. Do you want that?


That date picker is including the current time. I don't see any way to format it not to do that. I have never used data picker control. Try applying Format to the value in code:

Format(Me.ctlCalendar,"mm/dd/yyyy")


BTW, advise no spaces or special character/punctuation (underscore is exception) in naming convention.

[angie]

Hi June7. I want to be able to choose a date in the data picker and than filter the form using the command buttons on the form. The vb code that posted in the first post is how it use to filter on load on the form. I thought I could make a text box and name it ctlcalendar and it would work the same but not that easy.

[June7]

You didn't provide a textbox in the form arrangement, just the calendar control dropdown. I debugged what was provided.

I've never used calendar control. A date picker is available with textbox. I assume it's still in 2013. Textbox with input mask is what I use and it does work. My users have been happy with it. And for those who like to mouse click a date from a calendar, the dropdown calendar is still available.

When I first open db and first time opening Todays form, I see a popup flash so fast can't read it.

[angie]

Hi June7 I guess I don't understand your last post. were able to get it to work? do I need to do something different?

[June7]

I made a suggestion in post 11 - if you want to stay with the calendar control.

If you don't want the calendar control then delete it and use a textbox or combobox.