Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiday Append (2000)

    I need a piece of code that would fire from a form. When an end user enters a new event there are two fields Mutiday Event (yes/No) and Number of days (number). I need the code <if the Yes block is checked> to append the exact same data as the original <with the exception "Event Date" to the next days based on the value in the number of days. Any suggestions. The results will eventually end up on a Calendar Report. I have attached a sample of the table and form.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Multiday Append (2000)

    Probably the most straightforward approach would be to open an ADO or DAO recordset and do the insert for the appropriate number of days. Alternatively, you could loop through a DoCmd.RunSQL where you execute a SQL string that forms an append query the appropriate number of times. In either case, it involves the use of VBA behind the form.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiday Append (2000)

    I believe that the append query is my best option I just don't know how (1)to pass the value of the "number of days" field and (2) have the date change at each append.
    My plan is to have the append run before any new records are added. The problem here is which events to add the VBA code to. I could add code to (a) Refresh (b)close form and reopen the input form in the data add mode at the the "Add New" button. Then attach the append VBA code to the close form event. Any suggestions?

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

    Re: Multiday Append (2000)

    The best way would probably be to use an unbound form to enter new records. In the situation you have now, I would put a command button on the form, in the form footer, with On Click code like this:

    Private Sub cmdSave_Click()
    Dim i As Integer
    Dim d As Date
    On Error GoTo ErrHandler

    If Me.MutidayEvent = True Then
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If
    d = Me.EventDate
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    For i = 1 To Me.EventDays - 1
    RunCommand acCmdPasteAppend
    RunCommand acCmdRecordsGoToLast
    Me.EventDate = d + i
    Me.MutidayEvent = False
    Me.EventDays = Null
    Next i
    End If

    ExitHandler:
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiday Append (2000)

    The code worked almost perfectly. I am probably at fault because I did not understand about the unbound part of your instructions so I left it bound to the table in a Add Data mode. When I entered the data checked the box and added the number of days then clicked save I got an error message saying the "RunCommand acCmdPasteAppend" function was not available. When I went to Debug that was the line that was highlighted. Then a strange thing happened. I selected the "Continue" command from the drop down menu and when the form reopened the correct number of records with the correct date and data were successfully appended. I didn't change any code just told VB to continue and it worked great. I played with inserting "Refresh" in various places of your code but that had no effect. Is this because it is not an unbound form? And if so how do I make the form unbound. When I removed the data source from the properties all of the fields showed "Name?". Do I go to each field on the form and set the data properties there and leave the form's data property blank? I made a power point file that shows the aforementioned steps but it is slightly too big even when zipped to attach.

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

    Re: Multiday Append (2000)

    I have attached the database you posted with the command button added. It works for me. Do you get the error if you try this version?
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiday Append (2000)

    I deeply appreciate your patience with this. But I get the same error sequence with your attached form. I get the error message "Paste Append function is not available". When I open the "Debug" window (VB Editor) and select "Continue" (F5) from the Run Menu options it continues and finishes the tasks you assigned it perfectly. The strange thing was that after going through the "Debug" sequence it did work once but I could not get it to duplicate the success. Is it possible that I do not have a needed "Reference" checked in the library?

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

    Re: Multiday Append (2000)

    No, if there was a references problem, the code would not work at all. I will try to get behind this, but it'll have to be later.

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

    Re: Multiday Append (2000)

    Here is a different version. It uses an unbound form, and code behind the "Save" button to write the record or records to the table.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiday Append (2000)

    Just to let you know, I retried your original design this AM and it worked perfectly. I put it up on a shared network and again it worked perfectly. I made a copy of it renamed it and it still worked perfectly. You sir are a genius. I have no clue on why it kept hanging up yesterday.

Posting Permissions

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