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, I want a copy of this record to go to the invoice detail table with the right invoiceID.
First table where the deytails are: called Detailfinance_tbl and copy of the record should go to table called invoicedetail table.
The fields that should be copied after selecting the invoiceId and clicking on the checkbox is is Amount,InvoiceID, and description
Is there a way to do this?
Microsoft Access
Last Comment
als315
8/22/2022 - Mon
als315
Create add query from source table to resulting, add conditiion for invoiceID (use builder, select your form and invoiceid combobox).
Try run it. If all will be working, you can run it from your checkbox with afterupdate event:
docmd.setwarnings False
docmd.openquery "YourQueryName"
docmd.setwarnings True
Jeffrey Coachman
<I want a copy of this record to go to the invoice detail table with the right invoiceID.>
If I am reading this correctly, this will create Duplicate data.
What is the reason why this data cannot be "Linked" to the Invoice detail table?
HappyAlways
ASKER
Actually, it will select the right records to be part of the invoice details table. The invoice detail table is editable.
This worked perfectly, but 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:
"if i select the checkbox in 5 records, it will copy them total 20 times"
You have something wrong in logic. May be you can upload your DB with sample data?
HappyAlways
ASKER
i don' think I have anything wrong with the logic. I am not sure if it has something to do with the datasheet subform.
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
als315
How many records you see, if GLApend_qry is opened manually (from designer)? Form N_ProjectSub_frm should be also opened.
Try run it. If all will be working, you can run it from your checkbox with afterupdate event:
docmd.setwarnings False
docmd.openquery "YourQueryName"
docmd.setwarnings True