Hi. I wanted to create a tabular form to allow a user to update a field (colour) in a 3rd party DB I use. In order to find the correct rows and get all the info I need to show the user I need a nested query:
Code:
SELECT DISTINCT P4.[Product reference], P4.[Short description], P4.[nParentSectionID], P4.[Price], TONYProductHistoryTable.[Product Reference Num], P4.sValue, P4.[Image FileName], "C:\Users\tonyg\Documents\SellerDeck 2013\Sites\Site1\" & P4.[Image FileName] As ImagePathFROM (SELECT Distinct Q4.SContentID, Q4.sValue, Product.[Product Reference], Product.[Short description], Product.[nParentSectionID], Product.[Price], Product.[Image FileName]
FROM Product INNER JOIN
(SELECT UserDefinedProperties.SContentID, UserDefinedProperties.sValue FROM UserDefinedProperties LEFT JOIN Variable on Variable.nID = UserDefinedProperties.nVariableID Where Variable.sName = 'Colour' and UserDefinedProperties.SContentID Not Like '*!*') As Q4
on Product.[Product Reference] = Q4.SContentID
where ((Product.[Product reference]) Not Like '*!*') AND Not IsNull(Product.[Short description]) AND (Product.[bSuppressHtml] <> -1)) AS P4 LEFT JOIN TONYProductHistoryTable ON (val(P4.[Product reference]) = TONYProductHistoryTable.[Product Reference Num])
WHERE (TONYProductHistoryTable.[ProductCategoriesChecked] = 0);
This works fine and brings me back all the info I need into the form using the query.
My issue is I want the user to be able to look through each row (there are hundreds) and select a colour for each product. I cant see a simple way to write back the changed values into the field "UserDefinedProperties.sValue".
If it was not a nested query then I guess updating the field in the form would also update the underlying field in the table. I need to simulate this process or have a big save button on the form that can take write back the data. Either way is ok but I am struggling to know the easiest/most efficient way to do it?
Any ideas would be appreciated?
many thanks
Tony