Solved

Copy record from on table to another and change a few fields before add.

Posted on 2012-04-06
5
361 Views
Last Modified: 2012-06-27
Ok, I am pretty lost here......  


The table that I'm reading from is SupplierRFQ, it has one record.  I want to copy that one record(all fields) to SupplierRFQSlt which is identical to the SupplierRFQ table with the exception of a few fields.  I am using a query that loops through a contact database that has contacts.  I need to add the record from SupplierRFQ to SupplierRFQSlt for each contact in the query and populate the additional fields(SupplierRFQContact) in SupplierRFQSlt during that add.

The query loop I have in place is working fine with the exception of the above question.

Please provide code using my table names and field name, I'm new to VBA.......
0
Comment
Question by:CompTech810
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 37815967
Based on your description ther are is no code needed to do this.  The query grid will handle all this.
You just use an append query.

Start by creating a select query from the SupplierRFQ table and the Contacts table, without joining them in the query grid.

You can select whichever fields you want from each table and then when you convert the query to an append query, make sure the fields all map to the required destination.
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37815998
Ok, I'll take a look at this.  What would be perfect is what I have below.

CurrentProject.Connection.Execute "INSERT INTO SupplierRFQSLT SELECT *, 'Testing' As SupplierAbbreviation FROM SUPPLIERRFQ"

Is there a way that this can be done?
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37816704
That worked!!

But one of the fields I populate with a combination of fields, that value = the file location field.

How do I do that, can  do it in the query field then use 'FileLocation' field in the append area?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37816763
In the field cell enter the expression that combines the sources, like...

FullName:FirstName & " " & LastName

You can then remove the individual fields if they are not going anywhere.
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37816971
Thanks!!
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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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