the following two methods allow a user to have a date value automatically entered into a field or control, based on prior date information in the table or other source.
the first method is performed in the GotFocus event
Private Sub dtMonths_GotFocus()
' to avoid errors from data validation, dim the date ' variable as type Variant Dim dtDateValue As Variant
Dim dtCurrentDate As Date Dim dtPriorDate As Date
' what's in the field right now? dtMonths is the name ' of the date field dtDateValue = Me!dtMonths.Value
' if a date isn't in the current field, find out what's ' in the same field in the prior record If Not IsDate(dtDateValue) Then dtPriorDate = CheckPriorDateValue()
' calculate what the current field should be ' in this example, the month value is incremented ' by one dtCurrentDate = DateAdd("m", 1, dtPriorDate)
' assign the new value to the current field Me!dtMonths.Value = dtCurrentDate End If
---------- the function CheckPriorDateValue() opens a recordset in the current table consisting of all the priviously entered (corresponding) date values. it then returns the date value of the *last* record if such a value exists. if a value can't be found, a prior date value is created and returned.
Private Function CheckPriorDateValue() As Date
Dim dtPriorDateValue As Date Dim strSQL As String
On Error GoTo ERROR_HANDLER
strSQL = "SELECT * FROM YourTable(s) WHERE criteria" & _ " = requirments"
Dim cnCurrent As ADODB.Connection Dim rsDates As ADODB.Recordset
Set cnCurrent = CurrentProject.Connection Set rsDates = New ADODB.Recordset
' open the recordset rsDates.Open strSQL, cnCurrent, adOpenDynamic, _ adLockOptomistic
' move to the last record and get the value rsDates.MoveLast dtPriorDateValue = rsDates!dtMonths
' close the recordset and connection rsDates.Close cnCurrent.Close Set rsDates = Nothing Set cnCurrent = Nothing
' return the prior date value CheckPriorDateValue = dtPriorDateValue Exit Function
ERROR_HANDLER: ' an error occurs when a recordset can't be created. ' e.g. no data ' this means that the focused "dtMonth" MUST be the ' first date value in the querried table. therefore, ' the first month to appear in the field should be ' the current date.
' assign today to dtPriorDateValue dtPriorDateValue = Format(Date, "mmm yy") ' turn it into a prior date and return CheckPriorDateValue = DateAdd("m", -1, _ dtPriorDateValue) 'e.g., last month Exit Function