Looping thru Subform Records

  • Thread starter Thread starter oldblindpew
  • Start date Start date
O

oldblindpew

I have a function that writes to a text box in a subform based on the values
of other controls in that subform. Right now this function runs from the On
Current event for the subform as well as the After Update event for the
appropriate controls.

How would I make this function run automatically for every record in the
subform's record source, at the time the subform is loaded? Or, in case this
is the wrong question, would I need to write an On Load event procedure that
would duplicate or recreate all of this effort independent of the form
environment?

Do I need to be concerned about conflicts between users as a result of the
proposed automatic updates of multiple records?

Thanks,
OldBlindPew
 
oldblindpew said:
I have a function that writes to a text box in a subform based on the values
of other controls in that subform. Right now this function runs from the On
Current event for the subform as well as the After Update event for the
appropriate controls.

How would I make this function run automatically for every record in the
subform's record source, at the time the subform is loaded? Or, in case this
is the wrong question, would I need to write an On Load event procedure that
would duplicate or recreate all of this effort independent of the form
environment?

Do I need to be concerned about conflicts between users as a result of the
proposed automatic updates of multiple records?
Click to expand...


Using the current event is only viable if the text box is
not bound to a data field. If it is unbound, then there is
no need to calculate it for every record unless the subform
is displayed in continuous view.

OTOH, for continuous view, it would not make sense to use
code to calculate and set the value for all the records.
The calculation would have to be done in the record source
query or by using a function in text box's control source
expression.

Based on all that, your question only makes sense if the
field is bound, but doing what you asked is a serious no-no
in a relational database. Values that can be calculated
from other fields in the record (or table) should not be
stored in the record. Instead, they should be recalculated
in a query or text box expression whenever they are
displayed to a user. Therefore, you are lead back to using
an unbound text box with an expression or a calculated field
in the record source query.

If any of that logic escapes you, post back with specific
questions about the parts that are not clear to you.
 
Thanks, Marshall.

I followed your advice and revised my function so that it writes the desired
value to itself, and then I set the Control Source for my text box equal to
the function. It works perfectly! As soon as my subform is opened, all
visible records are updated, that is, the text box value is updated by the
function, for all records in the form.

However... When I now edit any of the controls upon which the value of the
text box depends, nothing happens. The value of the text box does not change
in response to the input. I have to close the form and reopen it to get the
function to run again.

Would I need some sort of command in the After Update events for the various
pertinent controls to force the text box to attend to its duty?

Thanks Again,
Pew
 
oldblindpew said:
I followed your advice and revised my function so that it writes the desired
value to itself, and then I set the Control Source for my text box equal to
the function. It works perfectly! As soon as my subform is opened, all
visible records are updated, that is, the text box value is updated by the
function, for all records in the form.

However... When I now edit any of the controls upon which the value of the
text box depends, nothing happens. The value of the text box does not change
in response to the input. I have to close the form and reopen it to get the
function to run again.

Would I need some sort of command in the After Update events for the various
pertinent controls to force the text box to attend to its duty?
Click to expand...


Updating dependent text box expression will usually be done
automatically by Access. I suspect that your function is
retrieving the values it needs in its code thus hiding the
dependency from Access. Instead the base value fields
should be passed to the function in arguments.

Post back with a Copy/Paste of the function's code and an
explation of where the base values come from if you need
help modifying the function.

If worst comes to worst, you may also need to use the other
control's AfterUpdate event to requery the text box with the
function call.
 
I will avail myself of your invitation to post my ugly code. I don't think
there is anything about it that is hidden from Access; it just isn't running
after update of controls or records. If the function isn't running, I don't
see how changing the arguments will make any difference.

Any suggestions for improving the code would be welcome. It just checks a
date and four check boxes to decide the status. I know I'm supposed to put
the status values in a lookup table, but that just seemed like more
complications at the time.

Thanks,
Pew

Private Function InsStatus() As String
On Error GoTo Err_InsStatus

