Issues with forms populating based on relationships after migrating backend to SharePoint

Question

Hi,

I have recently created a data collection tool in Microsoft Access and the backend (tables) has been migrated to SharePoint. After migrating the backend to SharePoint, I am having a slight issue with the behaviour of my forms on the front end.

I have an appointment details form where a user can enter details such as client ID, session date, attendance, session time, duration etc. Within this appointment details form, I have a tabbed subforms containing the different outcome measures that the user could collect data for in each appointment. In one tab for example, I have a GBO form where the user can enter GBO scores. The GBO table has a relationship with the appointment details table, so client ID and session date automatically populate based on the information entered in the parent form. In another tab, I have ORS/CORS. This ORS/CORS tab contains a combo box where the user can select the ORS form or the CORS form depending on which measure is appropriate (these measures are age specific). I have the following code to ensure the unbound subform populates with the correct form based on the selection from the combo box:

Private Sub Combo188_AfterUpdate()

If Me.Combo188 = "ORS" Then 

    Me.Child190.SourceObject = "Form.ORS Data Entry Form"  

ElseIf Me.Combo188 = "CORS" Then 

    Me.Child190.SourceObject = "Form.CORS Data Entry Form" 

End If 

End Sub

When the ORS or CORS form displays, the client ID and appointment data do not populate as they do with the tabs that do not have the combo box e.g. GBO. I did not have this issue before I migrated the backend to SharePoint and I have checked that the relationships have been correctly established in the backend (I had to re-establish all the relationships after migrating the backend to SharePoint).

Does anyone have any advice/idea of what could be causing this issue?

Thanks!

Answer 1

Ok, the child table column is NEVER a auto number, but a simple plain jane long number column. So, the above screen shot looks "mostly" correct.

I don't see that join has having been enforced. I did not think that was required, but it would not hurt to enforce that join. And might as well make it a left join, since in theory the child table record of "Session information" does not necessary have to exist. So, while you drawn a join line, I don't see it as a enforced relationship, and it should be.

In other words, your join line should look like this:

So, note in above, I see the "omega" sign - that means you enforced the relationship. Now the right arrow? That means a left join, and you probably don't need that, but it still a good idea. So, the relationship should be enforced.

Hence double click on the join line, and you should have settings like this:

So, SharePoint tables? Yes, they ONLY support a master to child record join on a PK column (in the parent table), and standard number column in the child table. You can NOT use text columns for these joins.

So, while that YT video was using web tables, you can and should be able to use the standard relationship window, and your joins should look like above.

Note that the join should be left, (it does not have to be, but it should). So, in above, we click on the join type and have this:

So, yes, the relationships need to be "enforced" for the up-load to work correctly.

Answer 2

Hi Albert,

Thank you very much for your response - it is very helpful. I have addressed the first issue of linking the master/child fields correctly in my form, but I am struggling with resolving the second issue. I have watched your YouTube video, (Migrating Access tables to Office 365) and tried to re-establish my relationships to ensure they are correct before transferring to SharePoint, however they are not establishing correctly.

If I give the example of two tables, Referral Information and Enter Session Information. I need Referral Information to link with Enter Session Information by Client ID and User ID. The problem I am having is that Access is setting my primary key as ID, despite me having indicated that Client ID should be the primary key. I have shown a screenshot of this below. Do you have any idea why this may be?

Is the problem that I am trying to create a relationship that is not with a autonumber primary key? And if so, do you know if there is any way I can establish relationships between fields that are not autonumber primary keys?

If this is a limitation to having a backend on SharePoint using SharePoint lists - are you able to suggest any alternative ways that I can have a backend that multiple users can access over a cloud-based system?

Answer 3

The link master and child settings are what places the pk of the main record into the child record you add (Access can't guess which PK to put in that child record to link back to the parent record).

However, while you want to check the above, and even without SharePoint, those settings must be set correct, else the sub form can't know what records to display that belong to the parent record.

What I would first check is if you migrated the data correctly in the first place. Remember, when you migrate data to SharePoint tables, the PK values are changed, and thus that means the child records column used to link back to that one parent record ALSO must be changed.

What this means is that BEFORE you send the table(s) in question to SharePoint, you must be 100%, 200%, and 300% sure that you had valid table relationships setup. If those relationships were not setup before you migrate the tables, then the PK and FK values will become messed up, since as noted, migration to SharePoint tables means your PK values used will change. Since they change, then during the migration, then the child table link field (the so called foreign key) ALSO has to be change. The migration wizard will automatic handle this re-numbering of these PK and FK values, but this ONLY occurs if you had setup the relationships correctly before you migrate. Without a correct relationship setup, then the migration process can't know what columns to update automatic for you DURING this migration process.

When migrating to SQL server, this issue does not exist, since the PK (and FK) values are not changed. They are unfortunately ARE changed during the migration, and hence a VERY solid and good setup of relationships MUST exist before you migrate the data.

So, there are two issues:

The sub form control MUST have the correct link master and child settings, since as noted, that's how Access "knows" what FK value to insert into that new child row. And those settings are also how access "knows" what child records to display. In fact, these link and master column settings work EVEN when you don't have relationships setup. So, these settings usually default to what you have in the relationships table, but they are really much "independent" of each other.

So, 2 issues here have to be resolved:

The link master/child settings for those sub forms must be setup correct.

The 2nd issue is that during the migration, if you did not have the relationships setup correctly, then after a migration, all your links from parent to child tables will be lost (they are mumbo jumbo messed up, and you have to migrate a again to fix this).

Answer 4

Hi Tom, thank you very much for your response.

Do you still have LinkMasterFields and LinkChildFields properties set for the subform?

They're not, but this is because the subform is unbound until a selection is made from the combo box. I think (though correct me if I am wrong) that as the subform is unbound, I cannot have LinkMasterFields and LinkChildFields properties set for the subform? This may be the issue, but I did not have this problem before I moved the backend to SharePoint.

Answer 5

Do you still have LinkMasterFields and LinkChildFields properties set for the subform?

Also; some data sources such as SQL Server do not populate their Autonumber columns when the first character is entered in the record, unlike Access. This is one of the areas that sometimes needs attention from a programmer when upsizing the back-end. It's conceivable SP works the same way.