update all filtered records in a sum form

[Guest]

I am using Access 2003 and have a form based on 1 table, with a subform based
on another. Typically the sub form which is continuous contains 1-6 records.
I want to be able to update a date field on the main form which then updates
a date field in all filtered records of the sub form. I have tried doing this
using the after update event on the field in the main form but this only
updates the sub form record which has the focus. Any suggestions would be
most appreciated.

 

[Allen Browne]

Two approaches:
=============
a) Loop through the RecordsetClone of the subform, setting the value of the
field, or

b) Execute an Update query directly on the subform's table, with a WHERE
clause that limits it to the filtered records.

Before you do that, check these issues:
=============================
1. Is this a normalized design? Should the date really be in every record of
the subform's table, as well as in the main form's table, or does that break
the rule that there is only one place where you store and retrieve any
value. (Since you are talking filtered subform, it is possible that your
design is right.)

2. Is there any chance that a filter could be applied to the main form here?
If so, there is a bug in Access so that it cannot tell you reliably whether
the subform has a filter applied or not. More into in this article:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

3. By "filtering" do you mean a Filter is applied to the subform? Or do you
just mean that the subform shows only a subset of the records from its
table, due to the LinkMasterFields/LinkChildFields setting?

4. When do you want to change the date? It seems like you want to do this
when the date changes in the main form. However, if the user then *undoes*
that change, or if the change is not saved for some reason (e.g. required
field missing), presumably you don't want to change the date in the subform.
I suggest you use the AfterUpdate event of the main *form* to write the
change into the subform's records.

The events to use
=============
Now, you only need to write the change if the date changed. You can
determine that by looking at the OldValue of the text box on the main form.
However, Form_AfterUpdate is too late for that: the OldValue is no longer
accessible. You therefore need to use Form_BeforeUpdate to see if the Value
is different from the OldValue. The trouble with Form_BeforeUpdate is that
you don't know at that point whether the save will succeed. You therefore
need to set a flag in Form_BeforeUpate if the date changed, and check the
flag in Form_AfterUpdate to determine whether to update the subform's
filtered records.

Solution (a)
=========
1. Open the main form in design view. Open its module.
In the General Declarations section of the form's modules (at the top, with
the Option Statements), add this line:
Private mbUpdate As Boolean

2. Set the BeforeUpate property of the *form* to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Set up the code like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me.txtDate
If .Value = .OldValue Then
mbUpdate = False
Else
mbUpdate = True
End If
End With
'Any other validation code you need here.
End Sub

3. Set up the code for the *form's* AfterUpdate event procedure like this:
Private Sub Form_AfterUpdate()
Dim rs As DAO.Recordset
If mbUpdate Then
Set rs = Me.[Sub1].Form.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
rs![MyDate] = Me.[txtDate]
rs.Update
rs.MoveNext
Loop
End If
End If
Set rs = Nothing
End Sub

Solution (b)
=========
Follow steps 1 and 2 of solution (a).
Replace step 3 with this:
Private Sub Form_AfterUpdate()
If mbUpDate Then
With Me.[Sub1].Form
strSql = "UPDATE [SubTable] SET [MyDate] = " & _
Format(Me.[txtDate], "\#mm\/dd\/yyyy\#") & _
" WHERE ([MyFK] = " & Me.[ID] & ")"
If .FilterOn Then
strSql = strSql & " AND (" & .Filter & ")"
End If
dbEngine(0)(0).Execute strSql, dbFailOnError
.Requery
End With
End If
End Sub

The example code is untested. Check that it compiles (Compile on Debug
menu), change the names to match your fields/controls, and debug.

 

[Arvin Meyer [MVP]]

I am using Access 2003 and have a form based on 1 table, with a subform based
on another. Typically the sub form which is continuous contains 1-6 records.
I want to be able to update a date field on the main form which then updates
a date field in all filtered records of the sub form. I have tried doing this
using the after update event on the field in the main form but this only
updates the sub form record which has the focus. Any suggestions would be
most appreciated.

I have no way to test this but it should work for you:

In the AfterUpdate event of the textbox that you want to change, build a
recordset based on the form's recordsetclone. (air code):

Sub txtWhatever_AfterUpdate()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = Me.RecordsetClone
With rst
.MoveFirst
.Edit
.Whatever = Me.txtWhatever
.Update
.MoveNext
End With
rst.Close
Set rst = Nothing
Set db = Nothing
Me.Refresh

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer [MVP]]

