Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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

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
CompTech810
Asked:
CompTech810
  • 3
  • 2
1 Solution
 
peter57rCommented:
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
 
CompTech810Author Commented:
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
 
CompTech810Author Commented:
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
 
peter57rCommented:
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
 
CompTech810Author Commented:
Thanks!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now