VBA code to copy record

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

Answer 1

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

Answer 2

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...

Answer 3

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

Answer 4

Hi,

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

Answer 5

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.

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

Thanks, that's fine. Stand by; I will have something for you shortly.

---

-Tom. Microsoft Access MVP

Answer 10

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

Answer 11

Thank you so much. it is great :)

Answer 12

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

Answer 13

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

Answer 14

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...

Answer 15

Search for "access how to set multicolumn unique index" and you will find out.

---

-Tom. Microsoft Access MVP

Answer 16

I got it. Thank Tom so much :)