Text length in combo box

[Guest]

I have a form that includes one of several standard (and very specific, for
legal reasons) statements. So far we have been selecting the statement from
a combo box, but the latest one is too long to display (over 255 characters).
The combo box source is a Remarks table.
There are a few troublesome characteristics of this database, which is an
abomination I inherited and have not had time to redo. For one thing the
form itself is printed. For another the form involves several tables, but
for some reason it is full of unbound text boxes that are written to the
second table through code when the Print button is clicked. The combo box in
question is one such control.
In brief, the user selects a part number from one form and clicks a button.
Information related to that part number appears on another form (the one
referenced in the preceding paragraph), along with unbound text boxes for
Invoice Number, etc. The Print button's click event writes Invoice Number,
etc. to a second table.
The combo box has two columns, one the PK (0 width) and the other the text
(visible). I think I could solve the problem if I could get the value I need
into a text box, but of course if I specify [ComboBox].Column(1) as the text
box's control source that text is also truncated. I expect I need the text
box to display the record from the Remarks table with a PK to match the combo
box selection, but I can't figure out how to go about that. Or am I on the
wrong track completely?

 

[Ken Snell [MVP]]

Use the DLookup function to get the desired value for the textbox. The
ControlSource expression would be something like this:

=DLookup("FieldName", "Remarks", "[NameOfPKField]=" &
[NameOfComboBoxControl])

 

[Guest]

Thanks for the tip. That did the trick.
I have another situation in the same database where I have an unbound text
box (txtDate) that defaults to today's date, but which the user can change.
The format is mmm/dd/yyyy (not my idea to use that format; it is a government
specification) that I am trying to make all uppercase. I have set the text
box format to mmm/dd/yyyy, but I cannot figure out how to make it uppercase
as well. I realize I could do something like cover txtDate with another
unbound text box txtDate2, and set the Control Source for txtDate2 to
something like:
=UCase(Format([txtDate],"mmm/dd/yyyy"))
If somebody clicks txtDate2, the Click event would be to set the focus to
txtDate and make txtDate2 invisible. After changing the date, the After
Update event for txtDate would be to make txtDate2 visible. It seems rather
roundabout, so I wonder if there is a better way. I would rather not use an
input mask.

Use the DLookup function to get the desired value for the textbox. The
ControlSource expression would be something like this:

=DLookup("FieldName", "Remarks", "[NameOfPKField]=" &
[NameOfComboBoxControl])

--

Ken Snell
<MS ACCESS MVP>

I have a form that includes one of several standard (and very specific, for
legal reasons) statements. So far we have been selecting the statement
from
a combo box, but the latest one is too long to display (over 255
characters).
The combo box source is a Remarks table.
There are a few troublesome characteristics of this database, which is an
abomination I inherited and have not had time to redo. For one thing the
form itself is printed. For another the form involves several tables, but
for some reason it is full of unbound text boxes that are written to the
second table through code when the Print button is clicked. The combo box
in
question is one such control.
In brief, the user selects a part number from one form and clicks a
button.
Information related to that part number appears on another form (the one
referenced in the preceding paragraph), along with unbound text boxes for
Invoice Number, etc. The Print button's click event writes Invoice
Number,
etc. to a second table.
The combo box has two columns, one the PK (0 width) and the other the text
(visible). I think I could solve the problem if I could get the value I
need
into a text box, but of course if I specify [ComboBox].Column(1) as the
text
box's control source that text is also truncated. I expect I need the
text
box to display the record from the Remarks table with a PK to match the
combo
box selection, but I can't figure out how to go about that. Or am I on
the
wrong track completely?

 

[Ken Snell [MVP]]

Interesting question. I did a bit of testing, and I think that this will
work for you. Use the AfterUpdate event of the txtDate control to change the
value to upper case:

Private Sub txtDate_AfterUpdate()
Me.txtDate.Value = UCase(Format(Me.txtDate.Value, "mmm/dd/yyyy"))
End Sub

--

Ken Snell
<MS ACCESS MVP>

Thanks for the tip. That did the trick.
I have another situation in the same database where I have an unbound text
box (txtDate) that defaults to today's date, but which the user can
change.
The format is mmm/dd/yyyy (not my idea to use that format; it is a
government
specification) that I am trying to make all uppercase. I have set the
text
box format to mmm/dd/yyyy, but I cannot figure out how to make it
uppercase
as well. I realize I could do something like cover txtDate with another
unbound text box txtDate2, and set the Control Source for txtDate2 to
something like:
=UCase(Format([txtDate],"mmm/dd/yyyy"))
If somebody clicks txtDate2, the Click event would be to set the focus to
txtDate and make txtDate2 invisible. After changing the date, the After
Update event for txtDate would be to make txtDate2 visible. It seems
rather
roundabout, so I wonder if there is a better way. I would rather not use
an
input mask.

Use the DLookup function to get the desired value for the textbox. The
ControlSource expression would be something like this:

=DLookup("FieldName", "Remarks", "[NameOfPKField]=" &
[NameOfComboBoxControl])

--

Ken Snell
<MS ACCESS MVP>