Looks like I forgot to loop:

Sub txtWhatever_AfterUpdate()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = Me.RecordsetClone
Do Until rst.EOF
With rst
.MoveFirst
.Edit
.Whatever = Me.txtWhatever
.Update
.MoveNext
End With
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
Me.Refresh

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thank you for your swift and detailed response. In answer to your points;
1. I am satisfied that the date is in both tables as on occasion it could be
different which the user can adjust manually.
2. No filter will be applied.
3. The sub form does not have a filter but linked on Child/Master fields.
4. The date on the main form tells us that a process has concluded and the
date on the sub form is a start date for several following processes. This
date will not then be changed unless a user enters the wrong date and has to
subsequently edit.
With this further detail could you advise me on the most appropriate of your
2 methods to use.
Thanks once again.

Two approaches:
=============
a) Loop through the RecordsetClone of the subform, setting the value of the
field, or

b) Execute an Update query directly on the subform's table, with a WHERE
clause that limits it to the filtered records.

Before you do that, check these issues:
=============================
1. Is this a normalized design? Should the date really be in every record of
the subform's table, as well as in the main form's table, or does that break
the rule that there is only one place where you store and retrieve any
value. (Since you are talking filtered subform, it is possible that your
design is right.)

2. Is there any chance that a filter could be applied to the main form here?
If so, there is a bug in Access so that it cannot tell you reliably whether
the subform has a filter applied or not. More into in this article:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

3. By "filtering" do you mean a Filter is applied to the subform? Or do you
just mean that the subform shows only a subset of the records from its
table, due to the LinkMasterFields/LinkChildFields setting?

4. When do you want to change the date? It seems like you want to do this
when the date changes in the main form. However, if the user then *undoes*
that change, or if the change is not saved for some reason (e.g. required
field missing), presumably you don't want to change the date in the subform.
I suggest you use the AfterUpdate event of the main *form* to write the
change into the subform's records.

The events to use
=============
Now, you only need to write the change if the date changed. You can
determine that by looking at the OldValue of the text box on the main form.
However, Form_AfterUpdate is too late for that: the OldValue is no longer
accessible. You therefore need to use Form_BeforeUpdate to see if the Value
is different from the OldValue. The trouble with Form_BeforeUpdate is that
you don't know at that point whether the save will succeed. You therefore
need to set a flag in Form_BeforeUpate if the date changed, and check the
flag in Form_AfterUpdate to determine whether to update the subform's
filtered records.

Solution (a)
=========
1. Open the main form in design view. Open its module.
In the General Declarations section of the form's modules (at the top, with
the Option Statements), add this line:
Private mbUpdate As Boolean

2. Set the BeforeUpate property of the *form* to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Set up the code like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me.txtDate
If .Value = .OldValue Then
mbUpdate = False
Else
mbUpdate = True
End If
End With
'Any other validation code you need here.
End Sub

3. Set up the code for the *form's* AfterUpdate event procedure like this:
Private Sub Form_AfterUpdate()
Dim rs As DAO.Recordset
If mbUpdate Then
Set rs = Me.[Sub1].Form.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
rs![MyDate] = Me.[txtDate]
rs.Update
rs.MoveNext
Loop
End If
End If
Set rs = Nothing
End Sub

Solution (b)
=========
Follow steps 1 and 2 of solution (a).
Replace step 3 with this:
Private Sub Form_AfterUpdate()
If mbUpDate Then
With Me.[Sub1].Form
strSql = "UPDATE [SubTable] SET [MyDate] = " & _
Format(Me.[txtDate], "\#mm\/dd\/yyyy\#") & _
" WHERE ([MyFK] = " & Me.[ID] & ")"
If .FilterOn Then
strSql = strSql & " AND (" & .Filter & ")"
End If
dbEngine(0)(0).Execute strSql, dbFailOnError
.Requery
End With
End If
End Sub

The example code is untested. Check that it compiles (Compile on Debug
menu), change the names to match your fields/controls, and debug.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using Access 2003 and have a form based on 1 table, with a subform
based
on another. Typically the sub form which is continuous contains 1-6
records.
I want to be able to update a date field on the main form which then
updates
a date field in all filtered records of the sub form. I have tried doing
this
using the after update event on the field in the main form but this only
updates the sub form record which has the focus. Any suggestions would be
most appreciated.

 

[Allen Browne]

