Solved

records rom one table to another

Posted on 2011-03-10
8
245 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:HappyAlways
  • 4
  • 3
8 Comments
 
LVL 39

Expert Comment

by:als315
ID: 35094383
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35096707
<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?
0
 

Author Comment

by:HappyAlways
ID: 35097824
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:

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.

How can I make it work only to select the one that has the checkbox?
0
 
LVL 39

Expert Comment

by:als315
ID: 35098334
"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?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:HappyAlways
ID: 35098736
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
0
 
LVL 39

Expert Comment

by:als315
ID: 35105677
How many records you see, if GLApend_qry is opened manually (from designer)? Form N_ProjectSub_frm should be also opened.
0
 

Author Comment

by:HappyAlways
ID: 35107897
I see the correct number. 5 records
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 35108288
Query should add only 5 records. May be it is atarted more then once?
Try to comment line:
DoCmd.SetWarnings False
You will be asked about every query.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now