Change control for one record in subform, not all?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I'm trying to provide an option for either text or numeric entry; the form
in question has four columns, the last three of which are almost always
dollar values. Rather than make them all text, I thought of creating a
second set of fields that were text, changing them to not visible, and
placing them right on top of the existing numeric fields. Then I created a
shortcut menu macro with code that, I thought, would allow the user to
toggle between the two fields (the 2nd field is colored differently, so you
know which one you're using). The problem I hadn't thought of is that it
changes *all* the records. I'm looking for a way to change a control on
_one_ record. The other alternative - which I dislike - is to put the 3
other fields on the form. This would be a data entry nightmare.

TIA!

Mike
 
Mike,

The only idea I can think of here, as a general skeleton concept...

Put unbound textboxes on the form for each of the existing "field
pairs", over the top of the existing controls. Set the Control Source
property of these to something like this...
=Nz([OneOfYourFields],[TheOtherField])
.... so that whichever one has data will have its data shown on the form.
These controls are, of course, not editable. On the Enter event of
these controls, put code something like this...
If YourToggle = "Currency" Then
Me.TheCorrespondingCurrencyControl.SetFocus
Else
Me.TheCorrespondingTextControl.SetFocus
End If

So, what should happen, is the applicable control, according to your
selection, will "come to the front" while you do your data entry/edit,
and then, whichever of the two data fields you use, the data will be
shown in the calculated control. Hope that makes sense.
 
I hadn't thought about using an unbound text box, which got me thinking of
another idea, but I'm still having the same problem in that the change
effects every field on the subform. Your idea of using the text box made me
think of using it to input, than testing the datatype (isnumeric) to
determine which field to use. This has the benefit of being invisible to
the user, though it *does* increase the chance of errors (eg, someone types
in $1,234). I'm thinking I'll need to code this using VBA in order to apply
this to a specific record in the subform - is that correct? My knowledge of
VBA is slim, but can usually figure this stuff out pretty quickly.

Thanks!

Mike

Steve Schapel said:
Mike,

The only idea I can think of here, as a general skeleton concept...

Put unbound textboxes on the form for each of the existing "field pairs",
over the top of the existing controls. Set the Control Source property of
these to something like this...
=Nz([OneOfYourFields],[TheOtherField])
... so that whichever one has data will have its data shown on the form.
These controls are, of course, not editable. On the Enter event of these
controls, put code something like this...
If YourToggle = "Currency" Then
Me.TheCorrespondingCurrencyControl.SetFocus
Else
Me.TheCorrespondingTextControl.SetFocus
End If

So, what should happen, is the applicable control, according to your
selection, will "come to the front" while you do your data entry/edit, and
then, whichever of the two data fields you use, the data will be shown in
the calculated control. Hope that makes sense.

--
Steve Schapel, Microsoft Access MVP
I'm trying to provide an option for either text or numeric entry; the
form in question has four columns, the last three of which are almost
always dollar values. Rather than make them all text, I thought of
creating a second set of fields that were text, changing them to not
visible, and placing them right on top of the existing numeric fields.
Then I created a shortcut menu macro with code that, I thought, would
allow the user to toggle between the two fields (the 2nd field is colored
differently, so you know which one you're using). The problem I hadn't
thought of is that it changes *all* the records. I'm looking for a way
to change a control on _one_ record. The other alternative - which I
dislike - is to put the 3 other fields on the form. This would be a data
entry nightmare.

TIA!

Mike
Click to expand...
Click to expand...
 
Mike,

Interesting thoughts here.

I was not clear whether you had tried my suggestion, and if so, what you
don't like about it. I haven't tested it with exactly the scenario you
describe, but at this stage it is still the approach I would use myself.
It would, however, require the user to use an Option Group, or Toggle
Button, or some other way to nominate whether their entry will be the
text or numerical option.

As regards your idea of testing the "type" of data entered, this would
certainly be possible. If the Text type entry would always be like
letters of the alphabet, then testing for that would be relatively easy,
and then, as you suggest, use VBA code to write the entry to the
applicable field. But to me, the disadvantage of this approach is that
there will still be a difficulty to overcome as regards the display of
the data, and access to it for possible editing or deleting. The
unbound control that you use for entry of the data will not be able to
be used for this.
 
I liked the idea, but I had trouble trying to implement it (I wasn't sure
what the "Nz" represented). I also wasn't sure how the users would like
having to use a command button for every entry, since each of the three
fields may, indepently, be either text or currency . The bottom line is
that any solution is going to be a pain; the simplest solution is to simply
use a text data type, while the best solution (and unlikely to happen) is to
develop some guidelines for their data. Ah, the joy of having a project
dropped in your lap on Thursday with a Friday deadline!

I definitely need more VBA & SQL experience! Thanks again for your help.

Mike
 
Mike,

Ah, ok, I didn't realise that the currency/text choice had to be made
for potentially each field separately. I agree that would be a drag.

Nz() is the "Null to zero" function.
=Nz([OneOfYourFields],[TheOtherField])
means "if there is nothing in the [OneOfYourFields] field, then show
what is in the [TheOtherField] field" ... if you catch my drift here,
this would mean that assuming either the text field or the currency
field had some data entered, but not both, then whichever one has an
entry would have its data shown in the textbox.

Well, here's another idea... Instead of the data entry of new records
happening in the detail part of the continuous form, you could not allow
additions in the form itself, and have a separate data entry area, let's
say in the form header, or on a separate pop-up form. This could have
unbound controls, and show two rows of textboxes, one for the currency
options and one for the text options. It would be pretty easy to use
code to validate the data entered. And then, click a button and the
entered data is written to the table, into the appropriate fields, and
thus added to the list of records shown in the detail records of the
form, and something like the Nz() idea as suggested above for data display.
 
Back
Top