Results 1 to 7 of 7
  1. #1
    sessionone is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17

    Saving same record twice with minor changes (based on combobox selection)

    Hi all,



    I am looking for advice on how to save the same record twice, but with minor changes.

    Let's say I have txtNo., txtName, txtSurname and cboType. cboType choices are "1", "2" and "both".

    If I select "1", when "save" button is clicked, a new record is saved in table field "Type" as "1". The same is true with "2".

    I would like to have the option to save the record twice, with the same name and surname, but different types:

    John Smith, 1
    John Smith, 2

    Thank you for your kind help.

    sessionone

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    run 2 append queries.

    Code:
    select case cboType
       case '1'
        docmd.openquery "qaAdd1"
      
       case '2'
    
    docmd.openquery "qaAdd2"
    case 'both'
    docmd.openquery "qaAdd1" docmd.openquery "qaAdd2"
    end select

  3. #3
    sessionone is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17
    Thanks. Took me a while to try this.

    When I run the append query, I get the error:

    Click image for larger version. 

Name:	append error.png 
Views:	12 
Size:	9.0 KB 
ID:	39655

    If I ignore the error, nothing happens.

    At the moment this is what handles the saving:

    Code:
    Option Compare Database
    Option Explicit
    
    Private mSaved As Boolean
    
    Private Sub Form_Current()
    mSaved = False
    End Sub
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If mSaved = False Then
        Cancel = True
        Me.Undo
        Cancel = False
    End If
    
    If Me.NewRecord Then
            Me.[No] = Nz(DMax("[No]", "[tbl2019]") + 1)
    End If
    
    End Sub
    
    Private Sub btnSaveRecord_Click()
    
        mSaved = True
        Me.Requery
        Me.frmDataView.Requery
       
    End Sub
    This saves the record, clears the data entry form and refreshes the view of the "Data View" subform.

    Unfortunately, I can't share the database

    Thank you again for your time.
    Last edited by sessionone; 09-03-2019 at 05:01 AM.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The error is because you are trying to append the primary key field as part of your query.
    As a PK field must have unique values you get a key violation for each affected record.
    Solution: Remove the PK field from your append query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    sessionone is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17
    Thanks for the reply.

    If I remove the PK, every single single is being copied.

    I have a feeling my saving procedure is not correct for this purpose

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agreed. Something's wrong but if you can't share your database its difficult to know what exactly is wrong.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Make copy of database and remove confidential data.

    Cancel = False probably doesn't hurt but isn't necessary.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-22-2015, 11:01 AM
  2. Replies: 2
    Last Post: 08-22-2014, 01:20 PM
  3. Replies: 2
    Last Post: 03-01-2012, 03:11 PM
  4. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  5. Replies: 0
    Last Post: 12-16-2009, 01:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums