Synchronise subforms

[Guest]

Hello,
I've encounter a problem with a subform.
Every time I scroll trough the records on my main form, I expect the data in
the subforms will be adjusted automatically.
I used the toolbox to add subforms onto the main form.
The child and master are set up for these subform that uses data out of
another table. I also setup the relation via the Relationships.
Here is the query that selects the data:
SELECT Tbl_1_BSPOL76.plannr, Tbl_1_BSPOL76.datsnpl, Tbl_1_BSPOL76.aantal,
Tbl_1_BSPOL76.kwal6
FROM Tbl_1_BSPOL76
WHERE (((Tbl_1_BSPOL76.mabc)=[Forms]![Frm_1_Minfs]![Mabc8]));

Frm_1_Minfs is my main form.
After scrolling ttrough the table and positioning the mouse on the data area
of the subform and push F9 to refresh, the correct data does appear.

What can be wrong here? How do I get it synchronised automatically?

 

[Stefan Hoffmann]

hi,

I've encounter a problem with a subform.
Every time I scroll trough the records on my main form, I expect the data in
the subforms will be adjusted automatically.
Here is the query that selects the data:
SELECT Tbl_1_BSPOL76.plannr, Tbl_1_BSPOL76.datsnpl, Tbl_1_BSPOL76.aantal,
Tbl_1_BSPOL76.kwal6
FROM Tbl_1_BSPOL76
WHERE (((Tbl_1_BSPOL76.mabc)=[Forms]![Frm_1_Minfs]![Mabc8]));

Add in the main form Current event SubFormControlName.Form.Requery.

What can be wrong here? How do I get it synchronised automatically?

Take a closer look at the two properties below source object on the
second page in the property editor for your subform control.


mfG
--> stefan <--

 

[Guest]

HI,

Can I jump into this question with my own smiliar one. I hav two child
subforms that I want to syncronize so that when scrolling vertically one of
them, the other one follows. Both share the same foreign key linking them to
the master form.

/Leif S.
--
Systems Analyst

hi,

I've encounter a problem with a subform.
Every time I scroll trough the records on my main form, I expect the data in
the subforms will be adjusted automatically.
Here is the query that selects the data:
SELECT Tbl_1_BSPOL76.plannr, Tbl_1_BSPOL76.datsnpl, Tbl_1_BSPOL76.aantal,
Tbl_1_BSPOL76.kwal6
FROM Tbl_1_BSPOL76
WHERE (((Tbl_1_BSPOL76.mabc)=[Forms]![Frm_1_Minfs]![Mabc8]));

Add in the main form Current event SubFormControlName.Form.Requery.

What can be wrong here? How do I get it synchronised automatically?

Take a closer look at the two properties below source object on the
second page in the property editor for your subform control.


mfG
--> stefan <--

 

[Marshall Barton]

I've encounter a problem with a subform.
Every time I scroll trough the records on my main form, I expect the data in
the subforms will be adjusted automatically.
I used the toolbox to add subforms onto the main form.
The child and master are set up for these subform that uses data out of
another table. I also setup the relation via the Relationships.
Here is the query that selects the data:
SELECT Tbl_1_BSPOL76.plannr, Tbl_1_BSPOL76.datsnpl, Tbl_1_BSPOL76.aantal,
Tbl_1_BSPOL76.kwal6
FROM Tbl_1_BSPOL76
WHERE (((Tbl_1_BSPOL76.mabc)=[Forms]![Frm_1_Minfs]![Mabc8]));

Frm_1_Minfs is my main form.
After scrolling ttrough the table and positioning the mouse on the data area
of the subform and push F9 to refresh, the correct data does appear.

What can be wrong here? How do I get it synchronised automatically?

It sounds like you're using two separate mechanisms to sync
the main form and the subform. I would expect the Link
Master property to be Mabc8 and the Lick Child property to
be mabc. That should be all you need. If it is, then get
rid of the WHERE clause in the query.

If there is some reason you have not yet explained for using
the WHERE clause, then you should Requery the subform in the
main form's Current event.

 

[Stefan Hoffmann]

hi Leif,

Leif S wrote

Can I jump into this question with my own smiliar one. I hav two child
subforms that I want to syncronize so that when scrolling vertically one of
them, the other one follows. Both share the same foreign key linking them to
the master form.

This needs some coding, place the following code in both subforms on
current event:

Private Sub Form_Current()

Dim rs As DAO.Recordset

If (Not IsNull(Me![ID])) And _
(Not IsNull(Parent.OtherSubFormControl.Form![ID])) And _
(Me![ID] <> Parent.OtherSubFormControl.Form![ID]) Then
Set rs = Parent.OtherSubFormControl.Form.RecordsetClone
rs.FindFirst "ID = " & Me![ID]
If Not rs.NoMatch Then
Parent.OtherSubFormControl.Form.Bookmark = rs.Bookmark
End If
End If

