Help With Combo Box

  • Thread starter Thread starter T D
  • Start date Start date
T

T D

I have a data entry form attached to and "Payroll" table. On this form I
have a combo box where I can pull down from field 1 the employees name (from
"Employees" table.) I next have a combo box where I can pull down the
employees department code (from "Departments" table.) There are other
fields that are filled out (i.e. Time Started, Time Ended, Date, etc.). All
these results are written to the "Payroll" table.

What I need to be able to do, is when the user enters the Employee Name and
tabs to the Department field, that this field is automatically filled in
with the users default department code (a field in the Employees table). In
other words when the user enters the weekly payroll time most of the time
the employees dept code will be the default dept code assigned to the
employee in the employees table, but sometimes this code need to be changed
if the employee works in another department.

Any suggestions please?

Thanks,
TD
 
TD:

Base cboEmployeeName on a query that includes that Employee's default
DeptCode. You can set the ColumnWidth for the field to 0 so that the user
won't see it if you like *but* make sure that it is included within the
ColumnCount of the combo.

When the user makes a selection, grab the DeptCode from the appropriate
column of the combo (lets say column 2) and place it in cboDeptCode as the
"suggested" Value:

Public Sub cboEmployeeName_AfterUpdate()
' Simplified code is missing a check for Null.
' Null can be returned if there is nothing "selected" in the cbo.
Me.cboDeptCode = Me.cboEmployeeName.Column(2)
End Sub

(I am assuming that the referential integrity you have established prevents
any "value not in list" errors). The user can still change the suggested
value in cboDeptCode with no effect on the original value. In fact, if they
do change cboDeptCode but then decide they want to change it back they can
simply re-select the name in cboEmployeeName and it should repopulate
cboDeptCode with the default value.

Hope this helps,
 
I couldn't resist this response.
http://www.amazon.com/exec/obidos/tg/detail/-/0425176002/qid=1071700137/sr=1
-1/ref=sr_1_1/104-9461135-6713561?v=glance&s=books

Tom

ps
the previous writer was advising to person to use an extra column (2) in the
first combo box. (cboEmployeeName) and the use that column
(.Me.cboEmployeeName.Column(2)) in the example to refer to that in the next
field (Me.cboDeptCode = Me.cboEmployeeName.Column(2)).

You probably need to learn a little visual basic. There is a lot of it used
here
 
George:
I tried what you mentioned but I can't seem to get it to work. Could you
elaborate a little more in detail how to do this please?

TIA
TD
 
Here is a blow-by-blow.
Note: My initial post contained an error. I should have said Column(1) to
refer to the 2nd column of a combobox *NOT* Column(2). The Column property
uses zero-based counting. Mea culpa.

ComboBox1:
Name: cboEmployeeName
RowSourceType: Table/Query
Rowsource: a query that returns a list of EmployeeNames and Dept Codes
"SELECT EmployeeName, DeptCode FROM Employees"
ColumnCount: 2
ColumnWidths: 1,0 (or 1,1 if you want the DeptCode to show. Change 1 as
desired)
BoundColumn: 1

ComboBox2:
Name: cboDeptCode
RowSourceType: Table/Query
Rowsource: a query that returns a list of DeptCodes
"SELECT DeptCode FROM Departments"
ColumnCount: 1
ColumnWidths: 1
BoundColumn: 1

Private Sub cboEmployeeName_AfterUpdate()
Me.cboDeptCode = Me.cboEmployeeName.Column(1)
End Sub
--

Hope this helps,

George Nicholson

Remove 'Junk' from return address.
 
Thank you George. It works great!

T.D.


George Nicholson said:
Here is a blow-by-blow.
Note: My initial post contained an error. I should have said Column(1) to
refer to the 2nd column of a combobox *NOT* Column(2). The Column property
uses zero-based counting. Mea culpa.

ComboBox1:
Name: cboEmployeeName
RowSourceType: Table/Query
Rowsource: a query that returns a list of EmployeeNames and Dept Codes
"SELECT EmployeeName, DeptCode FROM Employees"
ColumnCount: 2
ColumnWidths: 1,0 (or 1,1 if you want the DeptCode to show. Change 1 as
desired)
BoundColumn: 1

ComboBox2:
Name: cboDeptCode
RowSourceType: Table/Query
Rowsource: a query that returns a list of DeptCodes
"SELECT DeptCode FROM Departments"
ColumnCount: 1
ColumnWidths: 1
BoundColumn: 1

Private Sub cboEmployeeName_AfterUpdate()
Me.cboDeptCode = Me.cboEmployeeName.Column(1)
End Sub
--

Hope this helps,

George Nicholson

Remove 'Junk' from return address.


form
Click to expand...
 
Back
Top