How can i add a VBA function in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating an Access database for a biology lab. we have 3 values in our
form. pH value, Concentration, and test result. if both pH value or
Concentration be in the acceptable criteria, the value in test result should
automatically return ''pass" , otherwise it should return "fail". I don't
have any idea about VBA. Is there anyway that i can use the Access functions
for this? If not, can you tell me what is the VBA code for this and how
should i generate it? I really appreciate your help in this.
 
Shery said:
I am creating an Access database for a biology lab. we have 3 values
in our form. pH value, Concentration, and test result. if both pH
value or Concentration be in the acceptable criteria, the value in
test result should automatically return ''pass" , otherwise it should
return "fail". I don't have any idea about VBA. Is there anyway
that i can use the Access functions for this? If not, can you tell me
what is the VBA code for this and how should i generate it? I
really appreciate your help in this.
Click to expand...

Depending on what constitutes "acceptable criteria", you may be able to
do this easily without writing your own function. For example, suppose
pH has to be between 6 and 8 and Concentration has to be between, oh, 25
and 50 (obviously I have no idea what would make sense for this test).
Then you could define a calculated control on the form with this
controlsource:

=IIf(([pH] >= 6 And [pH] <= 8) And
([Concentration] >= 25 And [Concentration] <= 50),
"pass",
"fail")

Note that I wrote the above on multiple lines for clarity, but actually
it would all be on the same line in a control's ControlSource property.

If you want to use this calculation in other places as well, you might
want to do it as a calculated field in a query, and then base your form
on the query.
 
Thanks Dirk. Just to clearify, the first line of the source is IIF or IF? I
am using the property part of the 'test result' textbox, I will pick Data /
Control Source and i am going to add your code in there. Is it right?

Dirk Goldgar said:
Shery said:
I am creating an Access database for a biology lab. we have 3 values
in our form. pH value, Concentration, and test result. if both pH
value or Concentration be in the acceptable criteria, the value in
test result should automatically return ''pass" , otherwise it should
return "fail". I don't have any idea about VBA. Is there anyway
that i can use the Access functions for this? If not, can you tell me
what is the VBA code for this and how should i generate it? I
really appreciate your help in this.
Click to expand...

Depending on what constitutes "acceptable criteria", you may be able to
do this easily without writing your own function. For example, suppose
pH has to be between 6 and 8 and Concentration has to be between, oh, 25
and 50 (obviously I have no idea what would make sense for this test).
Then you could define a calculated control on the form with this
controlsource:

=IIf(([pH] >= 6 And [pH] <= 8) And
([Concentration] >= 25 And [Concentration] <= 50),
"pass",
"fail")

Note that I wrote the above on multiple lines for clarity, but actually
it would all be on the same line in a control's ControlSource property.

If you want to use this calculation in other places as well, you might
want to do it as a calculated field in a query, and then base your form
on the query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Shery said:
Thanks Dirk. Just to clearify, the first line of the source is IIF
or IF? I am using the property part of the 'test result' textbox, I
will pick Data / Control Source and i am going to add your code in
there. Is it right?
Click to expand...

It is "IIf", not just "If" -- it stands for "Immediate If", I think.
And yes, the Control Source property of the Data tab on the text box's
property sheet is where it should go. Do note two things:

(1) There should be no actual field in the table named "test result",
since there is no point in trying to store a calculated field.

(2) As I mentioned in my original post, the expression should be all on
one line.

Let me know if you have any trouble with this.
 
Back
Top