If Statement in Access form

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

Guest

I have a combo box, and I want to be able to display the number "1" in a
textbox in the case I select the value "USD" ifrom my combo box.
This is what I have right now and it is not working...
=IIf([Currency_Code]="USD",[Exchange_Rate]=1,[Exchange_Rate]=0)
Also, if the selected value is not "USD" then instead of displaying 0 in the
Exchange_Rate textbox i want to be able to enter any number.
Any ideas?

Thanks
 
I don't know where your code currently resides, but I would suggest you put
something like it in the After Update event of the Currency_Code combo box.
Also, if your code is trying to refer to controls on your form, the syntax is
not correct. If Currency_Code and Exchange_Rate are table fields, then you
need to address the form control rather than the table field.
After Update event of the combo box:
Me.txtExchangeRate = IIf(Me.cboCurrencyCode = "USD", 1, 0)
 
Also, I have two radio buttons that I want to use for selecting a field to
record a number into. I have two fields, Credit Amount and Debit Amount and
two radio buttons named Debit and Credit respectively. If I click Debit, I
want a particular amount to be recorded into Debit Amount field and when I
click Credit I want the amount to be recorded into the Credit Amount Field.
Hopefully this explained well what I am trying to do.

Thanks for your former answer.

Red_Star20

Klatuu said:
I don't know where your code currently resides, but I would suggest you put
something like it in the After Update event of the Currency_Code combo box.
Also, if your code is trying to refer to controls on your form, the syntax is
not correct. If Currency_Code and Exchange_Rate are table fields, then you
need to address the form control rather than the table field.
After Update event of the combo box:
Me.txtExchangeRate = IIf(Me.cboCurrencyCode = "USD", 1, 0)

Red_Star20 said:
I have a combo box, and I want to be able to display the number "1" in a
textbox in the case I select the value "USD" ifrom my combo box.
This is what I have right now and it is not working...
=IIf([Currency_Code]="USD",[Exchange_Rate]=1,[Exchange_Rate]=0)
Also, if the selected value is not "USD" then instead of displaying 0 in the
Exchange_Rate textbox i want to be able to enter any number.
Any ideas?

Thanks
Click to expand...
Click to expand...
 
Assuming these two radio buttons are part of an option group (If they are
not, they should be), the same technique would apply. Put code in the After
Update event of the option group to record the value in the correct text box
depending on which button is pressed.

Red_Star20 said:
Also, I have two radio buttons that I want to use for selecting a field to
record a number into. I have two fields, Credit Amount and Debit Amount and
two radio buttons named Debit and Credit respectively. If I click Debit, I
want a particular amount to be recorded into Debit Amount field and when I
click Credit I want the amount to be recorded into the Credit Amount Field.
Hopefully this explained well what I am trying to do.

Thanks for your former answer.

Red_Star20

Klatuu said:
I don't know where your code currently resides, but I would suggest you put
something like it in the After Update event of the Currency_Code combo box.
Also, if your code is trying to refer to controls on your form, the syntax is
not correct. If Currency_Code and Exchange_Rate are table fields, then you
need to address the form control rather than the table field.
After Update event of the combo box:
Me.txtExchangeRate = IIf(Me.cboCurrencyCode = "USD", 1, 0)

Red_Star20 said:
I have a combo box, and I want to be able to display the number "1" in a
textbox in the case I select the value "USD" ifrom my combo box.
This is what I have right now and it is not working...
=IIf([Currency_Code]="USD",[Exchange_Rate]=1,[Exchange_Rate]=0)
Also, if the selected value is not "USD" then instead of displaying 0 in the
Exchange_Rate textbox i want to be able to enter any number.
Any ideas?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
I did what you said and got an error message. it is looking for the macro
"Me" and says it does not exist.....

Klatuu said:
Assuming these two radio buttons are part of an option group (If they are
not, they should be), the same technique would apply. Put code in the After
Update event of the option group to record the value in the correct text box
depending on which button is pressed.

Red_Star20 said:
Also, I have two radio buttons that I want to use for selecting a field to
record a number into. I have two fields, Credit Amount and Debit Amount and
two radio buttons named Debit and Credit respectively. If I click Debit, I
want a particular amount to be recorded into Debit Amount field and when I
click Credit I want the amount to be recorded into the Credit Amount Field.
Hopefully this explained well what I am trying to do.

Thanks for your former answer.

Red_Star20

Klatuu said:
I don't know where your code currently resides, but I would suggest you put
something like it in the After Update event of the Currency_Code combo box.
Also, if your code is trying to refer to controls on your form, the syntax is
not correct. If Currency_Code and Exchange_Rate are table fields, then you
need to address the form control rather than the table field.
After Update event of the combo box:
Me.txtExchangeRate = IIf(Me.cboCurrencyCode = "USD", 1, 0)

:

I have a combo box, and I want to be able to display the number "1" in a
textbox in the case I select the value "USD" ifrom my combo box.
This is what I have right now and it is not working...
=IIf([Currency_Code]="USD",[Exchange_Rate]=1,[Exchange_Rate]=0)
Also, if the selected value is not "USD" then instead of displaying 0 in the
Exchange_Rate textbox i want to be able to enter any number.
Any ideas?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Please post the offending code. the Me. designation should not be a problem
as it is the designation for the form or report that has the focus, a
shortcut, if you will:
forms!MyFormName!MyControlName = Me.MyControlName

