Calendar not working

**TheSmileyCoder** · Sep 6 '12, 10:24 PM

What access version? Is it 2010 and built in calender control(Date picker), or is it a custom calender control?

Is the form in datasheet, single record view, or continuous or split? Is the form bound?

**Becker** · Sep 6 '12, 10:54 PM

Access version 2007. I guess it is the built in calender. It is just the one that appears at the side of a text box that is bound to a field on a table that accepts dates only. The form is just in the standard form view. The form is bound to the table with these fields that require dates.

**TheSmileyCoder** · Sep 7 '12, 08:14 AM

This sounds weird to me. Do you have any VBA code or macros running in the form? If so, please post it all here, for review.

So your saying that after the first record is added people can still click the date picker, and select a date, but the date does not get written into the control?

**Becker** · Sep 7 '12, 02:59 PM

This is all of the code for this form. The text boxes that are having this issue are "Date_Order ed" and "Delivery." Again it works the first time but not after that.

Code:

Private Sub BY_AfterUpdate()
Me.Price_Unit.SetFocus
End Sub

Private Sub BY_GotFocus()
If IsNull(Me.Date) = True Then
Me.BY.Locked = True
Else: Me.BY.Locked = False
End If
End Sub

Private Sub Delete_Click()
If IsNull(Me.finditem) = False Then
Dim Msg, Style, Title, Response, MyString

Msg = "Are you sure you want to delete this order?" & vbNewLine & "You cannot undo this action."
Style = vbYesNo
Title = "Warning!"

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbNo Then
Exit Sub
End If
If Response = vbYes Then
DoCmd.Hourglass True
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

Dim iCfg As Object
Dim iMsg As Object
Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")
With iCfg.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "exc2007.futuraind.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Update
End With

'Stop
With iMsg
.Configuration = iCfg
.Subject = "Order Canceled"
.to = Me.purchasesub!Notify
.TextBody = "The order for " & Me.finditem & " submitted on " & Me.Date & " has been cancled by purchasing.  Please address any issues or concerns with purchasing."
'.AddAttachment "FullPathToAttachment"
.from = "Purchasing"
.sender = "PurchasingNoReply@futuraind.com"
.send
End With
Set iMsg = Nothing
Set iCfg = Nothing
Call MsgBox("Your order was processed successfully!", vbOKOnly, "Success!")
DoCmd.Close acForm, "purchase chemicals"
DoCmd.OpenForm "purchase chemicals"
DoCmd.Hourglass False
DoCmd.Close acForm, "purchase chemicals"
DoCmd.OpenForm "purchase chemicals"
End If
ElseIf IsNull(Me.finditem) = True Then
Call MsgBox("There is not an order to delete.")
Exit Sub
End If
End Sub

Private Sub Complete_Click()
If IsNull(Me.Date_Ordered) = True Then
Call MsgBox("The Date Ordered is a required field.")
Exit Sub
ElseIf IsNull(Me.Date_Ordered) = False Then
DoCmd.Hourglass True
Dim iCfg As Object
Dim iMsg As Object
Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")
With iCfg.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "exc2007.futuraind.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Update
End With

'Stop
With iMsg
.Configuration = iCfg
.Subject = "Order Completed On: " & DateValue(Now) & " at: " & TimeValue(Now)
.to = Me.purchasesub!Notify
.TextBody = "Date Requested: " & Me.Date & vbNewLine & "Item: " & Me.finditem & vbNewLine & "Units: " & Me.purchasesub!Units & vbNewLine & "Order Quantity: " & Me.purchasesub![Reorder Quantity] & vbNewLine & "Date Ordered: " & Me.Date_Ordered & vbNewLine & "Estimated Delivery Date: " & Me.Delivery & vbNewLine & "Purchase Order #: " & Me.PO & vbNewLine & "Purchased By: " & Me.BY & vbNewLine & "Price/Unit: " & Me.Price_Unit & vbNewLine & "Purchasing Comments: " & Me.Order_Comments
'.AddAttachment "FullPathToAttachment"
.from = "Purchasing"
.sender = "PurchasingNoReply@futuraind.com"
.send
End With
Set iMsg = Nothing
Set iCfg = Nothing
DoCmd.Close acForm, "purchase chemicals"
DoCmd.OpenForm "purchase chemicals"
DoCmd.Hourglass False
End If
End Sub