If IsNull(Me.txbJobNo) Then 'Bad record
GoTo Exit_InsStatus
Else 'Good record
If IsNull(Me.txbExpSoonest) Then 'No Expiration Date
If Me.chkInForce.Value = Unchecked Then 'No Date, No Checkbox: No
Insurance
InsStatus = "Absent"
Else 'No Date, Yes Checkbox: Need Date
InsStatus = "Date Req'd"
End If
Else 'There is an Expiration Date
If Me.chkInForce.Value = Unchecked Then 'Yes Date, No Checkbox:
Insurance Cancelled
InsStatus = "Cancelled"
Else 'Yes Date, Yes Checkbox: check date
If Me.txbExpSoonest < Date Then 'Expiration date has passed
InsStatus = "Expired"
Else 'Exp Date has not passed; will it pass within a month?
If Me.txbExpSoonest <= DateAdd("m", 1, Date) Then 'Expiration
within a month
InsStatus = "Expiring"
Else 'Exp Date okay: Check Limits & Endorsements
If Me.chkAllLimitsOk.Value = Unchecked Then
InsStatus = "Deficient"
Else
If Me.chkAIEndorsOk.Value = Unchecked Then
InsStatus = "Deficient"
Else
If Me.chkWOSEndorsOk.Value = Unchecked Then
InsStatus = "Deficient"
Else
InsStatus = "Ok"
End If
End If
End If
End If
End If
End If
End If
End If

Exit_InsStatus:
 
The only thing in VBA that is not hidden from Access are
public functions in standard modules and functions in a
form/report's class module. In other words, Access has no
idea that your code references a text box, check box, etc.
on your form. Even if it did, it would not know what to do
when a value in a control is changed.

I see that your code refers to several things that are not
declared in the function (e.g. txbExpSoonest, chkInForce,
etc). Because you used Me. with those names, I can tell
that they are form controls so those are the things that
need to be passed as arguments. Without trying to rewrite
all the code, the general idea would be something like
changing your calculated text box expression from:
=TotalPrice()
with this kind of function:
Function TotalPrice()
TotalPrice = Me.Price * Me,Quantity
End Function

to something more like using this expression:
=TotalPrice(Price, Quantity)
with the function:
Function TotalPrice(P, Q)
TotalPrice = P * Q
End Function

The important point here is that Access sees the expression
=TotalPrice(Price, Quantity) and "knows" that the Price and
Quantity fields are used so it can create a dependency and
recalculate the expression whenever the value of Price or
Quantity changes.

You also used something named Unchecked. I can't see where
that's declared so I would only be guessing when I assume
you have a statement like:
Const Unchecked As Boolean =False
somewhere in your project. If that's not whar you have,
please explain what you do have.
 
Hi BruceM and Marshall.
Thanks very much for helping me with this.

Error Handling: Don't worry about this, Bruce. I just didn't bother
pasting in the rest of the code. Notice there is no End Function statement.

Nested If statements: Thanks, Bruce. I tried to do this at the outset but
could not get the syntax right, which is an ongoing problem for me. I know
syntax must be done right, but am continually thwarted trying to look it up.
For example, Access online Help for "Using If...Then...Else Statements",
simply does not address this situation.

"Unchecked": I got this by looking at someone else's code on the Microsoft
Discussion Forum. It worked by accident because I had not used the Option
Explicit statement, so VB interpreted it as an undeclared variable with a
value of zero. I have since added the Option Explicit statement and changed
to the correct term False.

Original problem: The form was behaving like a spreadsheet stuck on manual
recalculate. I therefore created a private function as follows, which is run
from each pertinent control's After Update event:

Private Function Recalculate()
Me.Recalc

End Function

Question: I still don't see how just adding some arguments to a function
will make it run automatically, without telling it to recalculate, per above.

Next problem: I have a second form/subform that will need to run this same
Insurance Status function (InsStatus), so I will need to either reinvent the
function as a public function in a class module (correct but difficult), or
simply copy the function as is into the other form's module (poor practice,
but easy). I will attempt the former, and would appreciate any hints.

Thanks,
Pew