Red_Star20 said:
I did what you said and got an error message. it is looking for the macro
"Me" and says it does not exist.....

Klatuu said:
Assuming these two radio buttons are part of an option group (If they are
not, they should be), the same technique would apply. Put code in the After
Update event of the option group to record the value in the correct text box
depending on which button is pressed.

Red_Star20 said:
Also, I have two radio buttons that I want to use for selecting a field to
record a number into. I have two fields, Credit Amount and Debit Amount and
two radio buttons named Debit and Credit respectively. If I click Debit, I
want a particular amount to be recorded into Debit Amount field and when I
click Credit I want the amount to be recorded into the Credit Amount Field.
Hopefully this explained well what I am trying to do.

Thanks for your former answer.

Red_Star20

:

I don't know where your code currently resides, but I would suggest you put
something like it in the After Update event of the Currency_Code combo box.
Also, if your code is trying to refer to controls on your form, the syntax is
not correct. If Currency_Code and Exchange_Rate are table fields, then you
need to address the form control rather than the table field.
After Update event of the combo box:
Me.txtExchangeRate = IIf(Me.cboCurrencyCode = "USD", 1, 0)

:

I have a combo box, and I want to be able to display the number "1" in a
textbox in the case I select the value "USD" ifrom my combo box.
This is what I have right now and it is not working...
=IIf([Currency_Code]="USD",[Exchange_Rate]=1,[Exchange_Rate]=0)
Also, if the selected value is not "USD" then instead of displaying 0 in the
Exchange_Rate textbox i want to be able to enter any number.
Any ideas?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Here is the error msg, I get:

2005 SBA International Programs Accounts can't find the macro 'Me.'

The macro (or its macro group) does't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under.

Offending Code:

Me.Exchange_Rate = IIf(Me.Currency_Code = "USD", 1, 0)

I have this in the After Update property....
 
Assuming Exchange_Rate and Currency_Code are control names on your form, this
does not make any sense. If you have a macro group named Me, rename it.
Other than that, I don't have a clue what the problem could be.
 
Well, I changed the control names around to see if that does the trick but i
am still getting the same error msg. I have no macros at all, so that should
not be a problem either. I am puzzeled.... Is there any way I could send it
to you so u can look at it? This is probably not a common practice here, but
I am just desperate to solve this as I have been working on it for quite some
time. I appreciate all your help.

Red_Star20
 
Another thing,

Do u by any chance know how to record a calculated control's content into a
table? To my knowledge calculated controls are unbound by nature, so it would
not be possible, but I am sure there is another way.
Let's say I have two textboxes, textbox 1 and textbox 2 both containing
number.
I also want a third textbox in which the product of the two numbers show up
when clicking a command button. I can write in the 3rd text box's control
source the following: =[textbox1]*[textbox2] and this does the
multiplication, but I need to get a command button involved and make the 3rd
textbox's content stored in my table...

Any clue on this?

Red_Star
 
Red_Star20 said:
Another thing,

Do u by any chance know how to record a calculated control's content
into a table? To my knowledge calculated controls are unbound by
nature, so it would not be possible, but I am sure there is another
way.
Let's say I have two textboxes, textbox 1 and textbox 2 both
containing number.
I also want a third textbox in which the product of the two numbers
show up when clicking a command button. I can write in the 3rd text
box's control source the following: =[textbox1]*[textbox2] and this
does the multiplication, but I need to get a command button involved
and make the 3rd textbox's content stored in my table...

Any clue on this?
Click to expand...

You could clear the ControlSource you have now and in the AfterUpdate event
of BOTH textbox1 and textbox2 have code...

Me.textbox3 = Me.textbox1 * Me.textbox2

BUT **** This is a BAD IDEA. If either field is ever changed by some method
other than your form you will have bad data and no way of knowing which
field is incorrect. That is (one of the reasons) why storing calculated
data is simply NOT done except in very rare cases.

What you SHOULD do is create a SELECT query based on your table (from which
you have removed this field) and in the query you perform the calculation
for field1* field2. Then you simply substitute that query every place you
are now using the table. The value for field3 will be there, it will
automatically update whenever you change either of the other field values,
and it will always be correct.

If you were doing this in Excel would you put an expression into the third
column to do a live calculation or would you create a macro that looped
through all rows in the sheet multiplying the value in columnA by the value
in columnB and place the result into columnC? The latter sounds pretty
ridiculous doesn't it? It is the equivelant of storing the result of a
calculation in a database table.
 
I did what you said and got an error message. it is looking for the macro
"Me" and says it does not exist.....
Click to expand...

PMFJI - I suspect that you took Klatuu too literally, and typed that
line of code into the AfterUpdate Event line in the control's
properties. What he was suggesting was instead to click on the ...
icon by the AfterUpdate event, invoke the Code Builder, and put that
line into the Private Sub controlname_AfterUpdate() subroutine,
between that line and the End Sub that Access will give you.

The Property on the form properties window should show either

[Event Procedure]

or the name of a valid macro - and if you have Me.<something> that's
not a valid macro!

John W. Vinson[MVP]
 
I also want a third textbox in which the product of the two numbers show up
when clicking a command button. I can write in the 3rd text box's control
source the following: =[textbox1]*[textbox2]
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]
 
Back
Top