Solved

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

Posted on 2012-04-06
5
358 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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

821 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