Thread: VBA - Dinamically use forms
08-20-12, 06:18 #1Registered User
- Join Date
- Aug 2012
Unanswered: VBA - Dinamically use forms
I'm building a form for users to fill with information.
Several fields are dates.
What i'm trying to do is to use the activeX calendar to pick a date.
I'm trying to do this dinamically using 2 global variables, one with the form name that is in focus, and another with the control name where i want to put the date.
I'm having some problems in this. Can anyone help me?
The code i'm using when user choses a date is the following:
Private Sub ChooseDate_Click()
Dim InitialForm As Object
Set InitialForm = NameOfForm 'NameOf Form is the name of the form that invoked the calendar
dateChosen = CalendarCTRL.Value
Forms!NameOfForm!NameOfControl.Value = dateChosen 'NameOfControl is the control name where i want to put the date chosen.
08-20-12, 19:21 #2Registered User
Provided Answers: 6
- Join Date
- May 2005
- Nevada, USA
08-20-12, 21:07 #3Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
I won't go into the reasons why the better choice would be to use a Form-based, non-version dependent calendar, rather than an ActiveX calendar, but just address how to use a single calendar for multiple date fields, on a Form.
A single line of code will do the job:
Private Sub YourCalendarName_AfterUpdate() Screen.PreviousControl = YourCalendarName End Sub
You simply Tab/Click into the Control for the date field in question, then click on the calendar.
Actually, I used to expand on this by simply:
- Setting the Calendar's Visible Property to False, by Default
- Set the Calendar's Visible to True when a Date Field gets Focus
- Assign the Value
- Move Focus away from the calendar, by setting it on another Control
- Setting the Calendar's Visible Property to False, again
So that would be, setting the Default Visible Property to False, in the Properties Pane.
Private Sub YourCalendarName_AfterUpdate() Screen.PreviousControl = YourCalendarName AnyOtherFieldName.SetFocus YourCalendarName.Visible = False End Sub
Private Sub Date1Field_GotFocus() YourCalendarName.Visible = True End Sub
Replacing the various Control names with actual names from your Form.
Linq ;0)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007