|
|
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.
--
....jeff...
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
|
|
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)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"Jeff Hunt" <JeffHunt[ at ]discussions.microsoft.com> wrote in message
news:1F6E31F7-AF05-4478-A15B-BCE249AA672C[ at ]microsoft.com...
[Quoted Text] > 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.
> --
> ...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 Hunt" <JeffHunt[ at ]discussions.microsoft.com> wrote in message
news:1F6E31F7-AF05-4478-A15B-BCE249AA672C[ at ]microsoft.com...
[Quoted Text] > 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.
> --
> ...jeff...
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
|
|
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 Boyce" wrote:
[Quoted Text] > 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" <JeffHunt[ at ]discussions.microsoft.com> wrote in message
> news:1F6E31F7-AF05-4478-A15B-BCE249AA672C[ at ]microsoft.com...
> > 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.
> > --
> > ...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
"Jeff Hunt" <JeffHunt[ at ]discussions.microsoft.com> wrote in message
news:402FEA99-D099-4FB3-8E0D-004C4B99B34E[ at ]microsoft.com...
[Quoted Text] > 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 Boyce" wrote:
>
>> 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" <JeffHunt[ at ]discussions.microsoft.com> wrote in message
>> news:1F6E31F7-AF05-4478-A15B-BCE249AA672C[ at ]microsoft.com...
>> > 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.
>> > --
>> > ...jeff...
>> > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>>
>>
>>
|
|
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...
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
"Jeff Boyce" wrote:
[Quoted Text] > 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
>
>
|
|
|