Solved Synchronzing Two Sub Forms to each other within a parent form

[John Lee]

Hi folks,

Your assistance would be most appreciated, I am in the process of creating a database and I’m having problems linking to subforms that are within a main form to each other so that they are synchronsing the two sub forms to each other.

I watch a video by Richard Rost over and over again and all I keep getting is a #Name! error. I have thoroughly checked all my master and child fields and haven't identified anything that suggests they are wrong

I have uploaded a test database for you to have a look at and hopefully you identify where I’m going wrong.

Below is a screen shot of the two sub forms that I am trying to synchronise with each other

They both work fine in synchronisation with the parent form.

What I am trying to do is where the versatility subject has been identified to an employee and the competence level has been also identified, that when I scroll up or down in the Versatility subject sub form the competencies scrolls up or down as well.

The test database is attached as well.

Figure 1 Figure 2

 

[Gasman]

I believe the way it is done generally is refer to subform A in a control on the main form, and link to that control on subform B.
That control on the mainform can be invisible.

Table lookups are also not recommended.

 

[John Lee]

I believe the way it is done generally is refer to subform A in a control on the main form, and link to that control on subform B.
That control on the mainform can be invisible.

Table lookups are also not recommended.

Hi Gasman,

I tried that several times and it just generates the #Name! error, but as your saying table lookup are not recommended, does that mean I need to change my lookups to something else?

 

[Gasman]

I took a quick look at your DB, and it seemed to remember left with right after amendments.
I believe you would need a unique number to link your subforms, not the employeeID

Why can't you just have comptencies in with the Versilatlity table.
I believe you need a junction table that would hold EmployeeID, VersalityID and Competency ID. Then you would just have one subform with two combos in the record for thos items?, unless you need to keep some sort of history of when whoever got to what.

Create your own lookups with queries.

However as long as you know how they work and understand them, then you can just use them, just that they are generally not recommeded.

I still have them in my very first DB that I created, and that DB is now complete. I am not going to change it for changes sake, but I do know how they work and understand them.
We get a lot of queries from people who do not.

The table that drives the whole DB of mine is called Links, and apart from the autonumber, every other field is a table lookup.

 

[Pat Hartman]

From your example, I can't tell what sync'ing means. There two uses of the concept. One is when you have a three level (or deeper) hierarchy Subform 3 is sync'd with 2 and 2 is sync'd with 1. Two is usually a continuous subform and three may or may not be. Sometimes the mainform needs to be continuous. in that case, you use an unbound main form and the technical main form is the highest level of three subforms.

The other is a two level struture where both subforms are sync'd with the main form AND scroll at the same time. So if you move the current record pointer to a new position on subform a, then subform b automatically positions to the same row.

Looking at your database, neither of these patterns seems to apply. What you are using as the second subform I think needs to be a combo on the first subform where you select which competency applies.

Let's start by fixing the database schema.
1. ALL tables need a primary key defined. Several of yours do not have one.
2. Define the relationships between the tables and enforce RI.
3. Remove any and all table level lookup fields.
4. Remove 0 as the default value for ALL Foreign keys. The Default for FK's must be null. Then if the FK is required, you mark the field as Required = Yes. 0 is never a valid value so allowing the field to default to 0 just causes errors that occur at runtime.
5. Optional but strongly recommended. Remove all the data type prefixes from the column names. This is unnecessary and interferes with intellisense because you have to type so many characters before you get to anything meaningful. Also, when you open tables or queries, all you see is lng or str and don't see anything meaningful which means that you have to expand any column just to read its name. Using prefixes on objects is used to organize our code to make it more readable and help us to avoid using the wrong object name.

 

[John Lee]

I took a quick look at your DB, and it seemed to remember left with right after amendments.
I believe you would need a unique number to link your subforms, not the employeeID

Why can't you just have comptencies in with the Versilatlity table.
I believe you need a junction table that would hold EmployeeID, VersalityID and Competency ID. Then you would just have one subform with two combos in the record for thos items?, unless you need to keep some sort of history of when whoever got to what.

Create your own lookups with queries.

However as long as you know how they work and understand them, then you can just use them, just that they are generally not recommeded.

I still have them in my very first DB that I created, and that DB is now complete. I am not going to change it for changes sake, but I do know how they work and understand them.
We get a lot of queries from people who do not.

The table that drives the whole DB of mine is called Links, and apart from the autonumber, every other field is a table lookup.
View attachment 117648

