need help in with code

[Guest]

I have some code that they are too big for the form but I have to run them
anyway maybe using macr or mudule and I don't know how to do it.
exmple:in the form changing the gender will affect some other field I put
the code on the other field in lost focus and I need to put the same code on
the gender and the age but the code is to big.
How do I call the codes by using macro or module.
thanks

 

[John W. Vinson]

I have some code that they are too big for the form but I have to run them
anyway maybe using macr or mudule and I don't know how to do it.
exmple:in the form changing the gender will affect some other field I put
the code on the other field in lost focus and I need to put the same code on
the gender and the age but the code is to big.
How do I call the codes by using macro or module.
thanks

Just view the form's Properties, and select the control in question (gender
let's say). Select the Events tab, and choose some appropriate event -
AfterUpdate in this case.

Click the ... icon by the event, and choose Code Builder. Access will open the
VBA editor with a Sub and End Sub line; enter whatever code you like.

John W. Vinson [MVP]

 

[Guest]

Thanks but this is not what I mean.I know where to put the codes but I have
too much codes on my form and I want to see if I can call the codes from
macro or module
thanks again irit

 

[John W. Vinson]

Thanks but this is not what I mean.I know where to put the codes but I have
too much codes on my form and I want to see if I can call the codes from
macro or module
thanks again irit

In what way do you have "too much codes"? How much code do you have - tens of
thousands of lines? Are you getting an error message?

YOu can certainly call Subroutines or Functions in a standard module, from
code in a form; you can also execute a Function in a standard module by typing

=Functionname(parameters)

in an Event property. Or do you want to call routines in one form's Module
from a different Form?

John W. Vinson [MVP]

 

[Guest]

Ok.
I have a field in my form that calculate chair stand field but the result
sare depending on Gender and age too.
the code are long and they have alot of if statments.
In the form I put the codes on lost focus of the field(chair stand) works
fine.
I have to run the same codes on the gender and the age fields becuase if
there are mistakes in the age or gender I want the calculate field(chair
stand) update by itself
again.
the code are too big and I have alot of other calclate fields like those in
my form.
I need to ahev some way too run the codes from Macro or from Module to check
if there is any change in gender or age field.
How do I do it?
and how do I call the field from other place.
I create a module function(chair)
I copy the codes and I try to call it from the gender field in on change but
it does not work
any idea?

thanks much
irit

 

[John W. Vinson]

Ok.
I have a field in my form that calculate chair stand field but the result
sare depending on Gender and age too.
the code are long and they have alot of if statments.

Without seeing the code I can't comment, but multiple nested IF's can
sometimes be replaced by a Switch() function. See the Help. Or you might need
another table to look up a value (perhaps using a multifield join) rather than
using code.

In the form I put the codes on lost focus of the field(chair stand) works
fine.

Well... lostfocus fires even if you just tab through the field looking at it.
might the AfterUpdate event be better?

I have to run the same codes on the gender and the age fields becuase if
there are mistakes in the age or gender I want the calculate field(chair
stand) update by itself
again.
the code are too big and I have alot of other calclate fields like those in
my form.

So put the code in a separate Function - Public Function CalcChair(arg, arg,
arg) let's say - and call it from the AfterUpdate events of all these
controls.

I need to ahev some way too run the codes from Macro or from Module to check
if there is any change in gender or age field.
How do I do it?

Me!txtChair = CalcChair(this, that, theother)

and how do I call the field from other place.

Not understanding this. "call the field"? You can't "call" a field.

I create a module function(chair)
I copy the codes and I try to call it from the gender field in on change but
it does not work
any idea?

Not without your posting the code, no.

John W. Vinson [MVP]

 

[Guest]

Can I send you one exmple of my code to your e-mail .It will be really
helpful for me.
Thanks again
irit

 

[John W. Vinson]

Can I send you one exmple of my code to your e-mail .It will be really
helpful for me.

Well... ordinarily I reserve private EMail support to paying clients. I'm a
self-employed consultant donating time on the newsgroup. Can you perhaps post
the code here?

If the code is not something you want in public, I'll make an exception; email
it to jvinson <at> wysard of info <dot> com (edit out the blanks and edit in
the punctuation) and I'll take a look.

John W. Vinson [MVP]

 

[Guest]

