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
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 AEK
Karl, 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 AEKHi Karl. There are only two tables as below.
I can run an editable Query on each of these tables individually but if i try to run a query on both tables i get the message "one or more of these tables is not related". In simple terms i want to be able to filter the subform content by [contract] then [supplier] from the main form and have this editable for updating. Apologies i should have known that the SQL I sent would confuse things as it used another Query.