Bruce,
The 2 letter TmDesigNtr I want to display was added as a field to the
tblHandlingTm. My Combo boxes are Cascading and what the user selects
is what is stored in the tblEmployees. The TmDesigNtr is part of the
tblHandlingTm. This table was created strictly to make the casacading
combo boxes work.
The control source for cboHandlingTeam is HandlingTeam; the Row Source
Is qryMFT (Multi-functional Team). The column count is 1; the column
width is blank. Information from this cboHandlingTeam is stored in
tblEmployees field HandlingTeam.
The Teams are ordered by auto number because I followed the instructions
that were given to me in a forum like this. I could order them by the
Team number, but I hadn't thought of it until you mentioned it.
Quite simply, I thought, the user would see and choose a Handling Team,
ie A401 from the cboHandlingTeam. This information is stored. Then I
want to display the 2 letter identifier (CB) from
tblHandlingTM.TmDesigNtr associated with that team along with the
tblEmployees.ClientID.
Obviously I'm lost and I appreciate your patience.
Mack
You are not limited to concatenating the combo box record source data.
You can refer to other columns thus:
=cboHandlingTm.Column(1)
The column counting is zero-based in this case, so Column(1) is
actually the second column. Column(0) is the first column. If you
leave out Column() you will get the bound column.
It makes good sense to store the value that will not change. It does
not have to be the displayed column. Any values from the combo box row
source can be displayed.
If you need more information, please be clear, remembering that I
cannot see you database. For instance, you refer to "the list", which
I assume is the HandlingTeam row source, but I am not sure. For a
combo box include information about Row Source, Record Source, Bound
Column, Column Count, and Column Widths. BTW, why order by the
autonumber?
TmDesigNtr does not seem to be part of the Row Source for
cboHandlingTeam that you posted earlier. Has the Row Source changed?
Ok, I've recovered somewhat.
The user may choose the HandlingCtr which then in turn allows him to
select the HandlingTeam. These are stored values. The list is
ordered by the ID Team which is an autonumber column. There is
another column in the qry table named "TmDesgNtr" this is the column
that stores the two letter Identifier of the team. The team name may
change but the designator never will. From your reponse I guess I'm
storing the wrong infomation. The user should be able to pick the
Handling Team, ie, "A401" and the TmDesigNtr "CB" should be stored.
Then I should be able to cocantenate the Client ID & TmDesigNtr.
Combo.61 is the Hanling Team Combobox which I have renamed to
cboHandlingTm. If I'm on the right track just Holler!
Mack
The Column Count is the number of columns in a combo box or list box
Row Source query or table. It can also be used with a value list Row
Source, but that doesn't seem to apply here. The Bound Column is the
one Access "sees" when it looks at the combo box. With this Row
Source you have three columns:
SELECT tblHandlingTeam.HandlingTeam, tblHandlingTeam.IDTeam,
tblHandlingTeam.IDHandlingCtr
FROM tblHandlingTeam ...
The combo box needs a Column Count (on the combo box Property Sheet,
Format tab) of 3; otherwise HandlingTeam is all that will be
available in the combo box. If the Bound Column (Data tab) is 1
(first column), Access will "see" Handling Team when it looks at the
combo box unless you specify otherwise. If the combo box is bound to
a field, the bound column is the data that will be stored in the
field.
The Column Widths (Format tab) is the widths of the columns when you
click the combo box drop-down arrow. If you do not specify otherwise
the first column in the Row Source will be the only visible data, and
it will be the width of the combo box. If you have something like
CompanyID, CompanyName as the Row Source, you probably intend for
your users to select the CompanyName, but you want to store the
CompanyID, which will stay the same if the company changes its name.
In that case the Column Count is 2, the Column Widths are 0";1.5" (or
whatever for the second column), and the Bound Column is 1.
I see that you are requerying Combo61. Is that the name of the
HandlingTeam combo box?
I like to give combo boxes, text boxes etc. meaningful names that are
distinct from the field name. If the field is HandlingCtr, I call
the combo box cboHandlingCtr; for HandlingTeam it is cboHandlingTeam.
It may not matter in this case, but in some situations it does, and
in all cases you know exactly to what you are referring in the code.
It also makes it easier when you post a question.
Bruce,
The ClientID is a 4 number random number assigned to each new
record.
The HandlingCtr combo box comes from qryHandlingCtr:
SELECT tblHandlingCtr.HandlingCtr, tblHandlingCtr.IDHandlingCtr
FROM tblHandlingCtr
ORDER BY tblHandlingCtr.IDHandlingCtr;
The After Update event is:
Private Sub HandlingCenter_AfterUpdate()
Me.Combo61.Requery
End Sub
The Row Source for the Handling Team is:
SELECT tblHandlingTeam.HandlingTeam, tblHandlingTeam.IDTeam,
tblHandlingTeam.IDHandlingCtr
FROM tblHandlingTeam
WHERE (((tblHandlingTeam.IDHandlingCtr)=[Forms]![LNA
Record]![HandlingCenter]))
ORDER BY tblHandlingTeam.IDTeam;
I don’t understand the bound column and column count terms.
Mack
:
The code would be helpful. It is by chance that I am still
monitoring this
thread. I usually stop after a couple of weeks at the most. I
have to say
I did not spend much time trying to sort out the previous part of
the
thread.
Here is some needed information:
What information are you storing using the form? You speak about
creating a
ClientID. Does that mean you are creating a new Client record, for
a client
who does not yet exist?
What is the the Row Source for the HandlingCtr combo box? What is
its After
Update event (which is where you would modify the Row Source for
the Team
combo box)? What is the Column Count, and which is the Bound
Column?
What is the Row Source for the Team combo box? What is the Column
Count,
and which is the Bound Column?
Again, you should be able to concatenate the three fields: Client,
HandlingCtr, and Team, but first we need to find where that
information is
stored.
Ok,
I will start from the top:
I have two cascading combo boxes. One is used to select the
HandlingCtr
(MNC-I, MND-B, MND-C, MND-N, MNF-W, or MEDCOM). Depending on
which center
is
selected the second combo box HandlingTm displays a choice of
teams, i.e.
(OPSCEN, A401, A402, etc). My ClientID is generated by pressing
a button
and
then never changes as it is the key index. In the form after the
ClientID
is
generated and the HandlingCtr and Team are selected in the combo
box I
want
to display [ClientID] & [CtrDsgNtr] & [TmDsgNtr] in a text box
for the
current record being looked at. Final product should look like
this:
1234IA.
A table holds information with CtrDsgNtr and TmDsgNtr being
fields. I
will
respond again with exact errors and any code that was written.
Merry
Christmas!
Mack
:
When describing an error or unexpected result you need to be
specific.
You
wrote "I tried the unbound textbox = method but did not work for
me". In
what way did it not work? What exactly did you place in the
Control
Source
for the unbound text box? If the information is as you
described earlier
my
suggestion will work. You can test each element of the Control
Source
expression. If you need to format EmployeeID as four digits:
=Format([EmployeeID],"0000")
Next try to get the letter code for the Center:
=Right(
,1)
Then try getting the letter from the Team value:
=Chr([Team] - 36)
If each expression works by itself, concatenate the expressions
using the
ampersand. If not, describe the result produced by the
expression(s)
that
did not work as expected.
Cascading combo boxes are generally taken to mean something like
this:
There is a combo box for selecting a state or province. After
that
selection is made, the City combo box is requeried to display
only cities
and towns in that state or province. I don't see where you have
this
sort
of situation. If you do, where exactly does it occur?
message
Bruce & John,
Thanks for all your help. The way I came up with the Handling
Ctr/Tm
values
was by choosing one of three ways to make my cascading combo
boxes.
Each
field currently only holds one value, I just need the
displayed number
to
be
a concatenated value. My problem is that what this number is
depends on
what
is chosen in the cascading combo boxes. I can send a copy of
the
database
if
that helps. Sorry if I'm making this difficult.
Mack
:
On Thu, 4 Dec 2008 07:49:01 -0800, MackBlale
Bruce,
While experimenting I simply added another column to my
tables. Now
in
tblHandlingCtr I have the fields IDHandlingCtr, HandlingCtr,
and
CtrDesigNtr.
In tblHandlingTeam I have HandlingTeam, HandlingCtr, and
TmDesigNtr.
The
designator field holds values A-Z for the Teams and B, C, I,
M, and W
for the
Centers. I tried the unbound textbox = method but did not
work for
me.
Also, previously I had the format set to "0000" because I
need the
number to
display as four digits. This is not working with the
cognate. Still
plugging away,
PMFJI but... it looks like the root of your problem is that
you're
trying
(in
at least two places) to store multiple pieces of information
in one
field.
This violates the basic principle that fields should be
"atomic" -
storing
only one chunk of information. It's always easier to
concatenate three
or
four
values together into a string than it is to tease them apart;
for
example
ShowID: [HandlingTeam] & [HandlingCtr] & Format([TmDesigNtr,
"0000")