I have a form that includes one of several standard (and very specific,
for
legal reasons) statements. So far we have been selecting the statement
from
a combo box, but the latest one is too long to display (over 255
characters).
The combo box source is a Remarks table.
There are a few troublesome characteristics of this database, which is
an
abomination I inherited and have not had time to redo. For one thing
the
form itself is printed. For another the form involves several tables,
but
for some reason it is full of unbound text boxes that are written to
the
second table through code when the Print button is clicked. The combo
box
in
question is one such control.
In brief, the user selects a part number from one form and clicks a
button.
Information related to that part number appears on another form (the
one
referenced in the preceding paragraph), along with unbound text boxes
for
Invoice Number, etc. The Print button's click event writes Invoice
Number,
etc. to a second table.
The combo box has two columns, one the PK (0 width) and the other the
text
(visible). I think I could solve the problem if I could get the value
I
need
into a text box, but of course if I specify [ComboBox].Column(1) as the
text
box's control source that text is also truncated. I expect I need the
text
box to display the record from the Remarks table with a PK to match the
combo
box selection, but I can't figure out how to go about that. Or am I on
the
wrong track completely?

 

[Guest]

Ken,

Thanks a lot for your replies. I got distracted by another project and
forgot to check back. Sorry about the delay acknowledging your help. The
UCase thing did the trick, although I added it to the form's On Current event
as well, because if the default date (today) is OK the text box will not be
updated.

Interesting question. I did a bit of testing, and I think that this will
work for you. Use the AfterUpdate event of the txtDate control to change the
value to upper case:

Private Sub txtDate_AfterUpdate()
Me.txtDate.Value = UCase(Format(Me.txtDate.Value, "mmm/dd/yyyy"))
End Sub

--

Ken Snell
<MS ACCESS MVP>

Thanks for the tip. That did the trick.
I have another situation in the same database where I have an unbound text
box (txtDate) that defaults to today's date, but which the user can
change.
The format is mmm/dd/yyyy (not my idea to use that format; it is a
government
specification) that I am trying to make all uppercase. I have set the
text
box format to mmm/dd/yyyy, but I cannot figure out how to make it
uppercase
as well. I realize I could do something like cover txtDate with another
unbound text box txtDate2, and set the Control Source for txtDate2 to
something like:
=UCase(Format([txtDate],"mmm/dd/yyyy"))
If somebody clicks txtDate2, the Click event would be to set the focus to
txtDate and make txtDate2 invisible. After changing the date, the After
Update event for txtDate would be to make txtDate2 visible. It seems
rather
roundabout, so I wonder if there is a better way. I would rather not use
an
input mask.

Use the DLookup function to get the desired value for the textbox. The
ControlSource expression would be something like this:

=DLookup("FieldName", "Remarks", "[NameOfPKField]=" &
[NameOfComboBoxControl])

--

Ken Snell
<MS ACCESS MVP>



I have a form that includes one of several standard (and very specific,
for
legal reasons) statements. So far we have been selecting the statement
from
a combo box, but the latest one is too long to display (over 255
characters).
The combo box source is a Remarks table.
There are a few troublesome characteristics of this database, which is
an
abomination I inherited and have not had time to redo. For one thing
the
form itself is printed. For another the form involves several tables,
but
for some reason it is full of unbound text boxes that are written to
the
second table through code when the Print button is clicked. The combo
box
in
question is one such control.
In brief, the user selects a part number from one form and clicks a
button.
Information related to that part number appears on another form (the
one
referenced in the preceding paragraph), along with unbound text boxes
for
Invoice Number, etc. The Print button's click event writes Invoice
Number,
etc. to a second table.
The combo box has two columns, one the PK (0 width) and the other the
text
(visible). I think I could solve the problem if I could get the value
I
need
into a text box, but of course if I specify [ComboBox].Column(1) as the
text
box's control source that text is also truncated. I expect I need the
text
box to display the record from the Remarks table with a PK to match the
combo
box selection, but I can't figure out how to go about that. Or am I on
the
wrong track completely?

 

[Ken Snell [MVP]]

Just change the Default Value expression to this:

=UCase(Format(Date(), "mmm/dd/yyyy"))


--

Ken Snell
<MS ACCESS MVP>

Ken,

Thanks a lot for your replies. I got distracted by another project and
forgot to check back. Sorry about the delay acknowledging your help. The
UCase thing did the trick, although I added it to the form's On Current
event
as well, because if the default date (today) is OK the text box will not
be
updated.

Interesting question. I did a bit of testing, and I think that this will
work for you. Use the AfterUpdate event of the txtDate control to change
the
value to upper case:

Private Sub txtDate_AfterUpdate()
Me.txtDate.Value = UCase(Format(Me.txtDate.Value, "mmm/dd/yyyy"))
End Sub

--

Ken Snell
<MS ACCESS MVP>