BruceM via AccessMonster.com said:
A couple of observations unrelated to what Marshall has written:

You have the line:
On Error GoTo Err_InsStatus

However, I do not see Err_InsStatus. Perhaps you meant to use GoTo
Exit_InsStatus, which has On Error Resume Next. However, if you need On
Error Resume Next you can just put it in the code rather than sending the
code to Exit when there is an error, then back to the next line.

Also, you have the line:
GoTo Exit_InsStatus

You could just do Exit Function.

Exit_InsStatus has On Error Resume Next. I'm not sure that will do anything
meaningful (unless you meant to use Exit_InsStatus as I speculated above),
since by the time you get there the only "next" thing is Exit Sub.

One other observation is that you have some nested If statements that could
be consolidated with "Or":

If Me.chkAllLimitsOk = Unchecked Or _
Me.chkAIEndorsOk = Unchecked Or _
Me.chkWOSEndorsOk = Unchecked Then
InsStatus = "Deficient"
Else
InsStatus = "Ok"
End If

This assumes the variable Unchecked is resolved somewhere, as Marshall
mentioned.
I will avail myself of your invitation to post my ugly code. I don't think
there is anything about it that is hidden from Access; it just isn't running
after update of controls or records. If the function isn't running, I don't
see how changing the arguments will make any difference.

Any suggestions for improving the code would be welcome. It just checks a
date and four check boxes to decide the status. I know I'm supposed to put
the status values in a lookup table, but that just seemed like more
complications at the time.

Thanks,
Pew

Private Function InsStatus() As String
On Error GoTo Err_InsStatus

If IsNull(Me.txbJobNo) Then 'Bad record
GoTo Exit_InsStatus
Else 'Good record
If IsNull(Me.txbExpSoonest) Then 'No Expiration Date
If Me.chkInForce.Value = Unchecked Then 'No Date, No Checkbox: No
Insurance
InsStatus = "Absent"
Else 'No Date, Yes Checkbox: Need Date
InsStatus = "Date Req'd"
End If
Else 'There is an Expiration Date
If Me.chkInForce.Value = Unchecked Then 'Yes Date, No Checkbox:
Insurance Cancelled
InsStatus = "Cancelled"
Else 'Yes Date, Yes Checkbox: check date
If Me.txbExpSoonest < Date Then 'Expiration date has passed
InsStatus = "Expired"
Else 'Exp Date has not passed; will it pass within a month?
If Me.txbExpSoonest <= DateAdd("m", 1, Date) Then 'Expiration
within a month
InsStatus = "Expiring"
Else 'Exp Date okay: Check Limits & Endorsements
If Me.chkAllLimitsOk.Value = Unchecked Then
InsStatus = "Deficient"
Else
If Me.chkAIEndorsOk.Value = Unchecked Then
InsStatus = "Deficient"
Else
If Me.chkWOSEndorsOk.Value = Unchecked Then
InsStatus = "Deficient"
Else
InsStatus = "Ok"
End If
End If
End If
End If
End If
End If
End If
End If

Exit_InsStatus:
On Error Resume Next
Exit Function
I followed your advice and revised my function so that it writes the desired
value to itself, and then I set the Control Source for my text box equal to
Click to expand...
[quoted text clipped - 23 lines]
control's AfterUpdate event to requery the text box with the
function call.
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
I should have said "standard module". The function needs to be in a standard
module rather than being married to any one form.

But there must be more to it than declaring a form variable and using it in
place of the Me keyword. How do I pass the name of the calling form to the
function? I've been trying all afternoon. Correct syntax cannot be guessed,
yet I have very poor luck trying to systematically look it up in a book or
online.

Here's what I've got now, which doesn't work:

Function InsStatus(strCallForm As String) As String
Dim frm As Form
Set frm = Forms(strCallForm)

If IsNull(frm.txbExpSoonest) Then 'No Expiration Date
If frm.chkInForce.Value = False Then 'No Date, No Checkbox: No
Insurance
InsStatus = "Absent"
Else 'No Date, Yes Checkbox: Need Date
etc...

