Referring to another control in a form

[Guest]

I want to refer to another control in my form that will use a formula like
the "if, then" formula. I know how to do the "if, then" formula, however, I
am going to be using more than one "if" and I do not know how to write the
formula correctly. What I want to do is say If Control1 = Answer1, Answer2,
Answer3, Answer4, etc., Then "Whatever the answer will be".

I just need help writing the formula where I will use several "if's." For
example, I know in excel you can only use 7 strings of an "if, then" formula
for one cell. I think I will end up having about 10 strings for this control
to use. Is this possible???
Thank you.

 

[Guest]

This is a great time to learn about the Select Case statement. if you have
more than one condition to evaluate, it is the best.

Select Case Me.Control1
Case 1
Me.Answer = "It is One"
Case 2
Me.Answer = "It is Two"
Case 3
Me.Answer = "It is Three"
Case Else
Me.Answer = "I Dont Know"
End Select

Read more detail about Select Case in VBA Help

 

[Steve]

Klatuu is by far the preferred solution. But to answer your question, code
to string out If statements looks like:
If ..... Then
ElseIF .... Then

ElseIF .... Then

ElseIF .... Then

ElseIF .... Then

Else

End If

If the conditions in the If ... statement are not satisfied, Access goes
down the list of ElseIf .... statements in order checking the conditions in
those statements. If no conditions in the If .... or the ElseIf ....
statements are met, Access executes the code in the Else statement.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Here is what I want to do because all of this stuff is confusing me.
I have a list of doctor specialties that are "core". A few from this list
are:
Cardiology
Oncology
Urology
Psychiatry
etc.

In my form, I have a control from my table [Core or NC]. I want this field
to display Core for the list of "core" specialties above and NC for the
specialties that are Non-Core.

I just want the easiest way to do this in my form. There are about 20
specialties that are "core." I did not know if the "if, then" would work or
not, but the stuff that was said in the previous replies confused me.

Thanks for all of your help..

 

[UpRider]

Chris, do you have a textbox on your form that displays the specialties? If
so, what is its name? What is the
name of the table field it is bound to?
Do you type the Specialty in this textbox when you update the table, or is
it a combo box so that you can select
the specialty from a dropdown list? Or does the textbox just display the
specialty and you don't change it?

Do you want the word "Core" or "Non Core" to display in a different
textbox? If so, what is its name?
You do not want to store these words in your table. It's not necessary.

And the form we are working on, is it a data input form? Does it have a
subform? Is Specialty on a subform, or
is the textbox in which you want "Core" or Non Core" on a subform? Or are
both on the main form, or maybe
one on the main form and the other on a subform? Or maybe the is no subform
(makes it easier).

Given all these answers, we can get you up and running in short order.

UpRider

Here is what I want to do because all of this stuff is confusing me.
I have a list of doctor specialties that are "core". A few from this list
are:
Cardiology
Oncology
Urology
Psychiatry
etc.

In my form, I have a control from my table [Core or NC]. I want this
field
to display Core for the list of "core" specialties above and NC for the
specialties that are Non-Core.

I just want the easiest way to do this in my form. There are about 20
specialties that are "core." I did not know if the "if, then" would work
or
not, but the stuff that was said in the previous replies confused me.

Thanks for all of your help..

I want to refer to another control in my form that will use a formula
like
the "if, then" formula. I know how to do the "if, then" formula,
however, I
am going to be using more than one "if" and I do not know how to write
the
formula correctly. What I want to do is say If Control1 = Answer1,
Answer2,
Answer3, Answer4, etc., Then "Whatever the answer will be".

I just need help writing the formula where I will use several "if's."
For
example, I know in excel you can only use 7 strings of an "if, then"
formula
for one cell. I think I will end up having about 10 strings for this
control
to use. Is this possible???
Thank you.

 

[Guest]

UpRider,

I have a form with no subform. I have a textbox "Core or NC" which is bound
to the field "Core or NC" in my table. What happens is:

I get an update in Excel that I transfer to Access. I created a table with
all of the fields that I receive. Then I based my form off of that table.
One textbox displays "Specialties" that are listed for that record. These
never change. Each record has different specialties though, obviously, some
the same.

I want, in another textbox, for the field to say "Core" or "NC" depending on
if the specialty is Core or Non-Core. There is a list of specialties that
are Core and the rest are Non-Core. Thats all I want to do. Please let me
know if this clears everything up. Thanks for all of your help.

Chris, do you have a textbox on your form that displays the specialties? If
so, what is its name? What is the
name of the table field it is bound to?
Do you type the Specialty in this textbox when you update the table, or is
it a combo box so that you can select
the specialty from a dropdown list? Or does the textbox just display the
specialty and you don't change it?

Do you want the word "Core" or "Non Core" to display in a different
textbox? If so, what is its name?
You do not want to store these words in your table. It's not necessary.

