none
VBA code to copy record RRS feed

  • Question

  • Hi All

    Due to structure data is the same. So I just update first record when the form is opened then I would like to use copy command to copy it for new record. we only need change Line for register. The copy will perform 2 task copy and update on below form. But when i build code it can not execute duplicate data. these duplicated records have the same data except primary key. and finally it operates incorrectly.

    Here is my VBA code:

    Private Sub CmdNew_Click()
    
    On Error GoTo Err_cmdDuplicate_Click
    
    
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdCopy
        DoCmd.RunCommand acCmdRecordsGoToNew
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdPaste
    
    Exit_cmdDuplicate_Click:
        Exit Sub
    
    Err_cmdDuplicate_Click:
        MsgBox Err.Description
        Resume Exit_cmdDuplicate_Click
        
        Me.Refresh
    End Sub

    here is error when click on Copy


    Friday, June 8, 2018 6:43 AM

Answers

  • I uploaded my version here: https://1drv.ms/u/s!AnmKsZFxs8_Kh6hQzfbC2w4rP2pKXA

    I added a subform to select which lines you want to include, and the Production Date etc. controls are all unbound.


    -Tom. Microsoft Access MVP

    • Marked as answer by Nghi Trinh Saturday, June 9, 2018 5:04 PM
    Saturday, June 9, 2018 4:13 PM
  • Search for "access how to set multicolumn unique index" and you will find out.

    -Tom. Microsoft Access MVP

    • Marked as answer by Nghi Trinh Saturday, June 23, 2018 3:26 PM
    Saturday, June 23, 2018 3:04 AM

All replies

  • It appears as if you have a SET of records AL07 through 16 and want to copy all of them to the next day.

    Then why not do that with one append query? Off the cuff it would be something like:

    insert into myTable(Line, ProductionDate, Group, Shift, …)
    select Line, maxdate+1, Group, Shift, …
    from myTable
    where ProductionDate = maxdate

    You can replace maxdate by a DMax function call - see help file

    This will create the entire set of records for the next day.


    -Tom. Microsoft Access MVP

    Friday, June 8, 2018 1:25 PM
  • Hi,

    Another option is to set the default value for the new record based on the record you just entered. For example, in the AfterUpdate event of ProductionDate, you could try something like:

    Me.ProductionDate.DefaultValue = """" & Me.ProductionDate & """"

    Hope it helps...

    Friday, June 8, 2018 2:38 PM
  • Hi Guy

    I already tried but it appear fault when i click Save with below code:

    Private Sub Command90_Click()
    DoCmd.Save
    Me.Refresh
    Me!FrmSubHeaderInput.SetFocus
    DoCmd.GoToRecord acActiveDataObject, , acNewRec
    End Sub
    And i already set default Value for all field like Line, ProdDate, Group, Shift.. as your instruction. It can not copy or default value by last record. Help me check




    Friday, June 8, 2018 5:39 PM
  • Hi,

    Can you share a sample copy of your database with test data?

    Friday, June 8, 2018 5:48 PM
  • Thank Tom. If i have total  different Lines need to register with same production date, group, shift...How can I do it one time or line by line.
    Saturday, June 9, 2018 1:11 AM
  • Hi Guys

    Here is my DB. One time i want to register all 12 lines in the same day, same group and same shift. only different  line. so i want a smart way to do that for end users. If not they must input 12 times, even more if we have more running line.

    https://drive.google.com/open?id=1XP_k9mw85CanosGc6J5g3KS5SoIxaISm

    Thank you in advance


    Click here
    • Edited by Nghi Trinh Saturday, June 9, 2018 1:33 AM
    Saturday, June 9, 2018 1:32 AM
  • I'm looking at your database and I have some questions.

    1. You wrote " i want to register all 12 lines in the same day", but there are only 11 records in Tblline.

    2. The bottom subform has a recordsource of "select top 9 ...". Why 9 if your objective is to insert 11 or 12 records?

    3. Both subforms are bound to the same TblHeader, as is the parent form. Why?


    -Tom. Microsoft Access MVP

    Saturday, June 9, 2018 3:25 PM
  • Hi Tom

    1. I expect user will register enough line. But this case i just give you example. number of record is  not important. the point is how to perform as my expectation.

    2. Select top 9 that means i just want to show 9 newest records. this number is not fix. i can adjust base on number of LINE.

    3. 2 Subforms have different function. one for input and one for update data when one new record is registered

    Saturday, June 9, 2018 4:00 PM
  • Thanks, that's fine. Stand by; I will have something for you shortly.

    -Tom. Microsoft Access MVP

    Saturday, June 9, 2018 4:04 PM
  • I uploaded my version here: https://1drv.ms/u/s!AnmKsZFxs8_Kh6hQzfbC2w4rP2pKXA

    I added a subform to select which lines you want to include, and the Production Date etc. controls are all unbound.


    -Tom. Microsoft Access MVP

    • Marked as answer by Nghi Trinh Saturday, June 9, 2018 5:04 PM
    Saturday, June 9, 2018 4:13 PM
  • Thank you so much. it is great :)

    • Edited by Nghi Trinh Saturday, June 9, 2018 5:07 PM
    Saturday, June 9, 2018 5:04 PM
  • Hello Tom

    Give me one more question. In case some one submit twice time or more. The record will duplicate unnecessary. So how can prevent this issue. I wanna alert for user when record is duplicated and not allow to save them. I am looking forward to hearing your help. Thank in advance

    Best regards

    Nghi

    Thursday, June 21, 2018 9:12 AM
  • You prevent duplication by having a unique index on the combination of fields in the table that logically cannot be duplicated. Then the db engine will refuse to add the second row, and a runtime error will occur which you can trap and tell the user not to do that.

    -Tom. Microsoft Access MVP

    Thursday, June 21, 2018 2:19 PM
  • Hi Tom

    How I can set a unique index on the combination of fields. I already tested but is is not OK. We can not change setting because some fields is duplicate. You can see the above data: production date, shift and group is the same, only different line. But when we change shift then Line and production date is repeated again...

    Friday, June 22, 2018 7:34 AM
  • Search for "access how to set multicolumn unique index" and you will find out.

    -Tom. Microsoft Access MVP

    • Marked as answer by Nghi Trinh Saturday, June 23, 2018 3:26 PM
    Saturday, June 23, 2018 3:04 AM
  • I got it. Thank Tom so much :)
    Saturday, June 23, 2018 3:25 PM