Format text box based on table field format

[Jeff Hunt]

Is there a way to have the format of a text box be based on the format of the
field it is referencing in the table?

I have a form with unbound text boxes that get bound during the form load.
Right now, the text boxes have no formatting assigned, so when binding one to
a text or date field there is no problem. When it is bound to a number, it
is not retaining the format options saved in the table (e.g., "standard"
format with 6 decimal places). If I set that format on text box itself, it
messes up the appearance when the text box gets bound to a field containing
dates or numbers that should have a different number of decimals. I can
probably code it to check which field it is being bound to and format
appropriately, but there are about 10 of these text boxes with about 8
different formats in place, so I was trying to avoid all that coding. I know
this form is a complicated way to do things, but the user wanted it to change
dynamically and that part is already built.

Anyone know of a short way to use the table's existing format settings, or
am I out of luck?

Thanks.

 

[Arvin Meyer [MVP]]

I don't know of anyway to do what you want except by writing a standard
module or form function, and then passing the arguments to the function.
Then you can use:

=FunctionName(txtBoxName)

 

[Jeff Boyce]

Jeff

I'm missing something...

From your description, it sounds like you are dynamically filling otherwise
empty/unbound textbox controls with whatever you decide to fill them with.

A more common approach is to use a form to display specific records (or
portions of a record), with the control ALWAYS pointing at the same
data/datatype, even if dynamically-loaded (rather than bound).

You described a "how" question. Now, "what"? What will having the ability
to do this allow you to do? What business need will be satisfied by getting
this working? I'm not asking out of curiosity, but because there may be
alternate approached to getting done what you need, if we knew what that
was...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Hunt]

Thanks for responding. Hmm. How to answer your questions w/o spending five
pages trying to explain all the whys of my design decisions.

This form is used for recording account changes for investments. For all
transactions there are about 6 common fields, so these are entered into bound
text boxes. Depending on the transaction type, there may be 1-10 different
pieces of additional data that need to be recorded, hence the unbound boxes.
Each transaction type has its own table to record these data, which are all
combined with the main table via a query, which is the source for this form.
When the form loads, it looks at the transaction type and assigns the text
box sources accordingly, hiding the ones that are not used for this
transaction (for example, TransType_A may need 3 extra fields, so text boxes
1-3 are assigned and 4-10 are hidden). I considered using bound text boxes
and just hiding them, but I didn't want to have dozens of overlapping
textboxes, mostly because it would make design and maintenance more difficult
(there are 13 transaction types, so it would mean 38 textboxes just for the
first three "variable fields").

This process works great for most of what the user wants; they just recently
brought up the formatting issue. The benefit gained, as far as I can tell,
is user preference (probably for visual confirmation/accuracy). Since it's
not strictly a requirement, I was hoping there was a quick, easy setting to
say the formatting should match the table. If not, oh well, I'll figure
something else out.

--
....jeff...
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Jeff

I'm missing something...

From your description, it sounds like you are dynamically filling otherwise
empty/unbound textbox controls with whatever you decide to fill them with.

A more common approach is to use a form to display specific records (or
portions of a record), with the control ALWAYS pointing at the same
data/datatype, even if dynamically-loaded (rather than bound).

You described a "how" question. Now, "what"? What will having the ability
to do this allow you to do? What business need will be satisfied by getting
this working? I'm not asking out of curiosity, but because there may be
alternate approached to getting done what you need, if we knew what that
was...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Thanks for the clarification, Jeff.

If I'm understanding correctly, each inital choice of [TransactionType] will
result in one and only one "transaction type table" being appropriate.
First, can you confirm that those multiple transaction type tables share
common data elements (each only contains data specific to the transaction
type)?

Another potential approach, rather than dynamically "binding" controls based
on transaction type table would be to create as many forms as you have
transaction type tables, then use a single subform control on your main form
and change the source of that subform for each transaction type.

