Combo Box Help Please!

[Dagoon]

I was wondering if anyone from the group could please offer me some
help with a database I have developed with MS Access 2002.

Here is the deal

Within this database I have a form named frmPreStart that I use to add
data to a table named tblPreStart. On frmPreStart I have a combo box
named cbo1 that looks up data in another table named tblEmployee.
Table tblEmployee contains the following fields:

1) RefNum (key)- AutoNumber
2) Name- Text
3) Position- Text
4) YTDRPB- Number

For cbo1 I am using the name field as the bound column (2). The Row
Source for cbo1 is as follows:

SELECT tblEmployee.RefNum, tblEmployee.Name, tblEmployee.YTDRPB FROM
tblEmployee ORDER BY tblEmployee.Name;

Now here is where I need help. After I lookup the name field with
cbo1, on frmPreStart, I would like to take the data in the YTDRPB
column from cbo1 and put it in a separate text box. How do I go about
putting just the YTDRPB data in a separate textbox?

Can someone please point me in the right direction? Any help is
greatly appreciated!

 

[Bruce]

I'm a little puzzled by some of what you want to do. For
one thing, I can't see why you want the employee name to
be the bound column. I would think that the key column
should be the bound column. In the combo box properties
set the column count to 2, make the bound column 1, set
the width of the bound (first) column to 0' and the width
of the second column to 1.5" (or whatever) in this format:
0";1.5". Having said that, Employee name should probably
be stored in a table as at least a First Name field and a
Last Name field. Basing a query on the table will let you
combine the names. In query design view, click at the top
of an empty column and type: FullName: [First]&" "&
[Last]. Include the key column from the table in the
query, then use the combo box wizard to create a combo box
to select a record based on a value you select. Accept
the recommendation to hide the key column, and you should
be set. You can make a form from the Employee query (use
the autoform button on the toolbar to get you started),
then make that form into a subform.
I think you might be asking for trouble, though. If
YTDRPB is year to date something or other, it almost
certainly should not be in the employee table. In
general, an employee table should contain only data that
is known at the time of hire, or that changes in a way
that will be part of the employee's record thereafter,
such as changes to name, title, or position. You should
probably be adding records, not modifying existing ones.

 

[Roxie Aho]

I will try to answer your specific question but you should
heed Bruce's advice. It also appears that you are trying
to store the same information in different tables which
will lead you into trouble sooner or later. Ask the group
about design.

Enough preaching. You question was "How do I go about
putting just the YTDRPB data in a separate textbox?"

In the After Update event of the combo box, use this code:
Me.NameOfTextBox = Me.cbo1.Column(2)

YTDRPB is the third selection. Columns are counted 0, 1,
2, 3 etc.

Roxie Aho
roxiea at usinternet.com

-----Original Message-----
I'm a little puzzled by some of what you want to do. For
one thing, I can't see why you want the employee name to
be the bound column. I would think that the key column
should be the bound column. In the combo box properties
set the column count to 2, make the bound column 1, set
the width of the bound (first) column to 0' and the width
of the second column to 1.5" (or whatever) in this format:
0";1.5". Having said that, Employee name should probably
be stored in a table as at least a First Name field and a
Last Name field. Basing a query on the table will let you
combine the names. In query design view, click at the top
of an empty column and type: FullName: [First]&" "&
[Last]. Include the key column from the table in the
query, then use the combo box wizard to create a combo box
to select a record based on a value you select. Accept
the recommendation to hide the key column, and you should
be set. You can make a form from the Employee query (use
the autoform button on the toolbar to get you started),
then make that form into a subform.
I think you might be asking for trouble, though. If
YTDRPB is year to date something or other, it almost
certainly should not be in the employee table. In
general, an employee table should contain only data that
is known at the time of hire, or that changes in a way
that will be part of the employee's record thereafter,
such as changes to name, title, or position. You should
probably be adding records, not modifying existing ones.

-----Original Message-----
I was wondering if anyone from the group could please offer me some
help with a database I have developed with MS Access 2002.

Here is the deal

Within this database I have a form named frmPreStart

that
I use to add

data to a table named tblPreStart. On frmPreStart I have a combo box
named cbo1 that looks up data in another table named tblEmployee.
Table tblEmployee contains the following fields:

1) RefNum (key)- AutoNumber
2) Name- Text
3) Position- Text
4) YTDRPB- Number

For cbo1 I am using the name field as the bound column (2). The Row
Source for cbo1 is as follows:

SELECT tblEmployee.RefNum, tblEmployee.Name, tblEmployee.YTDRPB FROM
tblEmployee ORDER BY tblEmployee.Name;

Now here is where I need help. After I lookup the name field with
cbo1, on frmPreStart, I would like to take the data in the YTDRPB
column from cbo1 and put it in a separate text box. How do I go about
putting just the YTDRPB data in a separate textbox?

Can someone please point me in the right direction? Any help is
greatly appreciated!

.

.