[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

DTPicker Issue on Access Form

I am attempting to use DTPicker to update a Access form.  I have a SQL statement to refresh my report after making a new selection from the dropdown calendar.  My problem is that I can seem to get the form to reconize that I selected a new date. Adding my code to On Updated doesnot seem to work.  Lost Focus works, but it is not the solution I'm looking for.  How do I reconize that the DTPicker date has changed?

Ideas?
0
RobertStamm
Asked:
RobertStamm
  • 3
1 Solution
 
PlamodoCommented:
This probably isn't what you want to hear, but I'd scrap the DT Picker and use the calendar control.  If you place the calendar control on a form, you can make it look like a pop up calendar similar to the DT Picker.  After that, its very easy to set and extract data from.

Why go to this trouble?  Because it saves tons of headaches in the present and future.  I was pulling my hair out dealing with issues like the one you are grappling with, and there are many more.  Plus, the DT Picker often wasn't recognized on other people's computers, making it more troublesome.

If you go the route of the calendar control, I'll give you some code syntax to help you get going...
0
 
RobertStammAuthor Commented:
Thanks for your input.  This was the first time I attempted to use the DT Picker.  I'll take any suggestion you have for the calendar control.

Thanks.
0
 
PlamodoCommented:
No worries.  Before you go forward, I'm not saying the DT Picker is unusable.  I'm sure there are many who use it and like it.  I just don't happen to be one of them.  This process might look a bit lengthy, but its not as bad as it looks.  After you do this once, connecting all your other 'date' textboxes to the calendar control form is easy.

1.  I created a new form, place a calendar control on it, and some buttons "Select", "Clear" and "Cancel"
2.  On the form where the DT Picker was, place a textbox and small button next to it.  In the button's properties, under Format, select Picture and "..." and you can scroll down to 'calendar'.  You may need to resize your button after the picture has been selected.  Set the textbox's locked property to true
3.  In a module, I created a variable 'calledFrom'
4.  In the click event of the calendar button:

calledFrom = "TextBoxName"   'use whatever string you want here to identify the location of the textbox
docmd.openform ("Calendar")  'use the name of the form the calendar control sits on

5.  In the calendar form's module (at the top, below option explicit) :

Dim aControl As Control

6.  In the load event of the calendar form (small extract from my code - you'll have to use your own form names, control names and string values for 'calledFrom')  My example here shows two different places that use this form :

Private Sub Form_Load()
   
    If calledFrom = "Agreement-Expiry" Then
        Set aControl = Forms("Agreements").Controls("txtExpiryDate")
    ElseIf calledFrom = "Agreement-Potential" Then
        Set aControl = Forms("Agreements").Controls("txtPotentialDate")
    Else
        MsgBox "The calendar is not properly referenced from this object.", vbInformation
        DoCmd.Close
    End If
    If IsNull(aControl.Value) Or aControl.Value = "" Then
        objCalendar.Value = Date
    Else
        objCalendar.Value = aControl.Value
    End If
End Sub

7.  In the 'Cancel' button click event :

Private Sub cmdCancel_Click()
    DoCmd.Close
End Sub

8.  In the 'Clear' button click event :

Private Sub cmdClear_Click()
    aControl.Value = ""
    DoCmd.Close
End Sub

9.  In the 'Select' button click event:

Private Sub cmdSelect_Click()
    If objCalendar.Day <> 0 Then
        aControl.Value = objCalendar.Value
        DoCmd.Close
    Else
        MsgBox "A day has not been chosen.", vbInformation
    End If
End Sub
.
.
.

Hope I haven't overwhelmed you with code and instructions.  Let me know and I can explain or help you in greater detail.  Once all this is in place, you can easily connect all your date textboxes to the calendar form.
It gets easier the second time around :)
0
 
David_LeverenzCommented:
I am interested in using this code, and I am a novice.  I understand the overall concept of the solution, but I am not sure how to do this step

"3.  In a module, I created a variable 'calledFrom'"

I think that means do something like this "Dim calledFrom as Variant"

But I am not sure where to put that statement.  The solutions says to put it "in a module" but I am assuming that I cannot put it in any module, and if I can, what should the module name be?

Whenever I click the open form button I get the "The calendar is not properly referenced from this object." which I think means that the calledFrom statement might be in the wrong spot.  Currently I have it in the function that is assigned to the button to open the calendar:

Private Sub Command37_Click()
Dim calledFrom As Variant
calledFrom = "EndStartDate"
DoCmd.OpenForm ("frmcal")
End Sub

Any ideas?
0
 
PlamodoCommented:
Declare the calledfrom variable in any module (not a form).

Public calledFrom as string

If you don't make it public, the calendar form won't know what calledFrom is, which is why you got that message.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now