Forum Discussion
Cant Create form Using 2 x Sharepoint Linked Tables
Hi,
This is easily done with a query but it is not editable when running the query on both Tables.
You should post the SQL text of the query.
Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK
First, SharePoint lists require the use of Lookup fields to enforce Referential Integrity between related tables. This is, in fact, the only time I'd even consider using Lookup fields in tables, i.e. in Linked SharePoint lists.
So, to ensure that your tables are properly set up, confirm that this is the way they are designed in SharePoint. Access may or may not accurately show the relationship in the Access Relationship Window, but that is not the critical factor. It's how the relationship is set up and enforced in SP that matters.
Second, I can't quite picture how you'd go about creating a form in datasheet view that would allow you to manage both the records in the one side table and the records in the many side table. Please explain how that might look.
George, the table used by the Form has the following Fields
The table used by the subform has these fields
Only the Order Number field is common to both.
I set up these tables and forms a number of years ago so i have no doubt errors have been made! This is what i see when i go into Sharepoint List settings
What i want to be able to do is run a search by Contract Number then Supplier that will give me all the individual orders (Order Number) for those searches, I then want these resulting records listed in a datasheet view in order that these can be edited. This is easily done with a query but it is not editable when running the query on both Tables.
Hi,
This is easily done with a query but it is not editable when running the query on both Tables.
You should post the SQL text of the query.
Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEKKarl, what i should have said was i have a number of querys that i can run that give me the information i require but these are not editable, ill post the SQL of one below. This will probably not make any sense as it uses calculations based on another query.
SELECT [Plant Orders].[Contract Number], [Plant Orders].[Order Number], [Plant Orders].Supplier, Contracts.[Contract Name], Plant_Weeks_on_Hire_Query_Rev_A.[Order Number], Plant_Weeks_on_Hire_Query_Rev_A.Quantity, Plant_Weeks_on_Hire_Query_Rev_A.[Date On Hire], Plant_Weeks_on_Hire_Query_Rev_A.[Date Off Hire], Plant_Weeks_on_Hire_Query_Rev_A.[Off Hired], Plant_Weeks_on_Hire_Query_Rev_A.[Weeks on Hire], Plant_Weeks_on_Hire_Query_Rev_A.[Hire Rate], Plant_Weeks_on_Hire_Query_Rev_A.[Cost of Hire], Plant_Weeks_on_Hire_Query_Rev_A.Code, Plant_Weeks_on_Hire_Query_Rev_A.Description, Plant_Weeks_on_Hire_Query_Rev_A.[Off Hired]
FROM (Contracts INNER JOIN [Plant Orders] ON Contracts.[Contract Number] = [Plant Orders].[Contract Number]) INNER JOIN Plant_Weeks_on_Hire_Query_Rev_A ON [Plant Orders].[Order Number] = Plant_Weeks_on_Hire_Query_Rev_A.[Order Number]
WHERE ((([Plant Orders].[Contract Number])=[Please Enter Contract Number]) AND ((Plant_Weeks_on_Hire_Query_Rev_A.Code)="LPL" Or (Plant_Weeks_on_Hire_Query_Rev_A.Code)="SPL" Or (Plant_Weeks_on_Hire_Query_Rev_A.Code)="SURV" Or (Plant_Weeks_on_Hire_Query_Rev_A.Code)="Formwork"))
ORDER BY Plant_Weeks_on_Hire_Query_Rev_A.[Date On Hire] DESC;Hi,
Well, it's not exactly what we would expect based on your previous description with just "2 tables". So it's harder to "debug".
First, you need to make sure that the query used in this SQL text/query (let's call it B) Plant_Weeks_on_Hire_Query_Rev_A (let's call it A) is updatable.
If A is not updatable, then B cannot be either if you include A with a JOIN. You would either have to make A updatable or include it differently in B (subquery or domain function).
If A is updatable, then you should start with B very simply, with 1 table, test updatability, then add 2nd table, test updatability, then add query A, test updatability. This is the best way to find the cause.
After that, you can look for a solution.Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK