Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append a table base on form selection (2003)

    Hi,

    I have a form which is linked to a subform and only displays data that matches the criteria in the parent form. Then at the bottom of the parent form is a command button that I want to copy data from a line of data I select in the subform and append this to the bottom of the same subform table. I also need to maniplate certain fields i.e. increase a number by one.

    An example would be:
    Example data from subform
    CRN APPID REFDATE APPDATE OUTCOME TYPE
    123 1 01/01/08 05/01/08 F1 ENT
    124 2 01/01/08 12/02/08 F2 ENT

    I want to be able to select the line with the highest 'APPID' and append to the bottom of the same table, but increase 'APPID' by one and a message box requesting me to input the new 'APPDATE' and 'OUTCOME' and also to change the 'TYPE' field to have the letters GC at the end i.e. ENT=ENTGC

    The finished table above would look like the following
    CRN APPID REFDATE APPDATE OUTCOME TYPE
    123 1 01/01/08 05/01/08 F1 ENT
    124 2 01/01/08 12/02/08 F2 ENT
    125 3 01/01/08 22/02/08 J ENTGC

    Many thanks for all who can help.
    Regards
    Gerbil (AKA Kevin)

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

    Re: Append a table base on form selection (2003)

    Welcome to Woody's Lounge!

    Is there a unique key on any of the fields involved (for example the primary key of the table)?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append a table base on form selection (2003)

    Hi, thank you for your quick reply.
    There is no primary key on either of the two tables.
    Many thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: Append a table base on form selection (2003)

    Say that your command button is named cmdDuplicate. The On Click code for it could look like this:
    <code>
    Private Sub cmdDuplicate_Click()
    Dim txtDate As String
    Dim txtOutcome As String

    ' Prompt the user for new values
    txtDate = InputBox("Please enter the new REFDATE")
    If Not IsDate(txtDate) Then
    MsgBox "Date not valid. Please try again.", vbExclamation
    Exit Sub
    End If

    txtOutcome = InputBox("Please enter the new OUTCOME")

    ' Sort the form on APPID - can be omitted if it is already sorted that way
    Me.OrderBy = "APPID"
    Me.OrderByOn = True

    ' Duplicate the last record
    RunCommand acCmdRecordsGoToLast
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend

    ' Set the new values
    Me.CRN = Me.CRN + 1
    Me.APPID = Me.APPID + 1
    Me.REFDATE = CDate(txtDate)
    Me.OUTCOME = txtOutcome
    End Sub
    </code>
    Note: if CRN is an AutoNumber field, omit the line
    <code>
    Me.CRN = Me.CRN + 1</code>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append a table base on form selection (2003)

    Hi Hans, thanks for the code last night. I am been a little stupid and can't quite get things right. Below is the code you gave me adapted to my fields etc. But its falling over on the RunCommand part to select last, copy, paste etc. The error is 'The command or action 'RecordsGoToLast' is not available now.' You will notice the data I wish the amend is in a sub-form and the button 'Create Ghost Clinic' is on the parent form.

    As always you help is most welcomed.

    Private Sub CmdGhostClinic_Click()

    Dim txtDate As String
    Dim txtOutcome As String

    ' Prompt the user for new values
    txtDate = InputBox("Please enter the Ghost Clinc Appointment Date")
    If Not IsDate(txtDate) Then
    MsgBox "Date not valid. Please try again.", vbExclamation
    Exit Sub
    End If

    txtOutcome = InputBox("Please enter the new Outcome Code")

    ' Sort the form on APPTID - can be omitted if it is already sorted that way
    ' Me!FrmOUT0506Sub.Form OrderByOn = True
    ' Me!FrmOUT0506Sub.Form OrderBy = "Me!FrmOUT0506Sub.Form.ApptID"

    ' Duplicate the last record
    RunCommand acCmdRecordsGoToLast
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend

    ' Set the new values
    Me!FrmOUT0506Sub.Form.ApptID = Me!FrmOUT0506Sub.Form.ApptID + 1
    Me!FrmOUT0506Sub.Form.F_CLINIC = Me!FrmOUT0506Sub.Form.F_CLINIC & GC
    Me!FrmOUT0506Sub.Form.ApptDate = CDate(txtDate)
    Me!FrmOUT0506Sub.Form.OUTCOME = txtOutcome
    Me!FrmOUT0506Sub.Form.F_Status = L
    Me!FrmOUT0506Sub.Form.F_Fatt = 2
    Me!FrmOUT0506Sub.Form.F_DNA = 5
    Me!FrmOUT0506Sub.Form.F_MEDSTAFF = ""
    Me!FrmOUT0506Sub.Form.F_CONSCLINIC = ""
    Me!FrmOUT0506Sub.Form.F_CONS = ""
    End Sub
    Regards
    Gerbil (AKA Kevin)

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

    Re: Append a table base on form selection (2003)

    The RunCommand lines will act on the currently active form, so you have to make the subform active:

    Me!FrmOUT0506Sub.SetFocus
    RunCommand acCmdRecordsGoToLast
    ...

  7. #7
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append a table base on form selection (2003)

    You are a vba guru! thank you
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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