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]

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)

--
Marsh
MVP [MS Access]

[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?

Marshall Barton wrote:
>>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)
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200810/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]

[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.

Marshall Barton wrote:
>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)

--

[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]

JAdams via AccessMonster.com wrote:
>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?
>
>

[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)?

Marshall Barton wrote:
>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]

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).

Marshall Barton wrote:
>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]

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

--

[Marshall Barton]

JAdams via AccessMonster.com wrote:

>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

Marshall Barton wrote:
>>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
>

--