Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    3

    Question Unanswered: New Form Record based on Existing Record

    I am currently developing a database that contains a client data form. In some instances a user may want to enter a new client whose data is very similar to an existing client, i.e., a spouse. What code (or macro) might I use behind a copy button on the form to copy selected fields from the current form record to a new client record and then jump to the new (copied record). Further, I would like to distinguish between the original record and the new copy by editing one of the copied fields (Last Name field in Original record = Jones, Last Name field in the new copy of the record = Copy of Jones). I imagine this is pretty routine for some of you. Can anyone help? Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133
    Are you using the same form to enter new clients or a separate form?
    SteveH

  3. #3
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44

    copy record

    Here is some code I use for a member database to create a spouse record.
    The MailingNAME field is disabled to prevent duplicate mailings for the couple.
    You could use Me![NAME] = "Copy of " & Me![NAME] for your name distinction.
    Code:
    Private Sub btnSpouse_Click()
    On Error GoTo Err_btnSpouse_Click
    Dim stSx As String * 1
    
        blSpouse = True
        stSx = Me![SEX]
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 'Select Record
        DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70 'Copy Record
        DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
        Me![MailingNAME] = Null
        Me![MailingNAME].TabStop = False
        Me![MailingNAME].Enabled = False
        If stSx = "M" Then
           Me![SEX] = "F"
          Else
           Me![SEX] = "M"
           End If
        Me![BIRTH] = Null
        Me![NAME].SetFocus
    
    Exit_btnSpouse_Click:
        Exit Sub
    
    Err_btnSpouse_Click:
        MsgBox Err.Description
        Resume Exit_btnSpouse_Click
        
    End Sub

  4. #4
    Join Date
    Mar 2003
    Posts
    3

    Smile

    Thanks. As I am a little new to VBA programming I'll look at your code a little more closely tonight when I have some more time.

    What I did in the meantime is (for the Copy_Record_Click() Event)simply copy selected controls from the current record, append " (Copy of)" to the First Name field, goto the new record, and finally copy the stored fields back into the appropriate controls. Seems to work fine except I still have to put in some Null handling code.

    One very interesting phenomenom is that in my original form I had Primary and Mailing addresses in subforms (although they were part of the same table - perhaps a poor decision in the first place). These subforms were then part of two different Tabs in an Address tab control where one tab was Primary Address and the other Mailing Address. In this configuration when I copied the stored control data back into the new record seperate table entries were made for the Primary Address, the Mailing Address, and all the rest of the member data. In other words, where I was trying to make a copy of one member's data and put that data in the new record, I actually ended up with three new records. After clicking my Copy button all the data would show on the same form, but as soon as I clicked anywhere in the form the data would dissappear from the Address subforms but I could find it in two new individual records made after the first new record. This was solved when I eliminated the subforms which was easy to do since all of the forms data actually came from the same query, but I still wonder what is going on here.

    Thanks again.

  5. #5
    Join Date
    Mar 2003
    Posts
    3

    Smile

    Thanks. As I am a little new to VBA programming I'll look at your code a little more closely tonight when I have some more time.

    What I did in the meantime is (for the Copy_Record_Click() Event)simply copy selected controls from the current record, append " (Copy of)" to the First Name field, goto the new record, and finally copy the stored fields back into the appropriate controls. Seems to work fine except I still have to put in some Null handling code.

    One very interesting phenomenom is that in my original form I had Primary and Mailing addresses in subforms (although they were part of the same table - perhaps a poor decision in the first place). These subforms were then part of two different Tabs in an Address tab control where one tab was Primary Address and the other Mailing Address. In this configuration when I copied the stored control data back into the new record seperate table entries were made for the Primary Address, the Mailing Address, and all the rest of the member data. In other words, where I was trying to make a copy of one member's data and put that data in the new record, I actually ended up with three new records. After clicking my Copy button all the data would show on the same form, but as soon as I clicked anywhere in the form the data would dissappear from the Address subforms but I could find it in two new individual records made after the first new record. This was solved when I eliminated the subforms which was easy to do since all of the forms data actually came from the same query, but I still wonder what is going on here.

    Thanks again.

Posting Permissions

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