Hi Gasman, That sounds l like a plan to me, I did think about putting the competencies as a lookup in the versatility table, but then couldn't get it in my head how that would play out when linking that table to the employee table, but your response has given me the information I needed to see that it can work that way. Thank you very much for your assistance.

 

[Pat Hartman]

Do NOT use table level lookups to solve this problem. Use combos on your form. Please also review the rest of my suggestions before moving on. Having a firm foundation will help you to avoid issues going forward.

 

[mike60smart]

Hi folks,

Your assistance would be most appreciated, I am in the process of creating a database and I’m having problems linking to subforms that are within a main form to each other so that they are synchronsing the two sub forms to each other.

I watch a video by Richard Rost over and over again and all I keep getting is a #Name! error. I have thoroughly checked all my master and child fields and haven't identified anything that suggests they are wrong

I have uploaded a test database for you to have a look at and hopefully you identify where I’m going wrong.

Below is a screen shot of the two sub forms that I am trying to synchronise with each other

They both work fine in synchronisation with the parent form.

What I am trying to do is where the versatility subject has been identified to an employee and the competence level has been also identified, that when I scroll up or down in the Versatility subject sub form the competencies scrolls up or down as well.

The test database is attached as well.

Figure 1 Figure 2
View attachment 117645View attachment 117646

Are the Competencies supposed to be related to the Versatility Subject?

I should have asked "Does each Versatility Subject have 1 or More Competencies?"

 

[June7]

Here is a good article about syncing subforms https://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp
In this case, one subform data is actually parent to the other subform but this allows both to be viewed in Datasheet.

 

[John Lee]

Are the Competencies supposed to be related to the Versatility Subject?

I should have asked "Does each Versatility Subject have 1 or More Competencies?"

Hi Mike, the versatility subject can have a choice of three different competencies, but can never have more than one, an employee can have more than one versatility subject, which in turn can have a choice of one of the competencies.

So to clarify, an employee can have any number of versatility subjects linked to them and each of those versatility subjects will have a least one of the competencies assign to it according to how the line manager of that employee has determine that employee's competence against each versatility subject.

I did try to link the competencies at the table level, by creating a field that looked up the field in the tblversatiltiyRecordCompetencies table, but could show the competencies in a combo box at form level, because I couldn't get a second column to display and allow the ability to select the competency level assigned to that employee, I then tried a different approach by removing the strcompetenceLetter field from the tblVersatilityRecords table and creating a second combo box at form level in the same subform as the lngVersatilityRecordID field, but when I tried to set it up to look at the tblversatilityRecordCompetencies table the combo would offer me that option it kept on only offering the tblVersatilityRecords table.

Clearly I'm doing something wrong, I just need the ability that when a versatility subject for an employee is chosen, that they are also able to select the competency that the manager wants to assign to that particular employee and when ever the form view is opened, the versatility subject along with its assigned competency move up or down together when scrolling that employee's versatility record.

I managed to align the two records by creating a second sub form and then after watch Richard Rosts video on linking sub forms, tried that method to synchronise them, but it didn't work as I kept on getting the #Name! error in the text box that was supposed to link these two sub forms.

So whilst they are aligned they are no synchronised and therefore do not move together when scrolling up or down on either field, it also means that if a record needs to be removed that depending on which field you delete first the other remains, which of course makes this method unpractical and messy.

I hope this gives sufficient information on what it is I am trying to achieve.

Your assistance is most appreciated.

 

[mike60smart]

Hi John
Your Form should be linked as shown in the attached.

 

[Pat Hartman]

Hi Mike, the versatility subject can have a choice of three different competencies, but can never have more than one, an employee can have more than one versatility subject, which in turn can have a choice of one of the competencies.

Then you need to follow the directions I posted in #2. This isn't a sync'ing problem.

 

[Gasman]

Post #2 was my post Pat?
Do you mean post #5 ?

 

[Pat Hartman]

I did. Thanks.

 

[Pat Hartman]

Since there is only ONE competency for each subject, you need the competency letter to be stored in the subject record - there is NO additional table for this relationship since it is 1-many. Only a many-many relationship needs a separate junction table to hold the TWO 1-m relationships that define a m-m. Use a combo on the form to pick the letter. You can use list values in this particular combo. In your database, I would not create a separate table to define the competency letters.

Do NOT use lookup values at the table level. Use a combo on the form and please try to fix the other issues before moving on. The sample Mike posted is not what you need. It is for a m-m relationship. Sorry. I was looking at some random db,not the one Mike posted. Mike's sample is fine.