This tutorial shows how to create an import to add new products and new suppliers to the Products and Suppliers table in one go.
Note: this example uses several techniques and concepts which will usually be familiar to MS Access developers only: relationships, elementary VBA code. So this is not for the faint of heart.
In the Relationships window we see how the Suppliers ( parent) and Products ( child) data are related.
This schema requires for each NewProductsWithSuppliers record to first add a Supplier if it does not yet have a record in the database, followed by adding the Products records, using the newly created SupplierID.
This example shows the following key features of Import and Validate:
The import example can be found in the MyImport demo database: 
Start > All Programs
> Import and Validate > MyImports.mdb
In the Import an Validate form the Select Source Table tab gets focus.
After selecting the source table, the Select Destination Table tab gets focus.
We now want to select the first Destination Table, in our case this will be table Suppliers. The Supplier must be known before Products can be added because of the parent-child relationship (referential integrity).
The [Select Destination Table] dialog appears
In the Transfer Pane rows appear for the Suppliers destination table. You will notice there is initially no match with the Supplier. The reason for this is there is practically no overlap between the Suppliers table and the NewProductsWithSupplier table.
Only Companyname for the supplier is directly available, and that can't be matched automatically, so we have to match that manually:
The New Value cell gets filled with the name of the supplier
We have choosen Add & Replace as the way to add records, because this way if the Supplier already exists, this does not result in an error, and we will still have access to the SupplierID, which we will need for adding Products. However we don't want to change the supplier name:
To find what is the SupplierID of a company, we use a simple Visual Basic function (VBA), applied to the supplier's CompanyName.
The New Value field again gets the name of the company. This time however, it turns orange. This indicates the value is not acceptable. The reason is only numeric values are acceptable for the SupplierID field. We will fix this by making a function that looks up corresponding SupplierID.
A column 'Evaluate' appears next to the Source column.
A dialog appears 'Do you want a VBA evaluation function to be created?'
The above has two effects:
It is now your task to change the dummy function that was automatically created into one that looks up the Supplier ID corresponding to the Supplier companyname.
The green Example code suggests how you could alter the function to make it return what you need. In our case indeed we will be using the MS Access' DLookup function
Function Supplier_Evaluate( str As String) As
Variant
On Error
Resume Next
Supplier_Evaluate = DLookup("SupplierID", "Suppliers", "CompanyName='" &
str & "'")
End Function
and close Access VBE
If all's well, you should now see the SupplierID appear instead of the CompanyName in the New Value column.
See Remove unused rows from the Transfer Pane.
We now want to select the second Destination Table, in our case table Products.
The [Select Destination Table] dialog appears
In the transfer pane, an again matching is done of Source and Destination fields. Note that on row 231, the Source column value is 216.Suppliers.SupplierID. This tells the program to pick up the New Value of line 216. In case a new Supplier is added, the newly created SupplierID is automatically picked up and used to link the added new Products.
The record was added, the next record was selected.
If you open table Products, you will see 'Vegie-spread' was added. Close the table again.
The records for the new supplier and product were added, the next record was selected.
If you open tables Supplier and Products, you will see 'New Kid on the block' and 'Tofu' were added.