Private Sub Date_Ordered_AfterUpdate()
Me.Delivery.SetFocus
End Sub

Private Sub Date_Ordered_GotFocus()
If IsNull(Me.Date) = True Then
Me.Date_Ordered.Locked = True
Else: Me.Date_Ordered.Locked = False
End If
End Sub

Private Sub Delivery_AfterUpdate()
Me.PO.SetFocus
End Sub

Private Sub Delivery_GotFocus()
If IsNull(Me.Date) = True Then
Me.Delivery.Locked = True
Else: Me.Delivery.Locked = False
End If
End Sub

Private Sub edit_Click()
DoCmd.OpenTable "ordering information"
End Sub

Private Sub exit_Click()
DoCmd.Close acForm, "purchase chemicals"
DoCmd.OpenForm "order chemicals"
End Sub

Private Sub finditem_GotFocus()
Dim strFilter As String, strOldFilter As String
    strOldFilter = Me.Filter
    If Me!finditem > "" Then _
        strFilter = strFilter & _
                    " AND ([chemical] Like '" & _
                    Me!finditem & "*')"
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
End Sub

Private Sub Form_Load()
Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strFilePath As String

' looks up the version of the front-end as listed in the backend
strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")

' looks up the version of the front-end on the front-end
strFE = DLookup("fe_version_number", "tbl-fe_version")

' looks up the location of the front-end master file
strMasterLocation = DLookup("s_masterlocation", "tbl-version_master_location")

' checks for the existence of an updating batch file and deletes it if it exists
    strFilePath = CurrentProject.Path & "\UpdateDbFE.cmd"
    
    If Dir(strFilePath) <> "" Then
        Dim fs As Object
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.DeleteFile (strFilePath)
        Set fs = Nothing
    End If
        

' if the current database opened is the master then it bypasses the check.
If CurrentProject.Path = strMasterLocation Then

    Exit Sub
    
Else

' if the version numbers do not match and it is not the master that is opened,
' the database will do the update process
    If strFE <> strFEMaster Then
        MsgBox "Your program is not the latest version." & vbCrLf & _
        "The front-end needs to be updated.  The program will " & vbCrLf & _
        "now close and then should reopen automatically.", vbCritical, "VERSION NEEDS UPDATING"
        
        ' sets the global variable for the path/name of the current database
        g_strFilePath = CurrentProject.Path & "\" & CurrentProject.Name
        
        ' sets the global variable for the path/name of the database to copy
        g_strCopyLocation = strMasterLocation
        
        ' calls the UpdateFrontEnd module
        
        UpdateFrontEnd
        
    End If
    
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
RunCommand acCmdRecordsGoToNew
End Sub

Private Sub Order_Comments_AfterUpdate()
Me.Date_Ordered.SetFocus
End Sub

Private Sub Order_Comments_GotFocus()
If IsNull(Me.Date) = True Then
Me.Order_Comments.Locked = True
Else: Me.Order_Comments.Locked = False
End If
End Sub

Private Sub PO_AfterUpdate()
Me.BY.SetFocus
End Sub

Private Sub PO_GotFocus()
If IsNull(Me.Date) = True Then
Me.PO.Locked = True
Else: Me.PO.Locked = False
End If
End Sub

Private Sub Price_Unit_AfterUpdate()
Me.Order_Comments.SetFocus
End Sub

Private Sub Price_Unit_GotFocus()
If IsNull(Me.Date) = True Then
Me.Price_Unit.Locked = True
Else: Me.Price_Unit.Locked = False
End If
End Sub

Private Sub refresh_Click()
DoCmd.Close acForm, "purchase chemicals"
DoCmd.OpenForm "purchase chemicals"
DoCmd.SetWarnings False
DoCmd.OpenQuery "delete"
DoCmd.SetWarnings True
End Sub

**zmbd** · Sep 7 '12, 03:49 PM

Becker,
I have not been able to recreate the issue with any of my test databases using v2010.

