Update an object based on the value of another object

[JAdams]

Hello,
I'm an intermediate user of access but I'm learning a lot through these blogs.

Hope someone can help me.
I have designed a form and I'm trying to have an object automatically updated
based on the value of another object. Eg. I have an object called (Employee
Name) that contains a drop down list of employee names and another object
called (Employee Number). I'm trying to get the employee number object to
auto populate with the (employee number) based on the name selected in the
(Employee Name) object. Both the employee name and number are contained in
the same table as separate fields.

 

[Brian]

EmployeeName_AfterUpdate
If IsNull(EmployeeName) Then
EmployeeNumber = Null
Else
EmployeeNumber =
DLookup("[EmployeeNumber]","[EmployeeTable]","[EmployeeName] = '" &
[EmployeeName] & "'")
End IF
End Sub

This assumes that:

1. This form is not bound just to the Employee table, and
2. You have a way (while in the EmployeeName combo box) to differentiate
betwen two employees having the same name.

Be sure to lock/disable the EmployeeNumber combo box, though, so someone
does not change it after selecting the employee by name, thus creating a
mismatch between EmployeeName & EmployeeNumber.

If this form is used just to maintain the Employee table, then just bind
both controls to the fields of the same name and use a separate combo box
with a Bookmark to lookup & then navigate to the correct record - re-post a
clarification if that is the case, and I can give you an example.

 

[Marshall Barton]

I'm an intermediate user of access but I'm learning a lot through these blogs.

Hope someone can help me.
I have designed a form and I'm trying to have an object automatically updated
based on the value of another object. Eg. I have an object called (Employee
Name) that contains a drop down list of employee names and another object
called (Employee Number). I'm trying to get the employee number object to
auto populate with the (employee number) based on the name selected in the
(Employee Name) object. Both the employee name and number are contained in
the same table as separate fields.

The word "object" is too generic to describe what you are
doing. Please use the specific object type such as combo
box, text box, etc.

The easiest way to get the txtEmployeeNumber text box to
display the EmployeeNumber value that corresponds to the
selection in the cboEmployeeName combo box is to use a query
like:

SELECT EmployeeNumber, EmployeeName
FROM Employees
ORDER BY EmployeeName

as the combo box's row source. Set the combo box's
ColumnCount to 2, BoundColumn to 1 and ColumnWidths to 0;

Then all you need to do is set the text box's control source
expression to =cboEmployeeName.Column(1)

 

[JAdams via AccessMonster.com]

Thanks Marshall.

The current object is a 'combo box'. Now that I want to apply your logic
below, do I need to change the object type?

I'm an intermediate user of access but I'm learning a lot through these blogs.

[quoted text clipped - 6 lines]

(Employee Name) object. Both the employee name and number are contained in
the same table as separate fields.

The word "object" is too generic to describe what you are
doing. Please use the specific object type such as combo
box, text box, etc.

The easiest way to get the txtEmployeeNumber text box to
display the EmployeeNumber value that corresponds to the
selection in the cboEmployeeName combo box is to use a query
like:

SELECT EmployeeNumber, EmployeeName
FROM Employees
ORDER BY EmployeeName

as the combo box's row source. Set the combo box's
ColumnCount to 2, BoundColumn to 1 and ColumnWidths to 0;

Then all you need to do is set the text box's control source
expression to =cboEmployeeName.Column(1)

 

[Marshall Barton]

The word "Control" is better than the word "object". I do
not understand what you mean when you say "current object".

You have a combo box control with a row source query as I
described before. You also need a text box control with the
expression =cboEmployeeName.Column(0)
[I mistakenly used 1 instead of 0 before.]
--
Marsh
MVP [MS Access]

The current object is a 'combo box'. Now that I want to apply your logic
below, do I need to change the object type?

I'm an intermediate user of access but I'm learning a lot through these blogs.

[quoted text clipped - 6 lines]

(Employee Name) object. Both the employee name and number are contained in
the same table as separate fields.

The word "object" is too generic to describe what you are
doing. Please use the specific object type such as combo
box, text box, etc.

The easiest way to get the txtEmployeeNumber text box to
display the EmployeeNumber value that corresponds to the
selection in the cboEmployeeName combo box is to use a query
like:

SELECT EmployeeNumber, EmployeeName
FROM Employees
ORDER BY EmployeeName

as the combo box's row source. Set the combo box's
ColumnCount to 2, BoundColumn to 1 and ColumnWidths to 0;

Then all you need to do is set the text box's control source
expression to =cboEmployeeName.Column(1)

 

[JAdams via AccessMonster.com]

Hi Marshall,
Just to clarify, I have the following 2 combo box controls in my form
(EmployeeName & EmployeeNumber).
So are you saying I need to add a 3rd control identified as a 'text' type and
add the following expression to that new control "=cboEmployeeName.Column(0)"?


