INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

How to autonumber date values in a control. by mirirom
Posted: 28 Sep 01 (Edited 11 Dec 02)

updated 12/11/2002

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
    
End Sub

----------
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
    
End Function

--------------------

hope this helps:)
questions, comments?  email:  mirirom@digimetic.com

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close