My Control Source for the text box is =InsStatus("MyFormName")

Access barks back that it can't find any such form.

The "Question" in my prior post was in context of suggestion made earlier by
Marshall that my text box was not being automatically recalculated because
Access had no way of knowing that the calculation depended on control values
that had been changed. In other words, if I understand, he was saying that
if I mentioned those control names as arguments in my function call, Access
would then decide to re-run the function anytime those controls were edited.

Whether this is true or not I don't know, but my approach of using a
function in the After Update events of the various controls to force the form
to recalculate does at least work.

Thanks,
Pew


BruceM via AccessMonster.com said:
Regarding your second point, I'm not sure why it would need to be a public
procedure in a class module rather than a standard module. Assuming the
control names are the same in both forms you should be able simply to have:

Dim frm as Form

Thereafter replace the Me prefix with frm:

If frm.chkAllLimitsOk = False Then...

Then you can just call the function, supplying arguments as needed, same as
any function.

Here is an article about class modules. I can't say I quite follow all of it,
but it has a lot of information that could help you to decide what type of
module to use:
http://www.members.shaw.ca/AlbertKallal/Articles/WhyClass.html

Regarding the question, as I understand Recalc causes Access to re-evaluate
calculated controls (e.g. Control Source for TextBoxMain = TextBox1 +
TextBox2). If you change the TextBox1 value on the form, Access will
recalculate TextBoxMain, but if you change the TextBox1 value using VBA
Access will not "know" about it, and TextBoxMain will show the incorrect
number until you recalculate, or else do something like move to another
record, then back. I don't know if that helps. If it doesn't I guess I
don't quite understand the question.
Hi BruceM and Marshall.
Thanks very much for helping me with this.

Error Handling: Don't worry about this, Bruce. I just didn't bother
pasting in the rest of the code. Notice there is no End Function statement.

Nested If statements: Thanks, Bruce. I tried to do this at the outset but
could not get the syntax right, which is an ongoing problem for me. I know
syntax must be done right, but am continually thwarted trying to look it up.
For example, Access online Help for "Using If...Then...Else Statements",
simply does not address this situation.

"Unchecked": I got this by looking at someone else's code on the Microsoft
Discussion Forum. It worked by accident because I had not used the Option
Explicit statement, so VB interpreted it as an undeclared variable with a
value of zero. I have since added the Option Explicit statement and changed
to the correct term False.

Original problem: The form was behaving like a spreadsheet stuck on manual
recalculate. I therefore created a private function as follows, which is run
from each pertinent control's After Update event:

Private Function Recalculate()
Me.Recalc

End Function

Question: I still don't see how just adding some arguments to a function
will make it run automatically, without telling it to recalculate, per above.

Next problem: I have a second form/subform that will need to run this same
Insurance Status function (InsStatus), so I will need to either reinvent the
function as a public function in a class module (correct but difficult), or
simply copy the function as is into the other form's module (poor practice,
but easy). I will attempt the former, and would appreciate any hints.

Thanks,
Pew
A couple of observations unrelated to what Marshall has written:
Click to expand...
[quoted text clipped - 95 lines]
control's AfterUpdate event to requery the text box with the
function call.
Click to expand...
Click to expand...
Click to expand...
 
oldblindpew said:
I should have said "standard module". The function needs to be in a standard
module rather than being married to any one form.

But there must be more to it than declaring a form variable and using it in
place of the Me keyword. How do I pass the name of the calling form to the
function? I've been trying all afternoon. Correct syntax cannot be guessed,
yet I have very poor luck trying to systematically look it up in a book or
online.

Here's what I've got now, which doesn't work:

Function InsStatus(strCallForm As String) As String
Dim frm As Form
Set frm = Forms(strCallForm)

If IsNull(frm.txbExpSoonest) Then 'No Expiration Date
If frm.chkInForce.Value = False Then 'No Date, No Checkbox: No
Insurance
InsStatus = "Absent"
Else 'No Date, Yes Checkbox: Need Date
etc...

My Control Source for the text box is =InsStatus("MyFormName")