End Sub


mfG
--> stefan <--

 

[Guest]

Hello Stefan,

I put some extra code in the current event of the main form:
Private Sub Form_Current()
'Frm_1_Inve.Form.Requery

SubFormControlName = ("Frm_1_Inve")
SubFormControlName.Form.Requery
End Sub

It always pops up an error: Run-time error'424':
Object required.
Sorry to ask, but I'm quite a novice in this. What do i do next?

hi,

I've encounter a problem with a subform.
Every time I scroll trough the records on my main form, I expect the data in
the subforms will be adjusted automatically.
Here is the query that selects the data:
SELECT Tbl_1_BSPOL76.plannr, Tbl_1_BSPOL76.datsnpl, Tbl_1_BSPOL76.aantal,
Tbl_1_BSPOL76.kwal6
FROM Tbl_1_BSPOL76
WHERE (((Tbl_1_BSPOL76.mabc)=[Forms]![Frm_1_Minfs]![Mabc8]));

Add in the main form Current event SubFormControlName.Form.Requery.

What can be wrong here? How do I get it synchronised automatically?

Take a closer look at the two properties below source object on the
second page in the property editor for your subform control.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Private Sub Form_Current()
'Frm_1_Inve.Form.Requery
SubFormControlName = ("Frm_1_Inve")
SubFormControlName.Form.Requery
End Sub
It always pops up an error: Run-time error'424':
Object required.
Sorry to ask, but I'm quite a novice in this. What do i do next?

Mark the subform control on your form, open the property editor. In the
combobox on the top left you can see the name of your subform control.
Use this name instead of "SubFormControlName".
It is important that you mark the subform control and not the subform
itself.

Private Sub Form_Current()
SubFormControlName.Form.Requery
'replace SubFormControlName with the name of the control.
End Sub


mfG
--> stefan <--

 

[Guest]

I've put the extra code in the "on current" of the main form.
When scrolling trough the records on the main form, the subform seems to
react but......
Apparantly the subform shows data, but all of it, there is no relevant
synchronisation. I expect to see in my subform, only those records who
correspond with the "mabc8" from the main form.
The link chield fields is set to Mabc
The link master fields is set to Mabc8
No extra filter in the query.
Does anywhone know what is going on here?

I've encounter a problem with a subform.
Every time I scroll trough the records on my main form, I expect the data in
the subforms will be adjusted automatically.
I used the toolbox to add subforms onto the main form.
The child and master are set up for these subform that uses data out of
another table. I also setup the relation via the Relationships.
Here is the query that selects the data:
SELECT Tbl_1_BSPOL76.plannr, Tbl_1_BSPOL76.datsnpl, Tbl_1_BSPOL76.aantal,
Tbl_1_BSPOL76.kwal6
FROM Tbl_1_BSPOL76
WHERE (((Tbl_1_BSPOL76.mabc)=[Forms]![Frm_1_Minfs]![Mabc8]));

Frm_1_Minfs is my main form.
After scrolling ttrough the table and positioning the mouse on the data area
of the subform and push F9 to refresh, the correct data does appear.

What can be wrong here? How do I get it synchronised automatically?

It sounds like you're using two separate mechanisms to sync
the main form and the subform. I would expect the Link
Master property to be Mabc8 and the Lick Child property to
be mabc. That should be all you need. If it is, then get
rid of the WHERE clause in the query.

If there is some reason you have not yet explained for using
the WHERE clause, then you should Requery the subform in the
main form's Current event.

 

[Marshall Barton]

What "extra code"? Without the WHERE clause and with the
proper Link Master/Child setting, there's no need for any
extra code, especially not a Requery.
--
Marsh
MVP [MS Access]

I've put the extra code in the "on current" of the main form.
When scrolling trough the records on the main form, the subform seems to
react but......
Apparantly the subform shows data, but all of it, there is no relevant
synchronisation. I expect to see in my subform, only those records who
correspond with the "mabc8" from the main form.
The link chield fields is set to Mabc
The link master fields is set to Mabc8
No extra filter in the query.
Does anywhone know what is going on here?

I've encounter a problem with a subform.
Every time I scroll trough the records on my main form, I expect the data in
the subforms will be adjusted automatically.
I used the toolbox to add subforms onto the main form.
The child and master are set up for these subform that uses data out of
another table. I also setup the relation via the Relationships.
Here is the query that selects the data:
SELECT Tbl_1_BSPOL76.plannr, Tbl_1_BSPOL76.datsnpl, Tbl_1_BSPOL76.aantal,
Tbl_1_BSPOL76.kwal6
FROM Tbl_1_BSPOL76
WHERE (((Tbl_1_BSPOL76.mabc)=[Forms]![Frm_1_Minfs]![Mabc8]));

