Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    imitating Excel's fill function (A2003)

    Working on a form to enter utility billing information. I would like to automate entering the billing start and end dates rather like Excel's fill function works. Ideally I'd enter the month, day and year of the first billing's start day and the duration of the billing period, ie one month or 30 days, and the form would would take it form there. Anyone out there put anything like this together that I could study?

    E

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: imitating Excel's fill function (A2003)

    I would use a small unbound form for this, with
    - a text box txtStart for the start date. Set its Format property to your favorite date format; input will automatically be validated.
    - a text box txtDuration for the number of days. Set its Format property to General.
    - a command button cmdOK with caption "OK".
    - a command button cmdCancel with caption "Cancel".

    The On Click event procedure of the OK button creates new records, then closes the unbound form and optionally opens a form displaying the records. You must substitute the appropriate names in the following code:

    Private Sub cmdOK_Click()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim datStart As Date
    Dim i As Long
    Dim lngDuration As Long

    On Error GoTo ErrHandler

    If IsNull(Me.txtStart) Then
    MsgBox "Please enter a start date.", vbExclamation
    Me.txtStart.SetFocus
    Exit Sub
    End If

    If IsNull(Me.txtDuration) Then
    MsgBox "Please enter a duration.", vbExclamation
    Me.txtDuration.SetFocus
    Exit Sub
    End If

    If Not Val(Me.txtDuration) > 0 Then
    MsgBox "Please enter a valid duration.", vbExclamation
    Me.txtDuration.SetFocus
    End If

    Set cnn = CurrentProject.Connection
    rst.Open "tblSomething", cnn, adOpenDynamic, adLockOptimistic, adCmdTableDirect

    datStart = Me.txtStart
    lngDuration = Me.txtDuration

    For i = 0 To lngDuration - 1
    rst.AddNew
    rst!MyDate = datStart + i
    rst.Update
    Next i

    ' Optional: open other form.
    DoCmd.OpenForm "frmMyForm"

    ExitHandler:
    On Error Resume Next
    DoCmd.Close acForm, Me.Name, acSaveNo
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    The Cancel button just closes the unbound form:

    Private Sub cmdCancel_Click()
    DoCmd.Close acForm, Me.Name, acSaveNo
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: imitating Excel's fill function (A2003)

    This is a great headstart. Thanks.

    Currently the code accepts the date I enter and then lists subsequent dates for the duration I enter in txtDuration. What I'd like instead is to enter the Start and have the value in the duration add that many days to the State date and yield the End date the record. These are beginning and ending dates for billing periods.

    Next I'd like it to I'd like to add one day to the end date and have that be the Start date for a new record.

    I'd like that process to continue until the end date of a record exceeds the value entered in a "terminate" date.

    Can this code readily be amended to do that?

    E

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: imitating Excel's fill function (A2003)

    Need to muscle the code a bit to make it a better governor and add to it's star power.

    I get a "variable not defined" on the i in the section of code below. Added Dim i As Integer and then got compile error on the Next in the same line. The message is "Next without For".

    Do While datStart + lngDuration <= datSchwarznegger
    ' Create new record
    rst.AddNew
    rst!StartDate = datStart
    rst!EndDate = datStart + lngDuration
    rst.Update
    ' Set next start date
    datStart = datStart + lngDuration + 1
    Next i

    E

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: imitating Excel's fill function (A2003)

    Try substituting "Loop" for "Next i". You then can remove "Dim i as Integer". Hope this helps.
    Gre

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: imitating Excel's fill function (A2003)

    (Error in attachment corrected by HansV)

    Hi Elizabeth,

    What with all the checks, the code is getting too long to post in the reply itself, so I attached it as a text file. As before, you must substitute the names you are using. The code assumes that you have the following text boxes on the form:
    - txtStart: start date of first billing period
    - txtDuration: duration of billing period
    - txtTerminate: stop if end date of billing period is past this date.
    Post back if it doesn't work, I haven't tested it.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: imitating Excel's fill function (A2003)

    Sorry about that; fortunately unkamunka already pointed out the error (thanks, unkamunka!). I had not been careful enough when editing the original code. I have corrected the attachment in my previous reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •