Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append Current Form to another Table (Access 2000)

    How can I transfer the current record on a form to a table built to append records too, (e.g. old customers). Can I use a macro button to run an append query on the current record? Could you verify that notion, or is there a simpler, or better way to do this. If I did use an append query, then how in the append query would i state that it is the current record in the form I want appended?
    Thanks for any feedback
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Append Current Form to another Table (Access 2000)

    Unless the number of records becomes extremely large, I wouldn't use separate tables for current and old customers. Instead, I would add a Yes/No field named Archived to the customers table. If this field is set to Yes, the customer is an "old" customer.
    Create a query that selects all records from the table for which Archived is False, i.e. the current customers only, and use this as record source for your form (and report). When the user ticks the check box bound to Archived, the customer will still be in the table, but won't be displayed any more next time the form is opened.
    You can also create a query that selects all records from the table for which Archived is True, i.e. "old" customers.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Append Current Form to another Table (Access 2000)

    I don't disagree with Hans, although there are situations where you might want to do what you describe.

    In some situations, I append the current record to an archive table before allowing any changes to it, so the archive table represents a history of previous records.

    Assuming your table has a primary key, then the append query will append each field, but set a criterion on the key field. Use the expression builder to build an expression that refers to the primary key value on your form.
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Jun 2005
    Location
    Jacksonville, North Carolina, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Current Form to another Table (Access 2000)

    This question was asked on my behalf. To clarify a little, I have a form that has my personnel in it and a sub-form on the main form that holds the training and medical records for the personnel. I want to be able to display the current medical status when I open the form each time. However, I would like to be able to append the medical status to a separate table each time there status changes. I was looking at using a button on the sub-form "change medical status" and when they choose that button I want them to be able to type in a new status and have the old status append to the separate table. Any assistance would be appreciated.

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

    Re: Append Current Form to another Table (Access 2000)

    To expand on John's reply:

    This is easiest if the table with medical records has a unique key field, let's say MedRecID.
    Create a query based on your medical records table.
    Add all fields you want to transfer to the other table.
    Set the criteria for the MedRecID field (or whatever it's called) to

    [Forms]![MainFormName]![SubFormName]![MedRecID]

    substituting the appropriate names for the main form, subform and field name.
    Select Query | Append Query and specify the "other" table as the target table.
    Save this query, say as qryAppendMedRec.
    You can run this query from the command button using

    DoCmd.OpenQuery "qryAppendMedRec"

    The user will be warned about being about to perform an action query and to append 1 record. During testing, this is useful. For production, you'll want to suppress these messages:

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryAppendMedRec"
    DoCmd.SetWarnings True

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Current Form to another Table (Access 2000)

    I'm assuming your table is based on a PK of SSN(?) If so, why not create a separate table called tblMedicalHistory, with the fields SSN, MedicalStatus, MedStatFromDate, MedStatToDate, and Provider. In the After Update event of your form, you could use code to automatically insert a new record, instead of appending the current record, with no button clicking required, etc. You could also add a field to track who made the change.

    To do this, you would use code something like:
    Private Sub frmMedicalStatus_AfterUpdate()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rst.Open "tblMedicalHistory", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst.SSN = [SSN]
    rst!MedicalStatus = [MedicalStatus]
    rst!MedStatFromDate = [MedStatFromDate]
    rst!MedStatToDate = [MedStatToDate]
    rst!Provider = GetNetUser()
    rst.Update

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

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

    Just another approach. This method doesn't allow for errors though, as there is no button to click, it automatically enters the records when the data on your form is updated....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Append Current Form to another Table (Access 2000)

    It sounds like you already have a separate table for holding medical status records, and there can be multiple records for each person. The current status is just the last record in this table. This is shown in the subform. Does the subform show multiple records or just one?

    When you want to change the status, the normal thing to do would be to just add a new record to this table with the new status. From what you are saying it sounds like you want to copy the current last record, then go back and edit the record you just copied.

    If you just add a new record with the new status, you could display the current status on the main form using dlookup.
    Regards
    John



  8. #8
    New Lounger
    Join Date
    Jun 2005
    Location
    Jacksonville, North Carolina, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Current Form to another Table (Access 2000)

    You are correct, I do have a separate table already. The sub form shows only one record per person. Yes, I do want to move the information to a new table so that I have an on going table of all medical status records. I am working through I the help that has been left to see what is going to work the best for me, I will try the dlookup also.

Posting Permissions

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