Update a Field Based on A Dropdown Selection

[MJ]

We are building a form where we are entering information about a problem with
a variety of systems/applications. Out dropdown to select the
system/application is functioning correctly, but we are not picking up the
"Assigned To" value on selection.

Is it possible to get the "Assigned To" value when the user selects the
System?

The dropdown list has a <Row Source> = QrySysOther:

SELECT tblSystemOther.System
FROM tblSystemOther;

tblSystemOther is a table with two fields <System> and <Responsible> (which
holds the responsible team member's name for that system) and based on the
user's selection of system, Assigned To should get the value of <Responsible>
for that system.

I know that this is probably very simple, but I am having a brain lockup,
can someone out there give me a quick hand?

Thanks for the assist in advance,

MJ
I

 

[Klatuu]

Is the combo (dropdown) a bound control? If so, to which field is it bound?
If not, do you have a control on your form bound to the AssigedTo field?

If the first question is yes and it is bound to the AssignedTo field, then
set the bound column of the combo to 2.

If the second question is true, use the After Update event of the combo to
asign the value to the field.

Me.txtAssignedTo = Me.MyCombo.Column(1)

Weird thing about combos. Bound columns start with 1, but referencing the
column collection starts with 0.

 

[MJ]

Klatuu...

I started to reply twice already, but keep getting interupted. I tried the
things you suggested without any luck, so I will try to restate and clarify
my situation:

Form
frmOtherIssues > used to collect data on the issue being reported
(Priority; Opened By; Identified By; System;
Assigned To;
Description; and Expected Outcome)
Tables
tblCommon > stores all details of issues reports
tblSystemOther > lookup table containing system information and
responsible analyst

The Record Source = tblCommon for the form (frmOtherIssues).
The System field Row Source = SELECT tblSystemOther.System,
tblSystemOther.Responsible FROM tblSystemOther ORDER BY
tblSystemOther.System;

The combo (dropdown) is bound to tblCommon.System and it works as advertised
for selecting and storing the system value.

The second part of my task is to assign to an analyst based on the results
of the system selection, the analyst value (tblSystemOther.Responsible) is to
be stored tblCommon.AssignedTo . I have put together a query (Qry:
AssignedTo (SysOther)) that works when the system is selected:

SELECT tblSystemOther.Responsible FROM tblSystemOther
WHERE (((tblSystemOther.System)=[Forms]![frmOtherIssues].[System]));

My problem is how to get that value into the AssignedTo field.

Thanks again for your help,
MJ

 

[Klatuu]

What is the name of the second combo?
Is it bound to the Assigned To field?
If not, what is the name of the control bound to the Assigned To field?
--
Dave Hargis, Microsoft Access MVP

Klatuu...

I started to reply twice already, but keep getting interupted. I tried the
things you suggested without any luck, so I will try to restate and clarify
my situation:

Form
frmOtherIssues > used to collect data on the issue being reported
(Priority; Opened By; Identified By; System;
Assigned To;
Description; and Expected Outcome)
Tables
tblCommon > stores all details of issues reports
tblSystemOther > lookup table containing system information and
responsible analyst

The Record Source = tblCommon for the form (frmOtherIssues).
The System field Row Source = SELECT tblSystemOther.System,
tblSystemOther.Responsible FROM tblSystemOther ORDER BY
tblSystemOther.System;

The combo (dropdown) is bound to tblCommon.System and it works as advertised
for selecting and storing the system value.

The second part of my task is to assign to an analyst based on the results
of the system selection, the analyst value (tblSystemOther.Responsible) is to
be stored tblCommon.AssignedTo . I have put together a query (Qry:
AssignedTo (SysOther)) that works when the system is selected:

SELECT tblSystemOther.Responsible FROM tblSystemOther
WHERE (((tblSystemOther.System)=[Forms]![frmOtherIssues].[System]));

My problem is how to get that value into the AssignedTo field.

Thanks again for your help,
MJ

 

[MJ]

There is NO second combo, it is strictly a text field which I want it to pick
up the value of the tblSystemOther.Responsible based on the selection made in
the System dropdown. Is there a simple way to do that?

What is the name of the second combo?
Is it bound to the Assigned To field?
If not, what is the name of the control bound to the Assigned To field?
--
Dave Hargis, Microsoft Access MVP

Klatuu...

I started to reply twice already, but keep getting interupted. I tried the
things you suggested without any luck, so I will try to restate and clarify
my situation:

Form
frmOtherIssues > used to collect data on the issue being reported
(Priority; Opened By; Identified By; System;
Assigned To;
Description; and Expected Outcome)
Tables
tblCommon > stores all details of issues reports
tblSystemOther > lookup table containing system information and
responsible analyst

The Record Source = tblCommon for the form (frmOtherIssues).
The System field Row Source = SELECT tblSystemOther.System,
tblSystemOther.Responsible FROM tblSystemOther ORDER BY
tblSystemOther.System;

The combo (dropdown) is bound to tblCommon.System and it works as advertised
for selecting and storing the system value.

The second part of my task is to assign to an analyst based on the results
of the system selection, the analyst value (tblSystemOther.Responsible) is to
be stored tblCommon.AssignedTo . I have put together a query (Qry:
AssignedTo (SysOther)) that works when the system is selected:

SELECT tblSystemOther.Responsible FROM tblSystemOther
WHERE (((tblSystemOther.System)=[Forms]![frmOtherIssues].[System]));

My problem is how to get that value into the AssignedTo field.

Thanks again for your help,
MJ

Is the combo (dropdown) a bound control? If so, to which field is it bound?
If not, do you have a control on your form bound to the AssigedTo field?

If the first question is yes and it is bound to the AssignedTo field, then
set the bound column of the combo to 2.

If the second question is true, use the After Update event of the combo to
asign the value to the field.

Me.txtAssignedTo = Me.MyCombo.Column(1)

Weird thing about combos. Bound columns start with 1, but referencing the
column collection starts with 0.
--
Dave Hargis, Microsoft Access MVP


:

We are building a form where we are entering information about a problem
with a variety of systems/applications. Out dropdown to select the
system/application is functioning correctly, but we are not picking up the
"Assigned To" value on selection.

Is it possible to get the "Assigned To" value when the user selects the
System?

The dropdown list has a <Row Source> = QrySysOther:

SELECT tblSystemOther.System
FROM tblSystemOther;

tblSystemOther is a table with two fields <System> and <Responsible> (which
holds the responsible team member's name for that system) and based on the
user's selection of system, Assigned To should get the value of <Responsible>
for that system.

I know that this is probably very simple, but I am having a brain lockup,
can someone out there give me a quick hand?

Thanks for the assist in advance,

MJ