Erroneous date update = 12/30/1899

[skydivetom]

Hello:



I need some help with reviewing/tweaking existing VBA code which updates/load certain date values. The attached DB contains the following objects:

1. Table "tbl_Date_Startup"
2. Form "F00_Dates"
3. Form "F01_MainMenu"

Background:
- Table [tbl_Date_Startup].[DATE_STARTUP] is set to today (i.e., 10/1/2021). This "start up", however, shall/may be overwritten based on user's listbox value selection in form "F01_MainMenu".

More Background:
- Upon opening the DB, the default form Form "F01_MainMenu" pops up. Doing so also opens (automatically) form "F00_Dates" in hidden mode.
- The listbox (in blue header) is automatically pre-populated with two (2) date values (in format MMM YYYY). These are automatically derived from form "F00_Dates"... which again has the startup date currently set for "10/1/2021".
- Also, the in form's main body, I included 4 unbound text boxes which values are also determined by the startup date. For purposes of this thread though, these 4 text boxes are not import for now.

Current Process:
a. Open the DB... which in turn opens "F01_MainMenu".
b. Based on the start-up date, the listbox value currently shows "Oct 2021" and "Nov 2021".
c. Now, for sake of argument, let's click on the 1st value "Oct 2021" again. For testing purposes only, 2 message boxes are thrown which show "Oct 2021" and index value = 0.

What Currently is Not Working Properly:
- Based step c), the user clicked on "Oct 2021". Nothing really happened at this point except the throwing of the message boxes.
- HOWEVER, in the background, an update query was executed which should have updated value "10/1/2021" in table [tbl_Date_Startup] with new/same value = "10/01/2021".
- When I open [tbl_Date_Startup], the updated start-up date value was changed to "12/30/1899".
- And, if you were to close/re-open the DB (keep in mind that form "F00_Dates" is still open in hidden mode), the listbox rows shows "Dec 1899" and "Jan 1900".

My questions:
- Based on the original start-up value of "10/1/2021" why does click "Oct 2021" (in listbox) result in the update of start-up date = "12/30/1899" when it should have been overwritten with the same value = "10/01/2021"?
- Also, in my table "tbl_Date_Startup", the format has changed from short format "10/1/2021" to some other format that now includes the time when clicking on "12/30/1899". Why the format change? I'd like to keep it in MM/DD/YYYY.
- Lastly, independent from the erroneous update to "12/30/1899", I get an run-time error ("Application-defined or object-defined error) when clicking on "Nov 2021". What may be causing this?

Thank you in advance for any help that would solve this erroneous date updates for the startup date.

Cheers,
Tom

[davegri]

You need date delimiters:

        Case 0
            'For testing only
            MsgBox Me.lst_Month.Value, vbInformation, "Testing - Get Value"
            MsgBox Me.lst_Month.ListIndex, vbInformation, "Testing - Get Index"
            
            'Update startup-date based on listbox value selection
            lbl_Month_Current = Me.lst_Month.Value
            CurrentDb.Execute "UPDATE tbl_Date_Startup SET tbl_Date_Startup.DATE_STARTUP = #" & myDate1 & "# WHERE tbl_Date_Startup.ID=1;"
        Case 1
            'For testing only
            MsgBox Me.lst_Month.Value, vbInformation, "Testing - Get Value"
            MsgBox Me.lst_Month.ListIndex, vbInformation, "Testing - Get Index"
            
            'Update startup-date based on listbox value selection
            lbl_Month_Next = Me.lst_Month.Value
            CurrentDb.Execute "UPDATE tbl_Date_Startup SET tbl_Date_Startup.DATE_STARTUP = #" & myDate2 & "# WHERE tbl_Date_Startup.ID=1;"

For the runtime error check your name spelling:

Private Sub lst_Month_AfterUpdate()
    
    Dim myDate1 As Date
    Dim myDate2 As Date
    
    myDate1 = Forms![F00_Dates].Month_Current_First
    myDate2 = Forms![F00_Dates].Month_Current_Next         'there is no Month_Current_Next in F00_Dates

[skydivetom]

davegri -- thank you for identifying the underlying issue. It works as envisioned now. As the saying goes, 'devil is in the detail'. Again, thank you for your assistance.

Cheers,
Tom