Either way. Perhaps (a) would be preferred.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your swift and detailed response. In answer to your points;
1. I am satisfied that the date is in both tables as on occasion it could
be
different which the user can adjust manually.
2. No filter will be applied.
3. The sub form does not have a filter but linked on Child/Master fields.
4. The date on the main form tells us that a process has concluded and the
date on the sub form is a start date for several following processes.
This
date will not then be changed unless a user enters the wrong date and has
to
subsequently edit.
With this further detail could you advise me on the most appropriate of
your
2 methods to use.
Thanks once again.

Two approaches:
=============
a) Loop through the RecordsetClone of the subform, setting the value of
the
field, or

b) Execute an Update query directly on the subform's table, with a WHERE
clause that limits it to the filtered records.

Before you do that, check these issues:
=============================
1. Is this a normalized design? Should the date really be in every record
of
the subform's table, as well as in the main form's table, or does that
break
the rule that there is only one place where you store and retrieve any
value. (Since you are talking filtered subform, it is possible that your
design is right.)

2. Is there any chance that a filter could be applied to the main form
here?
If so, there is a bug in Access so that it cannot tell you reliably
whether
the subform has a filter applied or not. More into in this article:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

3. By "filtering" do you mean a Filter is applied to the subform? Or do
you
just mean that the subform shows only a subset of the records from its
table, due to the LinkMasterFields/LinkChildFields setting?

4. When do you want to change the date? It seems like you want to do this
when the date changes in the main form. However, if the user then
*undoes*
that change, or if the change is not saved for some reason (e.g. required
field missing), presumably you don't want to change the date in the
subform.
I suggest you use the AfterUpdate event of the main *form* to write the
change into the subform's records.

The events to use
=============
Now, you only need to write the change if the date changed. You can
determine that by looking at the OldValue of the text box on the main
form.
However, Form_AfterUpdate is too late for that: the OldValue is no longer
accessible. You therefore need to use Form_BeforeUpdate to see if the
Value
is different from the OldValue. The trouble with Form_BeforeUpdate is
that
you don't know at that point whether the save will succeed. You therefore
need to set a flag in Form_BeforeUpate if the date changed, and check the
flag in Form_AfterUpdate to determine whether to update the subform's
filtered records.

Solution (a)
=========
1. Open the main form in design view. Open its module.
In the General Declarations section of the form's modules (at the top,
with
the Option Statements), add this line:
Private mbUpdate As Boolean

2. Set the BeforeUpate property of the *form* to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Set up the code like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me.txtDate
If .Value = .OldValue Then
mbUpdate = False
Else
mbUpdate = True
End If
End With
'Any other validation code you need here.
End Sub

3. Set up the code for the *form's* AfterUpdate event procedure like
this:
Private Sub Form_AfterUpdate()
Dim rs As DAO.Recordset
If mbUpdate Then
Set rs = Me.[Sub1].Form.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
rs![MyDate] = Me.[txtDate]
rs.Update
rs.MoveNext
Loop
End If
End If
Set rs = Nothing
End Sub

Solution (b)
=========
Follow steps 1 and 2 of solution (a).
Replace step 3 with this:
Private Sub Form_AfterUpdate()
If mbUpDate Then
With Me.[Sub1].Form
strSql = "UPDATE [SubTable] SET [MyDate] = " & _
Format(Me.[txtDate], "\#mm\/dd\/yyyy\#") & _
" WHERE ([MyFK] = " & Me.[ID] & ")"
If .FilterOn Then
strSql = strSql & " AND (" & .Filter & ")"
End If
dbEngine(0)(0).Execute strSql, dbFailOnError
.Requery
End With
End If
End Sub

The example code is untested. Check that it compiles (Compile on Debug
menu), change the names to match your fields/controls, and debug.

I am using Access 2003 and have a form based on 1 table, with a subform
based
on another. Typically the sub form which is continuous contains 1-6
records.
I want to be able to update a date field on the main form which then
updates
a date field in all filtered records of the sub form. I have tried
doing
this
using the after update event on the field in the main form but this
only
updates the sub form record which has the focus. Any suggestions would
be
most appreciated.

 

[Guest]

Hi Arvin,
Thanks for your response.
Can you tell me what I need to use where you have entered "Whatever" in your
code. I assumed it would be the name of the sub form followed by the field
but I don't know how to refer to that with the options provided.
Also I assume where you have written "Me.txtWhatever" this is the name of
the control on the main form ?
Thank you