Frm_1_Minfs is my main form.
After scrolling ttrough the table and positioning the mouse on the data area
of the subform and push F9 to refresh, the correct data does appear.

What can be wrong here? How do I get it synchronised automatically?

It sounds like you're using two separate mechanisms to sync
the main form and the subform. I would expect the Link
Master property to be Mabc8 and the Lick Child property to
be mabc. That should be all you need. If it is, then get
rid of the WHERE clause in the query.

If there is some reason you have not yet explained for using
the WHERE clause, then you should Requery the subform in the
main form's Current event.

 

[Guest]

Hi Marshall,

I removed the Where clause as you proposed
The Link Master/child was set up from the beginning
Only in the Main Form On Current I wrote this line of code:
Private Sub Form_Current()
'Frm_1_Inve.Form.Requery

Inverwerking.Form.Requery
End Sub

Though, none of these solutions contain the correct result.

What "extra code"? Without the WHERE clause and with the
proper Link Master/Child setting, there's no need for any
extra code, especially not a Requery.
--
Marsh
MVP [MS Access]

I've put the extra code in the "on current" of the main form.
When scrolling trough the records on the main form, the subform seems to
react but......
Apparantly the subform shows data, but all of it, there is no relevant
synchronisation. I expect to see in my subform, only those records who
correspond with the "mabc8" from the main form.
The link chield fields is set to Mabc
The link master fields is set to Mabc8
No extra filter in the query.
Does anywhone know what is going on here?

Amduke wrote:
I've encounter a problem with a subform.
Every time I scroll trough the records on my main form, I expect the data in
the subforms will be adjusted automatically.
I used the toolbox to add subforms onto the main form.
The child and master are set up for these subform that uses data out of
another table. I also setup the relation via the Relationships.
Here is the query that selects the data:
SELECT Tbl_1_BSPOL76.plannr, Tbl_1_BSPOL76.datsnpl, Tbl_1_BSPOL76.aantal,
Tbl_1_BSPOL76.kwal6
FROM Tbl_1_BSPOL76
WHERE (((Tbl_1_BSPOL76.mabc)=[Forms]![Frm_1_Minfs]![Mabc8]));

Frm_1_Minfs is my main form.
After scrolling ttrough the table and positioning the mouse on the data area
of the subform and push F9 to refresh, the correct data does appear.

What can be wrong here? How do I get it synchronised automatically?

It sounds like you're using two separate mechanisms to sync
the main form and the subform. I would expect the Link
Master property to be Mabc8 and the Lick Child property to
be mabc. That should be all you need. If it is, then get
rid of the WHERE clause in the query.

If there is some reason you have not yet explained for using
the WHERE clause, then you should Requery the subform in the
main form's Current event.

 

[Marshall Barton]

Get rid of the Requery in the main form's Current event.
The Link Master/Child properties take care of that
automatically.

If that doesn't take care of synchronizing the subform to
the current mainform record, then I don't know what you want
to happen when you say "synchronize".
--
Marsh
MVP [MS Access]

I removed the Where clause as you proposed
The Link Master/child was set up from the beginning
Only in the Main Form On Current I wrote this line of code:
Private Sub Form_Current()
'Frm_1_Inve.Form.Requery

Inverwerking.Form.Requery
End Sub

Though, none of these solutions contain the correct result.

What "extra code"? Without the WHERE clause and with the
proper Link Master/Child setting, there's no need for any
extra code, especially not a Requery.

I've put the extra code in the "on current" of the main form.
When scrolling trough the records on the main form, the subform seems to
react but......
Apparantly the subform shows data, but all of it, there is no relevant
synchronisation. I expect to see in my subform, only those records who
correspond with the "mabc8" from the main form.
The link chield fields is set to Mabc
The link master fields is set to Mabc8
No extra filter in the query.
Does anywhone know what is going on here?


Amduke wrote:
I've encounter a problem with a subform.
Every time I scroll trough the records on my main form, I expect the data in
the subforms will be adjusted automatically.
I used the toolbox to add subforms onto the main form.
The child and master are set up for these subform that uses data out of
another table. I also setup the relation via the Relationships.
Here is the query that selects the data:
SELECT Tbl_1_BSPOL76.plannr, Tbl_1_BSPOL76.datsnpl, Tbl_1_BSPOL76.aantal,
Tbl_1_BSPOL76.kwal6
FROM Tbl_1_BSPOL76
WHERE (((Tbl_1_BSPOL76.mabc)=[Forms]![Frm_1_Minfs]![Mabc8]));

Frm_1_Minfs is my main form.
After scrolling ttrough the table and positioning the mouse on the data area
of the subform and push F9 to refresh, the correct data does appear.

What can be wrong here? How do I get it synchronised automatically?