Access barks back that it can't find any such form.

The "Question" in my prior post was in context of suggestion made earlier by
Marshall that my text box was not being automatically recalculated because
Access had no way of knowing that the calculation depended on control values
that had been changed. In other words, if I understand, he was saying that
if I mentioned those control names as arguments in my function call, Access
would then decide to re-run the function anytime those controls were edited.

Whether this is true or not I don't know, but my approach of using a
function in the After Update events of the various controls to force the form
to recalculate does at least work.
Click to expand...


If you are going to move the code to a different module,
then you really need to use arguments instead of referencing
controls in the code. Just because the code refers to a lot
of controls is not a reason to not use them in arguments.
Doing it this way would eliminate the need for setting a
form variable AND almost certainly solve the calculation
issue without using Recalc.

If you are having trouble translating my trivial example
function to your situation, please ask about whatever you
don't understand.

Side note: If you really had a need to recalculate, you
should use textbox.Requery to recalculate a single text box
expression instead of recalculating the entire form by using
Recalc..
 
The latter method you exampled was tried yesterday and again today, in both
cases producing the following error:
"Error #91 Object variable or With block variable not set".
This makes sense to me in that the function tries to use a variable called
'frm' that has been declared, but not set; it has no value. The function
cannot know which form we mean when we refer to 'frm'.

Your first method seemed more promising in that it not only declares the
variable, but establishes it as an argument passed when the function is
called, thereby giving it a value as well. It produced the following error:

The expression After Update you entered as the event property setting
produced the following error: The object doesn't contain the Automation
object 'formInsLogByJobSubform.'.

BUT FINALLY, grasping at straws, I changed the calling expression from
=InsStatus([formInsLogByJobSubform])
to
=InsStatus([Form])
(taking your instructions literally)...AND IT WORKED!

But give us a break! How did MS come up with this?? I pass the exact name
of my form and Access gives me a blank stare. I change to a hopeless,
meaningless, empty expression, and it works. How can anything so arbitrary
ever be mastered?

Please do not be put off or put out by the tenor of my comments. I am
frustrated with Access and VBA, but thoroughly grateful to yourself and
Marshall for your help. I don't think I would EVER have figured this out by
myself.

Further experimentation reveals that my Recalculate function is no longer
needed. Apparently the magic argument, 'Form', implies and includes all the
controls in the current form. So if Access is told that a calculation might
possibly involve some other controls, it recalculates, otherwise not.

I do note, however, that although it appears the automatic recalculation
affects only the current record, whereas my After Update recalculation
function updates all the records in the continuous subform, nevertheless my
function, which is doing more, gives a quicker response than the automatic
version, which is doing less. Go figure.

A Thousand Thanks,
Pew

BruceM via AccessMonster.com said:
I hope I am not posting in conflict with what Marshall has saying. I hope
further that I am not leading you astray. I will say that it really is not
much more complicated that declaring a form variable. I have done this in
two ways:

Public Function FunctionName(frm as Form)

' Code

End Function

When I call the function:

FunctionName(Form)

The other way is:

Public Function FunctionName()

Dim frm as Form

' Code

End Function

Either way, Access "knows" that the form from which you are calling is the
form to which the code applies. In your case it could be:

Function InsStatus(frm as Form)

If IsNull(frm.txbExpSoonest) Then 'No Expiration Date
If frm.chkInForce.Value = False Then 'No Date, No Checkbox: No
Insurance
InsStatus = "Absent"
Else 'No Date, Yes Checkbox: Need Date
etc.

Call it thus:

InsStatus(Form)

or:

Function InsStatus()

Dim frm As Form

If IsNull(frm.txbExpSoonest) Then 'No Expiration Date
If frm.chkInForce.Value = False Then 'No Date, No Checkbox: No
Insurance
InsStatus = "Absent"
Else 'No Date, Yes Checkbox: Need Date
etc.

Call it thus:

InsStatus()

That said, when I do this sort of thing it is to loop through controls or do
something at the form level such as determine the number of records and place
the value in an unbound text box txtCount:

