I have just installed 2010, mainly because we have gone that way at work.
I would like to know if there is a way, via VBA, I can turn off the Property ShowDatePicker on all my forms without having to open every form that has a date control and them manually change the property setting to Never.
I created a dummy form in 2010 to test. I find that all textboxes, controlType 109 have a ShowDatePicker property.
My testing shows
ShowDatePicker 0 means Never
ShowDatePicker 1 means ForDates
I wrote a small proc to check the controls on the form. My form (frmAncestors) has
Name
BirthDate
DeathDate
ID
with default labels for each.
In design mode I set the Birth and Death dates to ShowDatePicker ForDates
I ran my proc to see the numeric values. I then ran the proc and changed the
prp.Values where they were not = 0, using
Code:
90 If prp.Value <> 0 Then
100 prp.Value = Never
110 End If
When I check the ShowDialogPicker of the textboxes, they all show Never.
Here is the code:
Code:
'---------------------------------------------------------------------------------------
' Procedure : CheckForDatePicker
' Author : Jack
' Date : 02/02/2013
' Purpose : To determine the property and values that equate to
' ShowDatePicker Never | ForDates
'
' Using form frmAncestor with Name, BirthDate, DeathDate and ID fields (bound to table)
'
' I determined that all textboxes have a ShowDatePicker Property
' Never = 0 | ForDates = 1
'
'---------------------------------------------------------------------------------------
'
Sub CheckForDatePicker()
Dim ctl As Control
Dim prp As Property
Dim objActiveForm As Form
Const Never = 0, ForDates = 1
On Error GoTo CheckForDatePicker_Error
10 On Error Resume Next
20 DoCmd.OpenForm "frmAncestor", acDesign
30 Set objActiveForm = Application.Screen.ActiveForm
40 For Each ctl In objActiveForm.Controls
50 If ctl.ControlType = 109 Then 'TextBox is controltype 109
60 For Each prp In ctl.Properties
70 If prp.name = "ShowDatePicker" Then
80 Debug.Print Now & " " & ctl.name & " " & ctl.ControlType & " " & prp.name & " " & prp.Value
'
' *** Uncomment this If to Set all textbox ShowDatePicker to "Never" ***
90 ' If prp.Value <> 0 Then
100 ' prp.Value = Never
110 ' End If
120 End If
130 Next prp
140 End If
150 Next ctl
On Error GoTo 0
Exit Sub
CheckForDatePicker_Error:
MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure CheckForDatePicker of Module AWF_Related"
End Sub
I have continued to modify your original code and I have tested it and it works as I wanted to work.
1 - I created a module titled modCheckForDatePicker then modified the code.
Code:
Function CheckForDatePicker(pF As Form)
On Error Resume Next
Dim ctl As Control
Dim prp As Property
Const Never = 0, ForDates = 1
For Each ctl In pF.Controls
If ctl.ControlType = 109 Then 'TextBox is controltype 109
For Each prp In ctl.Properties
If prp.name = "ShowDatePicker" Then
If prp.Value <> 0 Then
prp.Value = Never
End If
End If
Next prp
End If
Next ctl
End Function
2 - I then in my forms OnLoad code I have this CheckForDatePicker Me
3 - Then when the form loads it changes the ShowDatePicker to Never
Hope this helps
Please feel free to claim this as yours, as it was you that provided the intitial code.
After a little trial an error, I would recommend a single run to change the ShowDatePicker property value of all the textboxes on all forms. I wouldn't mess with the Load events etc.
However, here is a method with Load Event.
I used the Form Load event, but when I intercepted and asked to Open the form in design view, Access complained -- I couldn't do that operation at this time. So I figured the user wants to Open the Form, so if he starts the Open, and I close the Form, then immediately open if in design mode, make my changes to ShowDatePicker property for every text box. then close the form, then Reopen the Form Normal.
I was able to use the form's Load event, but I had to
a) use a global variable to prevent redoing(looping the changes) and
b) had to include the global variable in the Load EventS
Private Sub Form_Load()
If Glbl = True Then
Call SetShowDatePickerToNever(Me.name)
End If
End Sub
I mocked up a proc to open 2 forms, altered the Load Events
Code:
Sub testSetShowDatePickerToNever()
DoCmd.SetWarnings False
Glbl = True
DoCmd.OpenForm "frmAncestor"
Glbl = True
DoCmd.OpenForm "Customers"
End Sub
and ran this code
Code:
'---------------------------------------------------------------------------------------
' Procedure : SetShowDatePickerToNever
' Author : Jack
' Date : 02/02/2013
' Purpose : Function to use a Form's name and the Load event to
' iterate all text boxes and change the ShowDatePicker property.
'
'
' Needs a Global Parameter
' Global Glbl As Boolean
'
'---------------------------------------------------------------------------------------
'
Sub SetShowDatePickerToNever(frmName As String)
Dim ctl As Control
Dim prp As Property
Dim objActiveForm As Form
Const Never = 0, ForDates = 1
10 On Error GoTo SetShowDatePickerToNever_Error
20 DoCmd.Close acForm, frmName ' close the user's open event
30 Glbl = False 'set to False to bypass Loop in Form.Open event
40 Debug.Print " Glbl " & Glbl
50 DoCmd.OpenForm frmName, acDesign 'open for design and change
60 Set objActiveForm = Application.Screen.ActiveForm
70 For Each ctl In objActiveForm.Controls
80 If ctl.ControlType = 109 Then 'TextBox is controltype 109
90 For Each prp In ctl.Properties
100 If prp.name = "ShowDatePicker" Then
110 Debug.Print Now & " " & ctl.name & " " & ctl.ControlType & " " & prp.name & " " & prp.Value
'
' *** Uncomment this If to Set all textbox ShowDatePicker to "Never" ***
120 If prp.Value <> 0 Then
130 prp.Value = Never
Debug.Print Now & " " & ctl.name & " " & ctl.ControlType & " " & prp.name & " " & prp.Value
140 End If
150 End If
160 Next prp
170 End If
180 Next ctl
190 DoCmd.Close acForm, frmName 'close my reset the ShowDatePicker activity
200 DoCmd.OpenForm frmName, acNormal 'make it look like the users Open Form (now showdatpicker is set to never)
210 On Error GoTo 0
220 Exit Sub
230 On Error GoTo 0
240 Exit Sub
SetShowDatePickerToNever_Error:
250 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure SetShowDatePickerToNever of Module AWF_Related"
End Sub
