Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate Record with No Duplicate Key? (A2k (9.0.3821) SR-1)

    Duplicate Record with No Duplicate Key?

    A2k (9.0.3821) SR-1

    This application has a form that allows entry of family information with social security number.

    To cut down on data entry I would like duplicate the wife informatiom into a separate record after entering the husbands information, then make wifes social security number adjustment along with other field changes.

    Also, if a child record has to be setup in the future, query a parent record and duplicate and renter the social security number for the child.

    What is the best approach to put a duplicate record button on a form to duplicate a record that has a social security number with no duplicates allowed?

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicate Record with No Duplicate Key? (A2k (9.0.3821) SR-1)

    The best approach is not to do it that way. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> You don't actually want to create duplicate records, you really want to copy values from fields in one record into a new record, which is not exactly the same thing.

    There are several ways you could approach this, and one of the simplest would be to generate the SQL for an append query in code, substituting the result of an inputbox for the social security number, but otherwise using the values from the current record as the values to pass into the append query. Then requery the form and use the value from the inputbox (stored in a variable, of course) to locate that record and make it the current record.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record with No Duplicate Key? (A2k (9.0.3821) SR-1)

    Hi Charlotte

    I like your solution, any code snippets kicking around that demonstrate your approach?.

    Had to go forward with code below to meet a deadline, may be of interest to other newbes.

    Thanks, John

    Private Sub Duplicate_Record_Click()

    Dim strDrss As String
    Dim strDrfnam As String
    Dim strDri As String
    Dim strDrlnam As String
    Dim strDraddr1 As String
    Dim strDraddr2 As String
    Dim strDrcity As String
    Dim strDrstate As String
    Dim strDrzip As String
    Dim strDrareac As String
    Dim strDrtel As String
    Dim strDrid As String
    Dim strPC As String

    'Save data in current record to variables
    strDrss = Nz(Me![strDrss])
    strDrfnam = Nz(Me![strDrfnam])
    strDri = Nz(Me![strDri])
    strDrlnam = Nz(Me![strDrlnam])
    strDraddr1 = Nz(Me![strDraddr1])
    strDraddr2 = Nz(Me![strDraddr2])
    strDrcity = Nz(Me![strDrcity])
    strDrstate = Nz(Me![strDrstate])
    strDrzip = Nz(Me![strDrzip])
    strDrareac = Nz(Me![strDrareac])
    strDrtel = Nz(Me![strDrtel])
    strDrid = Nz(Me![strDrid])
    strPC = Nz(Me![strPC])

    'Go to new record
    DoCmd.GoToRecord , , acNewRec
    If strDrss <> "" Then Me![strDrss] = strDrss
    If strDrfnam <> "" Then Me![strDrfnam] = strDrfnam
    If strDri <> "" Then Me![strDri] = strDri
    If strDrlnam <> "" Then Me![strDrlnam] = strDrlnam
    If strDraddr1 <> "" Then Me![strDraddr1] = strDraddr1
    If strDraddr2 <> "" Then Me![strDraddr2] = strDraddr2
    If strDrcity <> "" Then Me![strDrcity] = strDrcity
    If strDrstate <> "" Then Me![strDrstate] = strDrstate
    If strDrzip <> "" Then Me![strDrzip] = strDrzip
    If strDrareac <> "" Then Me![strDrareac] = strDrareac
    If strDrtel <> "" Then Me![strDrtel] = strDrtel
    If strDrid <> "" Then Me![strDrid] = strDrid
    If strPC <> "" Then Me![strPC] = strPC
    Me![strDrss] = "NEW " & Me![autDentistKey] ' Social Sec # (no dups)
    Me!strDrss.SetFocus

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicate Record with No Duplicate Key? (A2k (9.0.3821) SR-1)

    If strDrss is the social security number, I don't see how it could be an empty string, at least not if the table was properly indexed. I would NOT fill it in with a default but would always force the user to enter a social security number, which I assume they have to do anyhow in order to save the record.

    This suggests the social security number can be an empty string: If strDrss <> "" Then Me![strDrss] = strDrss.

    But even if it has a value, you're changing it here: Me![strDrss] = "NEW " & Me![autDentistKey] ' Social Sec # (no dups)

    It is less than clear whether the social security number is unique or not. Adding a "NEW" to an existing social security number just insures garbage in the database, not unique social security numbers.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Record with No Duplicate Key? (A2k (9.0.3821) SR-1)

    Hi Charlotte

    I understand your concern.

    I initially was duping the entire record less the ss#.

    After pressing Dup, if the operator changed their mind and hit the delete button, access would complain because ss# was an empty key field.

    When the dup button is pressed the new record appears so fast, the operator didn

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicate Record with No Duplicate Key? (A2k (9.0.3821) SR-1)

    Well, logically you can't delete a record that hasn't been saved yet. You can trap the KeyDown even and test to see if the vbKeyDelete has been pressed. If it was, test for me.dirty and use Me.Undo to reverse any changes. Tthen test to see if you're on a new record. If not, delete the current record. If you ARE on a new record, you don't have to do anything but move back to the record whose key is stored in your variable.
    Charlotte

Posting Permissions

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