Thanks for the tip. That did the trick.
I have another situation in the same database where I have an unbound
text
box (txtDate) that defaults to today's date, but which the user can
change.
The format is mmm/dd/yyyy (not my idea to use that format; it is a
government
specification) that I am trying to make all uppercase. I have set the
text
box format to mmm/dd/yyyy, but I cannot figure out how to make it
uppercase
as well. I realize I could do something like cover txtDate with
another
unbound text box txtDate2, and set the Control Source for txtDate2 to
something like:
=UCase(Format([txtDate],"mmm/dd/yyyy"))
If somebody clicks txtDate2, the Click event would be to set the focus
to
txtDate and make txtDate2 invisible. After changing the date, the
After
Update event for txtDate would be to make txtDate2 visible. It seems
rather
roundabout, so I wonder if there is a better way. I would rather not
use
an
input mask.

:

Use the DLookup function to get the desired value for the textbox. The
ControlSource expression would be something like this:

=DLookup("FieldName", "Remarks", "[NameOfPKField]=" &
[NameOfComboBoxControl])

--

Ken Snell
<MS ACCESS MVP>



I have a form that includes one of several standard (and very
specific,
for
legal reasons) statements. So far we have been selecting the
statement
from
a combo box, but the latest one is too long to display (over 255
characters).
The combo box source is a Remarks table.
There are a few troublesome characteristics of this database, which
is
an
abomination I inherited and have not had time to redo. For one
thing
the
form itself is printed. For another the form involves several
tables,
but
for some reason it is full of unbound text boxes that are written to
the
second table through code when the Print button is clicked. The
combo
box
in
question is one such control.
In brief, the user selects a part number from one form and clicks a
button.
Information related to that part number appears on another form (the
one
referenced in the preceding paragraph), along with unbound text
boxes
for
Invoice Number, etc. The Print button's click event writes Invoice
Number,
etc. to a second table.
The combo box has two columns, one the PK (0 width) and the other
the
text
(visible). I think I could solve the problem if I could get the
value
I
need
into a text box, but of course if I specify [ComboBox].Column(1) as
the
text
box's control source that text is also truncated. I expect I need
the
text
box to display the record from the Remarks table with a PK to match
the
combo
box selection, but I can't figure out how to go about that. Or am I
on
the
wrong track completely?

 

[Guest]

Thanks again. I don't know why I didn't do that, as it seems obvious enough
now.

Just change the Default Value expression to this:

=UCase(Format(Date(), "mmm/dd/yyyy"))


--

Ken Snell
<MS ACCESS MVP>

Ken,

Thanks a lot for your replies. I got distracted by another project and
forgot to check back. Sorry about the delay acknowledging your help. The
UCase thing did the trick, although I added it to the form's On Current
event
as well, because if the default date (today) is OK the text box will not
be
updated.

Interesting question. I did a bit of testing, and I think that this will
work for you. Use the AfterUpdate event of the txtDate control to change
the
value to upper case:

Private Sub txtDate_AfterUpdate()
Me.txtDate.Value = UCase(Format(Me.txtDate.Value, "mmm/dd/yyyy"))
End Sub

--

Ken Snell
<MS ACCESS MVP>


Thanks for the tip. That did the trick.
I have another situation in the same database where I have an unbound
text
box (txtDate) that defaults to today's date, but which the user can
change.
The format is mmm/dd/yyyy (not my idea to use that format; it is a
government
specification) that I am trying to make all uppercase. I have set the
text
box format to mmm/dd/yyyy, but I cannot figure out how to make it
uppercase
as well. I realize I could do something like cover txtDate with
another
unbound text box txtDate2, and set the Control Source for txtDate2 to
something like:
=UCase(Format([txtDate],"mmm/dd/yyyy"))
If somebody clicks txtDate2, the Click event would be to set the focus
to
txtDate and make txtDate2 invisible. After changing the date, the
After
Update event for txtDate would be to make txtDate2 visible. It seems
rather
roundabout, so I wonder if there is a better way. I would rather not
use
an
input mask.

:

Use the DLookup function to get the desired value for the textbox. The
ControlSource expression would be something like this:

=DLookup("FieldName", "Remarks", "[NameOfPKField]=" &
[NameOfComboBoxControl])

--

Ken Snell
<MS ACCESS MVP>



I have a form that includes one of several standard (and very
specific,
for
legal reasons) statements. So far we have been selecting the
statement
from
a combo box, but the latest one is too long to display (over 255
characters).
The combo box source is a Remarks table.
There are a few troublesome characteristics of this database, which
is
an
abomination I inherited and have not had time to redo. For one
thing
the
form itself is printed. For another the form involves several
tables,
but
for some reason it is full of unbound text boxes that are written to
the
second table through code when the Print button is clicked. The
combo
box
in
question is one such control.
In brief, the user selects a part number from one form and clicks a
button.
Information related to that part number appears on another form (the
one
referenced in the preceding paragraph), along with unbound text
boxes
for
Invoice Number, etc. The Print button's click event writes Invoice
Number,
etc. to a second table.
The combo box has two columns, one the PK (0 width) and the other
the
text
(visible). I think I could solve the problem if I could get the
value
I
need
into a text box, but of course if I specify [ComboBox].Column(1) as
the
text
box's control source that text is also truncated. I expect I need
the
text
box to display the record from the Remarks table with a PK to match
the
combo
box selection, but I can't figure out how to go about that. Or am I
on
the
wrong track completely?