Forum Discussion
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 Subform. I want to combine the records in these tables as an editable form in a datasheet view. If i use the wizard to create a form between these tables i get the message "one or more of these tables is not related".
If i check the relationships on Access it is true there are no links between these tables. However the Master Child Relationship in the database is working correctly as the common field between the two tables - Order Number - updates in the Order Details table on Sharepoint but is not entered in the Subform.
Ultimately what i am trying to achieve is a datasheet that can be edited that contains all the records for a particular job without having to scroll through each order number.
Any suggestions?
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
Thanks for your help, the issue as advised was that there was no lookup field in SharePoint List to create the one to many relationship that would have allowed the Query to Run. When i originally created the Main and Subforms this was done without a lookup column for the identical Fields.
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 settingsWhat 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