:
It sounds like you're using two separate mechanisms to sync
the main form and the subform. I would expect the Link
Master property to be Mabc8 and the Lick Child property to
be mabc. That should be all you need. If it is, then get
rid of the WHERE clause in the query.

If there is some reason you have not yet explained for using
the WHERE clause, then you should Requery the subform in the
main form's Current event.

 

[Guest]

Problem solved.
How?
I created a form with selection of the record according to the value of the
record shown in the mainform.
In the mainform I added the previous mensioned form as Subform, and the
Master/child relation was set automaticaly.
Also in the mainform I wrote 2 extra lines in the Event on current:
Private Sub Form_Current()
SubFormControlName.Form.Requery
SubFormControlName.Form.Refresh
replace SubFormControlName with the name of the control.
End Sub

Now it works fine. When scrolling trough the records, I see the subform
changing every time there is a match.

Thanks all for your assistance.

 

[Guest]

Stefan,
I came across this post in looking for an answer to a problem i'm having
with synchronizing 2 subforms. Your code worked perfectly except everytime I
open the form I get the following message: Run-time error '2455', You entered
an expression that as an invalid reference to the property Form/Report. This
does not keep the form from working, however. I used your code and modified
it for my needsrivate Sub Form_Current()

Dim rs As DAO.Recordset

If (Not IsNull(Me![ProjectID])) And _
(Not IsNull(Parent.Subform2.Form![ProjectID])) And _
(Me![ProjectID] <> Parent.Subform2.Form![ProjectID]) Then
Set rs = Parent.Subform2.Form.RecordsetClone
rs.FindFirst "ProjectID = " & Me![ProjectID]
If Not rs.NoMatch Then
Parent.Subform2.Form.Bookmark = rs.Bookmark
End If
End If

End Sub

Do you have any suggestions about the error? Thanks!

Kim

hi Leif,

Leif S wrote

Can I jump into this question with my own smiliar one. I hav two child
subforms that I want to syncronize so that when scrolling vertically one of
them, the other one follows. Both share the same foreign key linking them to
the master form.

This needs some coding, place the following code in both subforms on
current event:

Private Sub Form_Current()

Dim rs As DAO.Recordset

If (Not IsNull(Me![ID])) And _
(Not IsNull(Parent.OtherSubFormControl.Form![ID])) And _
(Me![ID] <> Parent.OtherSubFormControl.Form![ID]) Then
Set rs = Parent.OtherSubFormControl.Form.RecordsetClone
rs.FindFirst "ID = " & Me![ID]
If Not rs.NoMatch Then
Parent.OtherSubFormControl.Form.Bookmark = rs.Bookmark
End If
End If

End Sub


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Kim,

I came across this post in looking for an answer to a problem i'm having
with synchronizing 2 subforms. Your code worked perfectly except everytime I
open the form I get the following message: Run-time error '2455', You entered
an expression that as an invalid reference to the property Form/Report.

This error indicates that the event is fired while not all used (sub-)
forms are initiated, hence the invalid reference error.

Do you have any suggestions about the error? Thanks!

Using a simple

On Local Error Resume Next

in your events should do it.


mfG
--> stefan <--

 

[Guest]

That worked perfectly! Thanks for the help.

hi Kim,

This error indicates that the event is fired while not all used (sub-)
forms are initiated, hence the invalid reference error.

Using a simple

On Local Error Resume Next

in your events should do it.


mfG
--> stefan <--

 

[hughess7]

Hi Stefan

Thanks for this code, I've used it to sync my two subforms together. It
works but it is quite slow to move to the equivalent record on the other
subform and it causes a flickering in one subform until it has found the
match in the other subform. Is there anything i can do about this to speed it
up?

Each subform has one occurrence of a Territory Code, one subform is read
only displaying calculated estimated number of days on activities and the
other subform has data entry fields for the user to enter the actual days. I
then want the background colour to change if the user does change the values
so they no longer equal the estimated days. Hence the need to keep the two
subforms in sync so I can easily compare current records for conditional
formatting...

Thanks in advance for any help.
Sue

hi Leif,

Leif S wrote

Can I jump into this question with my own smiliar one. I hav two child
subforms that I want to syncronize so that when scrolling vertically one of
them, the other one follows. Both share the same foreign key linking them to
the master form.

This needs some coding, place the following code in both subforms on
current event:

Private Sub Form_Current()

Dim rs As DAO.Recordset

If (Not IsNull(Me![ID])) And _
(Not IsNull(Parent.OtherSubFormControl.Form![ID])) And _
(Me![ID] <> Parent.OtherSubFormControl.Form![ID]) Then
Set rs = Parent.OtherSubFormControl.Form.RecordsetClone
rs.FindFirst "ID = " & Me![ID]
If Not rs.NoMatch Then
Parent.OtherSubFormControl.Form.Bookmark = rs.Bookmark
End If
End If

End Sub


mfG
--> stefan <--