Thanks,
This is some of the coderivate Sub Aerobicpower_Change()
'male
If Aerobicpower >= 55.1 And Gender = "2" And Age = "3" Then
Aerobic_Resylt = "90%"
Else
If Aerobicpower >= 52.1 And Aerobicpower <= 55 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "80%-89%"
Else
If Aerobicpower >= 49 And Aerobicpower <= 52 And Gender = "2" And Age = "3"
Then
Aerobic_Resylt = "70%-79%"
Else
If Aerobicpower >= 47.4 And Aerobicpower <= 48.9 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "60%-79%"
Else
If Aerobicpower >= 44.2 And Aerobicpower <= 47.3 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "50%-59%"
Else
If Aerobicpower >= 42.6 And Aerobicpower <= 44.1 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "40%-49%"
Else
If Aerobicpower >= 41 And Aerobicpower <= 42.5 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "30%-39%"
Else
If Aerobicpower >= 37.8 And Aerobicpower <= 40.9 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "20%-29%"
Else
If Aerobicpower >= 34.6 And Aerobicpower <= 37.7 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "10%-19%"
Else
If Aerobicpower < 34.6 And Gender = "2" And Age = "3" Then
Aerobic_Resylt = "0%-9%"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

* For each field I have a code for different age and for the gender.
I have around 20 fields that I use the same idea of the code but using
different numbers.
The code is on the field "Aerobicpower" on lost fucus or after update .
1.Is there a way to shorter the code?
2.the result of the number that the user imput are showing in the field
"Aerobic_Resylt" but,
I want the result changing every time the the user change the gender or age.
Here is my problem beacuse the codes are so big it does not let me put the
same code on the gender or the age.
How do I do that?

thanks again
Irit

 

[Pieter Wijnen]

Why not use a table for this & use Dlookup (or similar) instead?
Table:Aerobic
Gender : Byte
Age: Byte
APowerFrom: Double
APowerTo: Double

Pieter

 

[BruceM]

I will just weigh in for a moment here to offer some observations, and to
say that Select Case may help. Also, there's no need to test for Gender and
Age over and over. On that topic, if Gender is a text field, why not use
Male or Female? If it is not a text field (for instance, if it is an Option
Group), do not use quotes. Similarly, Age should probably be a calculated
field based on Date of Birth, but if you are using an Option Group then it
is a number, so again, no quotes.

Having said that, maybe something like:
If Me.Gender = 2 and Me.Age = 3 Then
Select Case Me.AerobicPower
Case >= 55.1
Me.AerobicResult = "90%"
Case >= 52.1
Me.AerobicResult = "80% - 89%"
etc.
End Select
End If

However, there are a lot of other questions and considerations. If Aerobic
Result is a calculation, it would probably be best to perform the calulation
as needed. There are other age groups for which you need to test, and one
other gender, which will complicate the code or the calculations. If the
records are maintained for any length of time, what happens when somebody
moves into another age bracket?

 

[John W. Vinson]

Thanks,
This is some of the coderivate Sub Aerobicpower_Change()
'male
If Aerobicpower >= 55.1 And Gender = "2" And Age = "3" Then
Aerobic_Resylt = "90%"
Else
If Aerobicpower >= 52.1 And Aerobicpower <= 55 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "80%-89%"
Else
If Aerobicpower >= 49 And Aerobicpower <= 52 And Gender = "2" And Age = "3"
Then
Aerobic_Resylt = "70%-79%"
Else
If Aerobicpower >= 47.4 And Aerobicpower <= 48.9 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "60%-79%"
Else

How about a solution that involves *no code at all*, and is much easier to
maintain if the results calculation changes?

Create a lookup table with fields Gender, Age, PowerLow, PowerHigh, and
AerobicResylt. Fill it with records like

2; 3; 55.1; 1000; "90%"
2; 3; 52.1; 55.1; "80%-89%"
2; 3; 49; 52.1; "70=79%"

and so on, for all the relevant combinations of the fields.

Join this table to your query using a "non equi join" - join it on Gender and
Age, and - initially - join the AerobicPower field to PowerLow. Then edit the
SQL JOIN clause from

AND yourtable.[AerobicPower] = [lookuptable].[PowerLow]

to

AND yourtable.[AerobicPower] >= lookuptable.[PowerLow] AND
yourtable.[AerobicPower] < lookuptable.[PowerHigh]

