Copy records

[Eddyoftheyear]

Question: I have a detail finance data in a table. I added a checkbox and combobox that link to invoice ID table, when the user select invoiceID from the combobox and ceck the checkbox, copy of this record to go to the invoice detail table with the right invoiceID.

So I created apend query and worked nicly. and I placed

afterupdate event for the checkbox:
docmd.setwarnings False
docmd.openquery "Details_qry"
docmd.setwarnings True

The only problem I am facing is that when select invocie number from combobox and click on the checkbox, it copy the records multiple time. example:

Inv# amount desc
4403 50.00 food
4403 70.00 drink
4403 20.00 car
4403 80.00 train

if i select the checkbox in 5 records, it will copy them total 20 times. If I click on one of the records, it copies them once.

Howe can I make this work correctly?

 

[dhookom]

You might want to share the SQL view of Details_qry.

Duane
Hook'D on Access
MS Access MVP

 

[Eddyoftheyear]

The query is :

INSERT INTO InvoiceDetails_tbl ( InvoiceID, Total, Description, UnitTypeID, UnitPrice )
SELECT GLDetails_tbl.InvID, GLDetails_tbl.Amount, GLDetails_tbl.[CO Doc Line Item Txt], GLDetails_tbl.[Cost Element], GLDetails_tbl.Amount1
FROM GLDetails_tbl
WHERE (((GLDetails_tbl.InvID)=[Forms]![N_ProjectSub_frm]![Gldetails_subfrm].[Form]![InvID]));


Private Sub Invoice_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenQuery "GLApend_qry"
DoCmd.SetWarnings True

End Sub

 

[dhookom]

First, I wouldn't use a check box. I think a command button would be more appropriate. Your query will append all records with the same InvID value. Is this what you want or do you want something on a more detailed level.

I typically use an outer join to the target table so I don't get duplicates.

Duane
Hook'D on Access
MS Access MVP