Solved

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

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

744 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

10 Experts available now in Live!

Get 1:1 Help Now