Note that I'm using >= on the low side, and < on the high side - if
AerobicPower is a Float or Double, it might be 48.9000000000002 and would fail
your test, but the < will include it.


John W. Vinson [MVP]

 

[Guest]

Thanks I will try to work on it and if I have more question you "will" hear
from me.
after I create this table I put it behind the field on lost focus or
somethig elase
irit

Thanks,
This is some of the coderivate Sub Aerobicpower_Change()
'male
If Aerobicpower >= 55.1 And Gender = "2" And Age = "3" Then
Aerobic_Resylt = "90%"
Else
If Aerobicpower >= 52.1 And Aerobicpower <= 55 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "80%-89%"
Else
If Aerobicpower >= 49 And Aerobicpower <= 52 And Gender = "2" And Age = "3"
Then
Aerobic_Resylt = "70%-79%"
Else
If Aerobicpower >= 47.4 And Aerobicpower <= 48.9 And Gender = "2" And Age =
"3" Then
Aerobic_Resylt = "60%-79%"
Else

How about a solution that involves *no code at all*, and is much easier to
maintain if the results calculation changes?

Create a lookup table with fields Gender, Age, PowerLow, PowerHigh, and
AerobicResylt. Fill it with records like

2; 3; 55.1; 1000; "90%"
2; 3; 52.1; 55.1; "80%-89%"
2; 3; 49; 52.1; "70=79%"

and so on, for all the relevant combinations of the fields.

Join this table to your query using a "non equi join" - join it on Gender and
Age, and - initially - join the AerobicPower field to PowerLow. Then edit the
SQL JOIN clause from

AND yourtable.[AerobicPower] = [lookuptable].[PowerLow]

to

AND yourtable.[AerobicPower] >= lookuptable.[PowerLow] AND
yourtable.[AerobicPower] < lookuptable.[PowerHigh]

Note that I'm using >= on the low side, and < on the high side - if
AerobicPower is a Float or Double, it might be 48.9000000000002 and would fail
your test, but the < will include it.


John W. Vinson [MVP]

 

[John W. Vinson]

after I create this table I put it behind the field on lost focus or
somethig elase

A table is not a field!

No, you would either include the table in the Query upon which your form is
based, using the suggested "non equi join"; or use DLookUp in the control
source of a textbox on the form. Again... *NO CODE* is needed *at all*. You're
not calculating this value, you're finding it in a Table!

John W. Vinson [MVP]

 

[Guest]

I lost....
step by step:
1.I create a lookup table
2.I try to use DLookUp in the control source of a textbox on the form

