Forum Discussion

HenryD's avatar
HenryD
Copper Contributor
Feb 11, 2025
Solved

Cant Create form Using 2 x Sharepoint Linked Tables

Hi I have 2 x tables Plant Orders and Plant Orders Details that are linked from Sharepoint. I have a user input Form whereby the Plant Orders Form is the Main Form and Plant Order Details is the Subf...
access
sharepoint
George_Hepworth's avatar
George_Hepworth
Silver Contributor
Feb 11, 2025

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. 

 

 

  • HenryD's avatar
    HenryD
    Copper Contributor
    Feb 11, 2025

    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.

      • HenryD's avatar
        HenryD
        Copper Contributor
        Feb 11, 2025

        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;

Resources