Analysing text boxes on sub form/simplifying evet procedures

[David Gartrell]

Hi there,

I wonder if anyone can help me with a couple of problems I'm having with
Access 2000.

Firstly, I have a form and within that a subform that contains 10 text boxes
called weight1, weight2, weight3 and so on up to weight10. On the main form
I have some VB code that checks the contents of all ten boxes. At the moment
I have a seperate piece of code to check each text box when I would much
rather reference each one via a simple for...next loop. I have tried to
accomplish this but never seem to have any luck getting it to work. I know
that if everything resided on the main form it would be an easy task and i
would use some code such as:

for loop1 = 1 to 10
if me("weight"+format$(loop1))=100 then foundbox.visible = true
next

What I can't work out is how change the second line so it will look at each
box on the subform as part of the loop. I understand the principle of
[forms]![my subform]! etc etc but can't work out the correct syntax for
referencing them within a simple loop.



The other problem I'm having is that I also have an 'after update' event
procedure
against each of the ten text boxes above. All that each event procedure
does is point to the same subroutine that adds up the contents of all ten
boxes to provide a total. is there some way that I can have just one event
procedure that will serve all ten text boxes instead.

Basically what I'm trying to with all the above is cut down dramatically on
the amount of code and therefore hopefiully make the whole thing more
efficient.

If anyone can point me in the right direction or give me some advice then
I'd be very grateful.

Thanks V much

David.

 

[Graham Mandeno]

Hi David

<start of sermon>
It would seem that your database is not well normalised, and this might well
come back to bite you in the future. What happens if there are fewer than
ten weights? Even worse, what if there are more than 10? What do you do if
you want to find all weights in a particular range? What about the average
of all weights over a selection of records? I strongly recommend you
consider normalising the data to include only one weight per record.
<end of sermon>

The "Me" reserved word refers to the current class - in this case the form
in which the code is executing. You need to refer instead to the form which
is contained in the subform control:
Me![My Subform Control].Form("weight" & loop1)

The AfterUpdate *event property* does not have to be an *event procedure*.
It can be any function, either Public, or Private to the current form. So,
you can write a single function as follows:

Private Function UpdateTotal()
... some code
End Function

Then you can set the AfterUpdate property for each of the textboxes to refer
to the function:
=UpdateTotal()

BUT <I can feel another sermon coming on> you should not store calculated
data in your table because (a) it is unnecessary and (b) it can easily get
out of step with the data it comprises. If the composite data is in the
same record, you can use a calculated field in your recordsource query to
perform the calculation, and bind that field to a textbox on your form. If
you cane one weight per record (as suggested) you can include a
Sum([FieldName]) expression in the "Total" textbox.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi there,

I wonder if anyone can help me with a couple of problems I'm having with
Access 2000.

Firstly, I have a form and within that a subform that contains 10 text boxes
called weight1, weight2, weight3 and so on up to weight10. On the main form
I have some VB code that checks the contents of all ten boxes. At the moment
I have a seperate piece of code to check each text box when I would much
rather reference each one via a simple for...next loop. I have tried to
accomplish this but never seem to have any luck getting it to work. I know
that if everything resided on the main form it would be an easy task and i
would use some code such as:

for loop1 = 1 to 10
if me("weight"+format$(loop1))=100 then foundbox.visible = true
next

What I can't work out is how change the second line so it will look at each
box on the subform as part of the loop. I understand the principle of
[forms]![my subform]! etc etc but can't work out the correct syntax for
referencing them within a simple loop.



The other problem I'm having is that I also have an 'after update' event
procedure
against each of the ten text boxes above. All that each event procedure
does is point to the same subroutine that adds up the contents of all ten
boxes to provide a total. is there some way that I can have just one event
procedure that will serve all ten text boxes instead.

Basically what I'm trying to with all the above is cut down dramatically on
the amount of code and therefore hopefiully make the whole thing more
efficient.

If anyone can point me in the right direction or give me some advice then
I'd be very grateful.

Thanks V much

David.