I'm guessing that you're using: http://msdn.microsoft.com/en-us/libr.../gg251104.aspx - is that correct?

Just double check the properties are set as indicated.

The other thing... temporarily comment out your afterupdate events for both of these controls as they seem to be just form movement and see what happens (I'm just poking at things here to see what screams in the dark). Ideally, you should have the tab-order for your form set to move from one control to the next for most things and use the set-focus only when needed for an unusual situation or to get information from the control (weird.. ms... what can I say?!)

-z

**Rabbit** · Sep 7 '12, 03:54 PM

What is Me.Date? And why are a bunch of the controls getting locked if it's null?

**Becker** · Sep 7 '12, 04:23 PM

Z-Yes I am just using the built in date-picker. Settings are correct. I haven't changed anything with them. All I have done is made the field on the table require dates and the date-picker comes automatically. Yes my afterupdate events are just to move from one text box to another, like the tab controls. I am a newbie so I don't usually end up doing things in the most orthodox way I guess.

Rabbit-me.date is a box that contains that date that an order was submitted. The controls being locked is just my way of making sure that they do not enter information until they have actually selected an order to view.

**zmbd** · Sep 7 '12, 05:04 PM

Another stab in the dark....

Change the locks to enabled=true/false... maybe the controls are still locked after the first go around?

-z

**Becker** · Sep 7 '12, 05:23 PM

They are not because I can type in the box. The calendar still shows up, but when I pick a date it does not go into the box.

**zmbd** · Sep 7 '12, 07:17 PM

Becker,
I understand that; however, there maybe a bug in the datepicker so that it thinks that the control is still in the locked state.

On the test form I built (V2010) despite the control being in the locked state, I could select the text (couldn't change it) and the datepicker would also show - something I wouldn't have expected - that's ms for you.

ANYWAY... deep breath... Looking thru the developer's websites and a few other references I have access to - I have found a few other bug reports for the Datepicker where it hangs, doesn't let afterupdate fire, doesn't update a control, etc; however, nothing that matches what you are doing or I'd post the link. However, in each case I noted a common theme... the control was locked and then unlocked between losing and receiving focus and typically the control worked the first time but either failed the second time or at some random point. No-one seems to know why and I did not see this issue being reported as related to V2010, just V2007.

Being a scientist, chemist, we're trained to look for cause and effect, patterns... this caught my mind's eye as a pattern to test...

SO... I'm once again poking something sharp and pointy into the dark to see what screams... (ahh... October... the only month my morbid sense of humor gets to play };-) )

To test the hypothesis... try the form without locking any of the controls either by removing the locking entirely or by using the enabled property (ideally both).

On a personal note as an end user: I for one as a user get very annoyed at controls that appear like I should be able to enter data and they're locked. The enabled property (IMNSHO) is a better property when you need the information shown in that it allows the user to see the information and it is clearly for read-only.

That's the extent of the help I can offer at this point. I'll see if I can get a spider out looking for this for the other sites
-z

**Becker** · Sep 7 '12, 08:07 PM

Haha I tried removing the locks (so they are always unlocked) but it still didn't work.

**Arno** · Jun 20 '14, 10:58 AM

anything found to resolve this?? I have EXACTLY the same problem..

**twinnyfo** · Jun 20 '14, 11:08 AM

Arno, et al,

I don't know if this will help at all, but it has worked for me--to assist users from accidentally adding an incorrect date format. It does not prevent from clicking into the text box, but it automatically unlocks the text box and brings up the calendar when the text box gets the focus, and then locks the text box when it loses focus.

Code:

Private Sub txtDate_GotFocus()
    Me.txtDate.Locked = False
    DoCmd.RunCommand acCmdShowDatePicker
End Sub
Private Sub txtDate_LostFocus()
    Me.txtDate.Locked = True
End Sub

Hope this hepps!

**Arno** · Jun 20 '14, 11:16 AM

that could be a workaround yes!

in the meantime i've found what causes this behaviour, not a solution though.
after filling in the date and saving the record, there comes a msgbox. The second time the datepickers stops working and if i do not show the msgbox, it keeps on working . So there must be something about the Dialog window of that msgbox that causes the Datepicker to stop working..