Textbox controlsource depending on a table field

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

In a form where I have a calculated textfield, is it possible to let the
user choose between two ways how this field is calculated, from another
form/table field? The user would choose this "globally" so that it would
affect every new record.

In a settingstable one field is "addtax". Based on the value here (yes/no,
1-2, whole formula..?) the controlsource for the calculated textbox would be
determined. For example, i the "addtax" field in the settings table is "1",
the calculated textbox recordsource would be
"=[hours]*[hourlyrate]*[taxpercent]/100", if the "addtax" field in the
settings table is "2", the calculated textbox recordsource would be
"=([hours]*[hourlyrate])*(1-1/(1+([taxpercent]/100)))".

Tried IIf, tried to put the whole formula into the field, tried If Then
Else.. but without success.

Jen
 
Marty, thank you for a _wery_ good answer. Specially the part where you
tell that this (IIF) is the way to go, so I know I'm on track.

When I put this "=IIf([egna_uppgifter!AddTax]=1;10;20)" on a new forms only
textbox controlsource it works ok, that is if the value "AddTax" in the
table "egna_uppgifter" contains the number 1 then the textbox will show
"10", and if the value "AddTax" in the table "egna_uppgifter" contains the
number 2 then the textbox will show "20".

However when I put this on my "real" forms textbox (which btw is a subform)
all I get is #Name? in the textbox. Tried to put the formulas instead of
10,20 like
"=IIf([egna_uppgifter!AddTax]=1;[timmar]*[timdebitering]*[momsprocent]/100;(
[timmar]*[timdebitering])*(1-1/(1+([momsprocent]/100))))" I got the same
#Name?. Both formulas put on their own in the controlsource of the textbox
works ok so there shouldn't be a problem there.

One more thing, as soon as I write in
"=IIf([egna_uppgifter!AddTax]=1,10,20)" access changes the "," to ";". I
have english Windows and English Access but my national settings are Swedish
if that would make any difference.

Any ideas?

Jen, not knowing Access that good but keen to learn.
 
-----Original Message-----
Marty, thank you for a _wery_ good answer. Specially the part where you
tell that this (IIF) is the way to go, so I know I'm on track.

When I put this "=IIf([egna_uppgifter!AddTax]=1;10;20)" on a new forms only
textbox controlsource it works ok, that is if the value "AddTax" in the
table "egna_uppgifter" contains the number 1 then the textbox will show
"10", and if the value "AddTax" in the
Click to expand...
table "egna_uppgifter" contains the
number 2 then the textbox will show "20".

However when I put this on my "real" forms textbox (which btw is a subform)
all I get is #Name? in the textbox. Tried to put the formulas instead of
10,20 like
"=IIf([egna_uppgifter!AddTax]=1;[timmar]*[timdebitering]* [momsprocent]/100;(
[timmar]*[timdebitering])*(1-1/(1+([momsprocent]/100))))" I got the same
#Name?. Both formulas put on their own in the controlsource of the textbox
works ok so there shouldn't be a problem there.

One more thing, as soon as I write in
"=IIf([egna_uppgifter!AddTax]=1,10,20)" access changes the "," to ";". I
have english Windows and English Access but my national settings are Swedish
if that would make any difference.

Any ideas?

Jen, not knowing Access that good but keen to learn.


.
Glad I was able to help a little, but I'm not sure what
Click to expand...
is wrong at this point. Perhaps you have named two fields
the same and your recordsource is a join query, in which
case you need to distinguish between them by specifying a
little more. For instance, in one of my queries, I have
InvoiceID twice. Once for the Invoice table and once for
the Orders table. If I just say InvoiceID in an
expression, Access doesn't know which InvoiceID I am
refering to. So I have to be more specific by saying
Orders.InvoiceID, or Invoice.InvoiceID.

I couldn't comment on the Swedish/vs English settings.
I'm sure you can also error trap that possibility by
backing up to my original example and see if it makes a
difference.

You know, a lot of programming has to do with eliminating
the potential problems, not by having or writing correct
code right out of the chute. So if you are concerned that
the Swedish/English situation is a potential problem, find
out by experimenting again with simple example code!

I'm fairly new to all of this myself, but just think it's
a good idea to help when possible. With time, we will
both be experts! Best of luck Jen!
 
Glad I was able to help a little, but I'm not sure what
is wrong at this point. Perhaps you have named two fields
the same and your recordsource is a join query, in which
case you need to distinguish between them by specifying a
little more. For instance, in one of my queries, I have
InvoiceID twice. Once for the Invoice table and once for
the Orders table. If I just say InvoiceID in an
expression, Access doesn't know which InvoiceID I am
refering to. So I have to be more specific by saying
Orders.InvoiceID, or Invoice.InvoiceID.

I couldn't comment on the Swedish/vs English settings.
I'm sure you can also error trap that possibility by
backing up to my original example and see if it makes a
difference.

You know, a lot of programming has to do with eliminating
the potential problems, not by having or writing correct
code right out of the chute. So if you are concerned that
the Swedish/English situation is a potential problem, find
out by experimenting again with simple example code!

I'm fairly new to all of this myself, but just think it's
a good idea to help when possible. With time, we will
both be experts! Best of luck Jen!
Click to expand...

Marty, finally got it working, thanks to you! My error (#Name?) was because
I hadn't bound the AddTax field to the forms underlying query.
Thank you wey much for your time and effort.
 
Back
Top