Also, the query you mentioned below gets added to the row source of the
(EmployeeNumber) control?

Thanks for being patience with me, but I think where almost there.

The word "Control" is better than the word "object". I do
not understand what you mean when you say "current object".

You have a combo box control with a row source query as I
described before. You also need a text box control with the
expression =cboEmployeeName.Column(0)
[I mistakenly used 1 instead of 0 before.]

The current object is a 'combo box'. Now that I want to apply your logic
below, do I need to change the object type?

[quoted text clipped - 23 lines]

 

[Marshall Barton]

You have not explained why EmployeeNumber is a combo box and
I do not see a reason why it should not be a text box.

Try changing the EmployeeNumber combo box to a text box with
the control source expression I posted.

Set the EmployeeName combo box's RowSource to the query I
posted.

Post back with details about exactly what you did to both
controls and what about the result does not meet your
requirements.
--
Marsh
MVP [MS Access]

Just to clarify, I have the following 2 combo box controls in my form
(EmployeeName & EmployeeNumber).
So are you saying I need to add a 3rd control identified as a 'text' type and
add the following expression to that new control "=cboEmployeeName.Column(0)"?

Also, the query you mentioned below gets added to the row source of the
(EmployeeNumber) control?

The word "Control" is better than the word "object". I do
not understand what you mean when you say "current object".

You have a combo box control with a row source query as I
described before. You also need a text box control with the
expression =cboEmployeeName.Column(0)
[I mistakenly used 1 instead of 0 before.]

The current object is a 'combo box'. Now that I want to apply your logic
below, do I need to change the object type?

[quoted text clipped - 23 lines]

Then all you need to do is set the text box's control source
expression to =cboEmployeeName.Column(1)

 

[JAdams via AccessMonster.com]

Marshall,
Please disregard my previous post. I was able to get the text box control to
return the employee number based on the value in the cboEmployeeName control.
Only 2 control where needed (1 combo and 1 text) not 3.

ALMOST THERE!

So now that I have the value in the text box control, how can I store that
value in the database (Employee Request.EmployeeNumber)?

The word "Control" is better than the word "object". I do
not understand what you mean when you say "current object".

You have a combo box control with a row source query as I
described before. You also need a text box control with the
expression =cboEmployeeName.Column(0)
[I mistakenly used 1 instead of 0 before.]

The current object is a 'combo box'. Now that I want to apply your logic
below, do I need to change the object type?

[quoted text clipped - 23 lines]

 

[JAdams via AccessMonster.com]

I have changed the EmployeeNumber combo box to a text box and added the
expression (=EmployeeName.Column(0) ) without the outside brackets to the
Control Source. I also added the query you posted to the EmployeeName combo
box's Row Source.... WORKS GREAT!

But, now I need to store the value in the EmployeeNumber control to a field
called EmpNo in the Employee Request table (EmpNO.Employee Request)

fyi:
My form is using 2 different tables (tblEmployee) is used to get the Employee
Number and (Employee Request) is where I also need to store the employee
number (EmpNo).

You have not explained why EmployeeNumber is a combo box and
I do not see a reason why it should not be a text box.

Try changing the EmployeeNumber combo box to a text box with
the control source expression I posted.

Set the EmployeeName combo box's RowSource to the query I
posted.

Post back with details about exactly what you did to both
controls and what about the result does not meet your
requirements.

Just to clarify, I have the following 2 combo box controls in my form
(EmployeeName & EmployeeNumber).

[quoted text clipped - 16 lines]

 

[Marshall Barton]

I have changed the EmployeeNumber combo box to a text box and added the
expression (=EmployeeName.Column(0) ) without the outside brackets to the
Control Source. I also added the query you posted to the EmployeeName combo
box's Row Source.... WORKS GREAT!

But, now I need to store the value in the EmployeeNumber control to a field
called EmpNo in the Employee Request table (EmpNO.Employee Request)

fyi:
My form is using 2 different tables (tblEmployee) is used to get the Employee
Number and (Employee Request) is where I also need to store the employee
number (EmpNo).

Alright! Progress at last ;-)

OTOH, you have omitted a critical piece of information - the
form's record source. If the form's record source is
Employee Request, then all you need to fo is set the combo
box's ControlSource property to:
EmpNo

 

[JAdams via AccessMonster.com]

That was it!!!! Working like a charm now.
Thanks Marshall for all your help.

james

I have changed the EmployeeNumber combo box to a text box and added the
expression (=EmployeeName.Column(0) ) without the outside brackets to the

[quoted text clipped - 8 lines]

Number and (Employee Request) is where I also need to store the employee
number (EmpNo).

Alright! Progress at last ;-)

OTOH, you have omitted a critical piece of information - the
form's record source. If the form's record source is
Employee Request, then all you need to fo is set the combo
box's ControlSource property to:
EmpNo