IIf ( [Main information]![Aerobicpower] = [Aerobic Power]![Aerobic Low],
[Main information]![Aerobicpower] > = [Aerobic Power]![Aerobic Low] And [Main
information]![Aerobicpower] < [Aerobic Power]![Aerobic High], [Main
information]![Aerobic Resylt]
what's wrong here?It does not work
3.Is it more sfae to use query and if it is I create a query with the main
table and the lookup table wgere do I put the sql join

Thanks irit

 

[John W. Vinson]

I lost....
step by step:
1.I create a lookup table
2.I try to use DLookUp in the control source of a textbox on the form

IIf ( [Main information]![Aerobicpower] = [Aerobic Power]![Aerobic Low],
[Main information]![Aerobicpower] > = [Aerobic Power]![Aerobic Low] And [Main
information]![Aerobicpower] < [Aerobic Power]![Aerobic High], [Main
information]![Aerobic Resylt]
what's wrong here?It does not work
3.Is it more sfae to use query and if it is I create a query with the main
table and the lookup table wgere do I put the sql join

I would recommend using the query; you may need to back up and use DLookUp if
the query is not updateable. The join would be set up in the query design
window as suggested in my previous post - join first to the Aerobic High, then
go into SQL view and edit the JOIN clause.

What you have above is NOT DLookUp and will not work!!! What are your table
and field names in the two tables? Do you have a control with a DLookUp? If
so, what is its Control Source?

John W. Vinson [MVP]

 

[Guest]

Thank you.
I have another gender to check and I have to go through 5 groups of age
which again make the code very massive.for my understanding I need to use the
same code in the gender and the age control.Not becuase it needs to change by
age all the time (it is a one time test) but the usere can make a mistake
with the age and all the others control already filled they need to be
updated and to match to the right result without the users go throuth them
again.
hope that you ahve some suggestion
irit

 

[BruceM]

My point was that you tested for the same gender and age variables in each
expression, which is not necessary. Also, it is not necessary to use < and

in the same line of code in this situation, since Access will stop

checking once the result evaluates to True. For instance:
If Aerobicpower >= 55.1 And Gender = "2" And Age = "3" Then
Aerobic_Resylt = "90%"
Else
' If AerobicPower is 55.1 or more, Access _
' won't get to this line of code. All you need _
' to do is check as shown
If Aerobicpower >= 52.1 And _
Gender = "2" And Age = "3" Then
Aerobic_Resylt = "80%-89%"

Note that I tested once for Gender = 2 and Age = 3, then checked within that
result. I wasn't saying you don't need to check, but rather that you don't
need to write the same code over and over.

I showed Select Case as a way of making the code more compact and easier to
write. You could also have used ElseIf in your situation. Using nested If
expressions will work, but it is much more difficult to manage changes to
the code if you need to add or remove something (which "If" does this "End
If" go with?).

Both Select Case and ElseIf are described in more detail in Help, but having
said that I think John Vinson's suggestion to use a lookup table is a clean
and efficient way of managing this situation, and has a lot to recommend it
over the approach of using code. My responses are intended to explain my
thinking and to point out some coding options when the situation arises,
rather than to advocate that you follow the coding route to solve the
problem.

 

[Guest]

Thank you all.I was trying the lookup table but I got very confused.
I creadte the lookup table.Then I have to create a query with the lookup
tabel and my main table.
1.How in the qury I connect them and what field.
2.Where I right the sql in the qury:
yourtable.[AerobicPower] = [lookuptable].[PowerLow]

AND yourtable.[AerobicPower] >= lookuptable.[PowerLow] AND
yourtable.[AerobicPower] < lookuptable.[PowerHigh]

there is a way that I will get some exmaple.
thanks irit

 

[BruceM]

John Vinson suggested the lookup table. It seemed to me a better choice
than what I had suggested, but I am afraid I can't really advise you about
the best way to implement it. On my own I may be able to figure it out, but
I would have trouble providing useful suggestions and troubleshooting if
something goes wrong. Since John suggested the lookup table, questions
about it should be posted in his part of this thread.
In reviewing his postings in this thread I see that he suggested a way to
proceed, although I do not know to which query he referred, or just what he
meant by "non equi join". Sorry I can't be more helpful, but I will say
again that it wasn't my suggestion.
If you do not get a reply in a day or two, try posting a new thread in which
you ask specifically about using a lookup table. Good luck.

Thank you all.I was trying the lookup table but I got very confused.
I creadte the lookup table.Then I have to create a query with the lookup
tabel and my main table.
1.How in the qury I connect them and what field.
2.Where I right the sql in the qury:
yourtable.[AerobicPower] = [lookuptable].[PowerLow]

AND yourtable.[AerobicPower] >= lookuptable.[PowerLow] AND
yourtable.[AerobicPower] < lookuptable.[PowerHigh]

there is a way that I will get some exmaple.
thanks irit

My point was that you tested for the same gender and age variables in
each
expression, which is not necessary. Also, it is not necessary to use <
and
checking once the result evaluates to True. For instance:
If Aerobicpower >= 55.1 And Gender = "2" And Age = "3" Then
Aerobic_Resylt = "90%"
Else
' If AerobicPower is 55.1 or more, Access _
' won't get to this line of code. All you need _
' to do is check as shown
If Aerobicpower >= 52.1 And _
Gender = "2" And Age = "3" Then
Aerobic_Resylt = "80%-89%"

Note that I tested once for Gender = 2 and Age = 3, then checked within
that
result. I wasn't saying you don't need to check, but rather that you
don't
need to write the same code over and over.

I showed Select Case as a way of making the code more compact and easier
to
write. You could also have used ElseIf in your situation. Using nested
If
expressions will work, but it is much more difficult to manage changes to
the code if you need to add or remove something (which "If" does this
"End
If" go with?).

Both Select Case and ElseIf are described in more detail in Help, but
having
said that I think John Vinson's suggestion to use a lookup table is a
clean
and efficient way of managing this situation, and has a lot to recommend
it
over the approach of using code. My responses are intended to explain my
thinking and to point out some coding options when the situation arises,
rather than to advocate that you follow the coding route to solve the
problem.