And the form we are working on, is it a data input form? Does it have a
subform? Is Specialty on a subform, or
is the textbox in which you want "Core" or Non Core" on a subform? Or are
both on the main form, or maybe
one on the main form and the other on a subform? Or maybe the is no subform
(makes it easier).

Given all these answers, we can get you up and running in short order.

UpRider

Here is what I want to do because all of this stuff is confusing me.
I have a list of doctor specialties that are "core". A few from this list
are:
Cardiology
Oncology
Urology
Psychiatry
etc.

In my form, I have a control from my table [Core or NC]. I want this
field
to display Core for the list of "core" specialties above and NC for the
specialties that are Non-Core.

I just want the easiest way to do this in my form. There are about 20
specialties that are "core." I did not know if the "if, then" would work
or
not, but the stuff that was said in the previous replies confused me.

Thanks for all of your help..

I want to refer to another control in my form that will use a formula
like
the "if, then" formula. I know how to do the "if, then" formula,
however, I
am going to be using more than one "if" and I do not know how to write
the
formula correctly. What I want to do is say If Control1 = Answer1,
Answer2,
Answer3, Answer4, etc., Then "Whatever the answer will be".

I just need help writing the formula where I will use several "if's."
For
example, I know in excel you can only use 7 strings of an "if, then"
formula
for one cell. I think I will end up having about 10 strings for this
control
to use. Is this possible???
Thank you.

 

[UpRider]

OK.
Here's what we do. When the form becomes current, i.e. shows the current
record from the table, we will look at the specialty textbox and depending
on what we find there, put "Core" or "NC" in another textbox. Please change
the name of that textbox to txtCoreOrNC.
So, go to design mode for the form. Click on the little box with the square
in the upper left hand corner of the form.
In the property sheet (F4 if you don't see it), 'Event' tab, drop down the
'on current' and select 'Event Procedure', then click on the ... dots. A
code window will open up with the cursor at the proper place. Paste in that
spot:

Select Case [Core or NC]
Case "Obstetrics","Cardiology","Internal Medicine" 'your list of
specialties
txtCoreOrNC = "Core"
Case else
txtCoreOrNC = "NC"
End Select

Close the code window, switch from form design to form view and check it
out.

This will work fine now. Later, a more sophisticated approach would be to
put the specialties in a table and
have the code check the table for Core specialties. That way, if things
change, you wouldn't have to change the list above in your code, you would
just add/delete specialties from the table and it would automatically work
on the form.

UpRider

UpRider,

I have a form with no subform. I have a textbox "Core or NC" which is
bound
to the field "Core or NC" in my table. What happens is:

I get an update in Excel that I transfer to Access. I created a table
with
all of the fields that I receive. Then I based my form off of that table.
One textbox displays "Specialties" that are listed for that record. These
never change. Each record has different specialties though, obviously,
some
the same.

I want, in another textbox, for the field to say "Core" or "NC" depending
on
if the specialty is Core or Non-Core. There is a list of specialties that
are Core and the rest are Non-Core. Thats all I want to do. Please let
me
know if this clears everything up. Thanks for all of your help.

Chris, do you have a textbox on your form that displays the specialties?
If
so, what is its name? What is the
name of the table field it is bound to?
Do you type the Specialty in this textbox when you update the table, or
is
it a combo box so that you can select
the specialty from a dropdown list? Or does the textbox just display the
specialty and you don't change it?

Do you want the word "Core" or "Non Core" to display in a different
textbox? If so, what is its name?
You do not want to store these words in your table. It's not necessary.

And the form we are working on, is it a data input form? Does it have a
subform? Is Specialty on a subform, or
is the textbox in which you want "Core" or Non Core" on a subform? Or
are
both on the main form, or maybe
one on the main form and the other on a subform? Or maybe the is no
subform
(makes it easier).

Given all these answers, we can get you up and running in short order.

UpRider

Here is what I want to do because all of this stuff is confusing me.
I have a list of doctor specialties that are "core". A few from this
list
are:
Cardiology
Oncology
Urology
Psychiatry
etc.

In my form, I have a control from my table [Core or NC]. I want this
field
to display Core for the list of "core" specialties above and NC for the
specialties that are Non-Core.

I just want the easiest way to do this in my form. There are about 20
specialties that are "core." I did not know if the "if, then" would
work
or
not, but the stuff that was said in the previous replies confused me.

Thanks for all of your help..

:

I want to refer to another control in my form that will use a formula
like
the "if, then" formula. I know how to do the "if, then" formula,
however, I
am going to be using more than one "if" and I do not know how to write
the
formula correctly. What I want to do is say If Control1 = Answer1,
Answer2,
Answer3, Answer4, etc., Then "Whatever the answer will be".

I just need help writing the formula where I will use several "if's."
For
example, I know in excel you can only use 7 strings of an "if, then"
formula
for one cell. I think I will end up having about 10 strings for this
control
to use. Is this possible???
Thank you.