frm.txtCount = frm.Recordset.RecordCount

In this small example each form where you want to use this code needs a text
box named txtCount, but it is a generic text box rather than one bound to a
specific field. If two forms are similar enough that they have a lot of the
same control names they may be similar enough that you would do better to
load the recordset at run time and use one form for two different purposes.
I don't understand just what you are trying to do, so these thoughts are very
general, but when code in a standard module references a lot of specific
controls it may be an indication that you are building something that could
be difficult to maintain.

Again, I want to stress that this is not intended to be in opposition to what
Marshall has written. He has far more experience and skill than I. What I
know today about Access is due in part to his replies to my questions and to
his discussions in many threads I have watched silently. I am just trying to
add what I can about the use of form variables.

I should have said "standard module". The function needs to be in a standard
module rather than being married to any one form.

But there must be more to it than declaring a form variable and using it in
place of the Me keyword. How do I pass the name of the calling form to the
function? I've been trying all afternoon. Correct syntax cannot be guessed,
yet I have very poor luck trying to systematically look it up in a book or
online.

Here's what I've got now, which doesn't work:

Function InsStatus(strCallForm As String) As String
Dim frm As Form
Set frm = Forms(strCallForm)

If IsNull(frm.txbExpSoonest) Then 'No Expiration Date
If frm.chkInForce.Value = False Then 'No Date, No Checkbox: No
Insurance
InsStatus = "Absent"
Else 'No Date, Yes Checkbox: Need Date
etc...

My Control Source for the text box is =InsStatus("MyFormName")

Access barks back that it can't find any such form.

The "Question" in my prior post was in context of suggestion made earlier by
Marshall that my text box was not being automatically recalculated because
Access had no way of knowing that the calculation depended on control values
that had been changed. In other words, if I understand, he was saying that
if I mentioned those control names as arguments in my function call, Access
would then decide to re-run the function anytime those controls were edited.

Whether this is true or not I don't know, but my approach of using a
function in the After Update events of the various controls to force the form
to recalculate does at least work.

Thanks,
Pew
Regarding your second point, I'm not sure why it would need to be a public
procedure in a class module rather than a standard module. Assuming the
Click to expand...
[quoted text clipped - 67 lines]
control's AfterUpdate event to requery the text box with the
function call.
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Thanks Marshall,
I like the idea of using arguments because it is poor practice to hard-code
control names into the function. However, for now I've got it working along
the lines suggested by BruceM.

You said "If you are going to move the code to a different module..."
I am curious what the alternatives would be. I could simply keep the
function private and copy it as needed into other form's modules, but this
would seem rather inelegant. Are there other alternatives?

I thought about using requery, but from what I could understand in my
reading, it sounded more radical than recalc.

Thanks,
Pew
 
oldblindpew said:
I like the idea of using arguments because it is poor practice to hard-code
control names into the function. However, for now I've got it working along
the lines suggested by BruceM.
Click to expand...

There are good reasons to use good practices instead of poor
practices. Even if it takes some extra time and effort in
the short term, it will pay off many times over the long
haul.

A problem with referring to the form controls from multiple
forms is that you would then have to be careful to name the
controls exactly the same in all the forms where the
function is used. Not a restriction you want to have to
live with over the lifetime of your program. Good functions
just do their calculation without knowing anything about the
context that calls them.
You said "If you are going to move the code to a different module..."
I am curious what the alternatives would be. I could simply keep the
function private and copy it as needed into other form's modules, but this
would seem rather inelegant. Are there other alternatives?
Click to expand...

Having multiple copies of the same code is also a bad
practice because when you need to modify the code, you have
to remember to make the identical change in more than one
place. It is way to easy to forget that and/or make a
mistake in one of the changes.
I thought about using requery, but from what I could understand in my
reading, it sounded more radical than recalc.
Click to expand...

Requerying a form is heavy duty, but Requerying a text box
is light weight. I always thought that the text box
racalculation method should be named Recalc instead of
Requery, but I don't get to name thse things.
 
Back
Top