NOTE -- the design you are using, and the alternate I just outline are BOTH
incredibly maintenance intensive. If the number of transaction types
change, you'll need to add a new form, add a new table, modify your
queries/code/?reports, ...

If there is ANY way you can keep a single common form and use a transaction
type table to list the different types, you could avoid all that
maintenance.

You may want to check on the degree to which your current data structure is
well-normalized. You could save yourself a lot of work in the long run...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for responding. Hmm. How to answer your questions w/o spending
five
pages trying to explain all the whys of my design decisions.

This form is used for recording account changes for investments. For all
transactions there are about 6 common fields, so these are entered into
bound
text boxes. Depending on the transaction type, there may be 1-10
different
pieces of additional data that need to be recorded, hence the unbound
boxes.
Each transaction type has its own table to record these data, which are
all
combined with the main table via a query, which is the source for this
form.
When the form loads, it looks at the transaction type and assigns the text
box sources accordingly, hiding the ones that are not used for this
transaction (for example, TransType_A may need 3 extra fields, so text
boxes
1-3 are assigned and 4-10 are hidden). I considered using bound text boxes
and just hiding them, but I didn't want to have dozens of overlapping
textboxes, mostly because it would make design and maintenance more
difficult
(there are 13 transaction types, so it would mean 38 textboxes just for
the
first three "variable fields").

This process works great for most of what the user wants; they just
recently
brought up the formatting issue. The benefit gained, as far as I can
tell,
is user preference (probably for visual confirmation/accuracy). Since
it's
not strictly a requirement, I was hoping there was a quick, easy setting
to
say the formatting should match the table. If not, oh well, I'll figure
something else out.

 

[Jeff Hunt]

You are more or less correct that each choice of [TransactionType] results in
using only one transaction type table. It's more complicated than that,
because there are several tables involved for each TransactionType (some for
account level changes, some for "portfolio" or "sub-account" changes, etc).
I'm no expert in database design, but I think it's pretty close to 3NF. Some
fields with the same data type (e.g., Price) are in more than one
"transaction type table" but only when it was in just two or three tables.
It was a judgment call to simplify some parts of the process, even though
it's not technically good practice. Any fields occurring more frequently
were broken out to their own tables.

I considered using separate subforms, but decided it was easier to maintain
code than forms. It would have actually resulted in more than 13 subforms,
because I am already using subforms to break out the individual stages of the
change, so it would have been at least 26 forms. Since this is only stage
one of a multi-stage project, I didn't want to have to potentially change
that many forms if we needed to add additional fields, buttons, etc. I have
all of the field assignments in two modules, broken out into functions by
stage and type. It looks really long, but it's actually very simple (just
repetitive) so maintaining it is not too bad (mostly it's just copy, paste,
and change the field number).

Unfortunately, it looks like the quick fix I was hoping for does not exist.
However, while thinking about your comments I started writing down some
pseudo code for changing the format the long way, and I don't think it's
going to be near as bad as I had feared. Your comments about using a
transaction type table got me thinking about a way to greatly simplify my
existing code to change the field sources. Sadly, that part of the database
works right now, so I can't spend the time to fix what isn't broke yet.

Thanks again for your help.

--
....jeff...
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Thanks for the clarification, Jeff.

If I'm understanding correctly, each inital choice of [TransactionType] will
result in one and only one "transaction type table" being appropriate.
First, can you confirm that those multiple transaction type tables share
common data elements (each only contains data specific to the transaction
type)?

Another potential approach, rather than dynamically "binding" controls based
on transaction type table would be to create as many forms as you have
transaction type tables, then use a single subform control on your main form
and change the source of that subform for each transaction type.

NOTE -- the design you are using, and the alternate I just outline are BOTH
incredibly maintenance intensive. If the number of transaction types
change, you'll need to add a new form, add a new table, modify your
queries/code/?reports, ...

If there is ANY way you can keep a single common form and use a transaction
type table to list the different types, you could avoid all that
maintenance.

You may want to check on the degree to which your current data structure is
well-normalized. You could save yourself a lot of work in the long run...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP