VBA: Insert query skal slå id-reference op i anden tabel

Where does the data come fromwhich has to be inserted into a table?



If you have two table which are related and you want to insert the data into a third table then maka e query where you select the data from th etwo tables first. Then use this query in another query to insert into the third table.


If you only have two tables then it sounds to me as though you want to update on eof the tables with data from the other table.

Maybe some more information might help

I have three tables:
Sales: SaleID, Quantity, ItemID, CustomerID, Date
Customers: CustomerID, CustomerName
Items: ItemID, ItemName, ItemPrice

I also have a text file that I read line by line and split into the following variables:
ImportedQuantity, ImportedCustomerName, ImportedItemName, Date

Now I want to get the corresponding CustomerID and ItemID for CustomerName and ItemName, so I can do an insert into the Sales table.

But the text file has nothing to do with INSERT does it?

OK, I am guessing that the text file maybe has somethng to do with the insert.

What I suggest i syou imprt the text file into a temporary table then you use this in a query so that you can related the item and customer table. Then you can use this query in an append query to sales table.

You cant (easily) do this in a single query.

Following example from http://groups.google.com/group/microsoft.public.access/browse_thread/thread/1e749029e44e6ad9/7f99792385e3d85c#7f99792385e3d85c made it work for me. Yay!


Function GetMailList()
Dim MyDB As DAO.Database, MyRec As DAO.Recordset, MyList As String
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("Select email From TableName")
While Not MyRec.EOF  ' Loop trough the table
    MyList = MyList & ";" & MyRec![email]
    MyRec.MoveNext
Wend
MyList = Mid(MyList, 2)

' use you code here with the mail list ceated

MyRec.Close
MyDB.Close
End Function

I'm obviously missing something, what does the link have to do with your question?

It made things work.

Well thats whats import isnt it :o)

import = important :o)