'Morning all, I let a novice work for the last few months on developing a customer record database. One of the things that he did incorrectly was to set up a relationship between TBL_Customer and TBL_Address using a lookup field set to allow multiples. This is creating a lot of problems for us as we try to create forms to input / edit / add new records.
My question for the forum is, if I were to create a new junction table to replace this relationship, how can I update the table with the exsisting FK field's values?
Full Version: Converting Allow Multiple Lookup Field To Junction Table
Hi Scot,
You should be able to use a recordset and loop through the values in the field and then add them to the junction table one at a time.
Just my 2 cents...
PS. What sorts of problems were you running into?
You should be able to use a recordset and loop through the values in the field and then add them to the junction table one at a time.
Just my 2 cents...
PS. What sorts of problems were you running into?
Ugh, Field.value was throwing me off. MS web help got me there though.
CODE
INSERT INTO Jnct_CustAddy ( CustomerID, AddressID )
SELECT TBL_Customer_Addy.CustomerID.Value, TBL_Customer_Addy.CustAddyID
FROM TBL_Customer_Addy
WHERE (((TBL_Customer_Addy.CustomerID.Value) Is Not Null));
SELECT TBL_Customer_Addy.CustomerID.Value, TBL_Customer_Addy.CustAddyID
FROM TBL_Customer_Addy
WHERE (((TBL_Customer_Addy.CustomerID.Value) Is Not Null));
Hi Scot,
Glad to hear you got it sorted out. Good luck with your project.
Glad to hear you got it sorted out. Good luck with your project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.