Making one field on a form dependent on another fields results?

Hi Sprinks,

My question is the same, but I'm having a little trouble with the coding for
the ControlSource. I have a combo box made for "Fac Loc", which takes the
values from a table. I'm trying to "limit the choices of a downstream combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is the
primary key in the table and it was automatically included when I did the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc

....But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.

If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another field's results
are?

If that's an actual cut-and-paste, the semi-colon after [L Codes] needs to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ETC" wrote in message
...
Hi Sprinks,

My question is the same, but I'm having a little trouble with the coding
for
the ControlSource. I have a combo box made for "Fac Loc", which takes the
values from a table. I'm trying to "limit the choices of a downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is
the
primary key in the table and it was automatically included when I did the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc

...But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the quantity
and
unit prices, set the control's ControlSource to a valid expression, such
as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which case
data
entered in the control is stored in the field OR a calculation, but not
both
simultaneously.

If you wish to limit the choices of a downstream combo box by a prior
field,
you change the former's Row Source as appropriate. For example, once
having
chosen Region, you might want to limit the SalesRep field to those in
the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and controls
that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another field's
results
are?

Thanks, Douglas, but it's still not working for me. Here's what I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac
Loc]=Me!cboFacLoc

The name of the field in the L Codes table is "Fac Loc", but the name of the
combo box is "cboFacLoc". What did I do wrong?

Thanks again for your time.

ETC

"Douglas J Steele" wrote:

If that's an actual cut-and-paste, the semi-colon after [L Codes] needs to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ETC" wrote in message
...
Hi Sprinks,

My question is the same, but I'm having a little trouble with the coding
for
the ControlSource. I have a combo box made for "Fac Loc", which takes the
values from a table. I'm trying to "limit the choices of a downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is
the
primary key in the table and it was automatically included when I did the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc

...But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the quantity
and
unit prices, set the control's ControlSource to a valid expression, such
as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which case
data
entered in the control is stored in the field OR a calculation, but not
both
simultaneously.

If you wish to limit the choices of a downstream combo box by a prior
field,
you change the former's Row Source as appropriate. For example, once
having
chosen Region, you might want to limit the SalesRep field to those in
the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and controls
that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another field's
results
are?

What it's specifically doing is it keeps asking for the Parameter Value for
Me!cboFacLoc when I exit design view. Hope that helps you to help me. =)

"Douglas J Steele" wrote:

If that's an actual cut-and-paste, the semi-colon after [L Codes] needs to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ETC" wrote in message
...
Hi Sprinks,

My question is the same, but I'm having a little trouble with the coding
for
the ControlSource. I have a combo box made for "Fac Loc", which takes the
values from a table. I'm trying to "limit the choices of a downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is
the
primary key in the table and it was automatically included when I did the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc

...But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the quantity
and
unit prices, set the control's ControlSource to a valid expression, such
as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which case
data
entered in the control is stored in the field OR a calculation, but not
both
simultaneously.

If you wish to limit the choices of a downstream combo box by a prior
field,
you change the former's Row Source as appropriate. For example, once
having
chosen Region, you might want to limit the SalesRep field to those in
the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and controls
that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another field's
results
are?

Are you putting that as the Row Source for the combobox, or are you creating
the Row Source in code (the AfterUpdate event for the combobox)?

If the latter, post your exact code.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
What it's specifically doing is it keeps asking for the Parameter Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help me. =)

"Douglas J Steele" wrote:

If that's an actual cut-and-paste, the semi-colon after [L Codes] needs
to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ETC" wrote in message
...
Hi Sprinks,

My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which takes
the
values from a table. I'm trying to "limit the choices of a downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is
the
primary key in the table and it was automatically included when I did
the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc

...But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid expression,
such
as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in your
table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which case
data
entered in the control is stored in the field OR a calculation, but
not
both
simultaneously.

If you wish to limit the choices of a downstream combo box by a prior
field,
you change the former's Row Source as appropriate. For example, once
having
chosen Region, you might want to limit the SalesRep field to those in
the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another
field's
results
are?

I put it in the Row Source of cboX_ID...

SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L Codes]
WHERE [Fac Loc]=Me!cboFacLoc;

Would it be easier to put it in the After Update portion of cboFacLoc?



"Douglas J. Steele" wrote:

Are you putting that as the Row Source for the combobox, or are you creating
the Row Source in code (the AfterUpdate event for the combobox)?

If the latter, post your exact code.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
What it's specifically doing is it keeps asking for the Parameter Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help me. =)

"Douglas J Steele" wrote:

If that's an actual cut-and-paste, the semi-colon after [L Codes] needs
to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ETC" wrote in message
...
Hi Sprinks,

My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which takes
the
values from a table. I'm trying to "limit the choices of a downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is
the
primary key in the table and it was automatically included when I did
the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc

...But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid expression,
such
as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in your
table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which case
data
entered in the control is stored in the field OR a calculation, but
not
both
simultaneously.

If you wish to limit the choices of a downstream combo box by a prior
field,
you change the former's Row Source as appropriate. For example, once
having
chosen Region, you might want to limit the SalesRep field to those in
the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another
field's
results
are?

Yeah, I'd use something like this

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc

Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery

End Sub

This assumes that Fac Loc is a numeric field. If it's a text field, you'll
need


strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Chr$(34) & Me!cboFacLoc & Chr$(34)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
I put it in the Row Source of cboX_ID...

SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L Codes]
WHERE [Fac Loc]=Me!cboFacLoc;

Would it be easier to put it in the After Update portion of cboFacLoc?



"Douglas J. Steele" wrote:

Are you putting that as the Row Source for the combobox, or are you
creating
the Row Source in code (the AfterUpdate event for the combobox)?

If the latter, post your exact code.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
What it's specifically doing is it keeps asking for the Parameter Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help me.
=)

"Douglas J Steele" wrote:

If that's an actual cut-and-paste, the semi-colon after [L Codes]
needs
to
be removed. As well, your WHERE clause is incorrect. Replace the
quotes
around the field name with square brackets.

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ETC" wrote in message
...
Hi Sprinks,

My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which
takes
the
values from a table. I'm trying to "limit the choices of a
downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No"
is
the
primary key in the table and it was automatically included when I
did
the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac
Loc" =
Me!cboFacLoc

...But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid
expression,
such
as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in your
table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which
case
data
entered in the control is stored in the field OR a calculation,
but
not
both
simultaneously.

If you wish to limit the choices of a downstream combo box by a
prior
field,
you change the former's Row Source as appropriate. For example,
once
having
chosen Region, you might want to limit the SalesRep field to those
in
the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the
form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another
field's
results
are?

Argh! SO close! Here's what I got...

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc

Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery

End Sub

By the way, I used a value list for the data in this combo box instead of
extracting it from the table. I don't know if that might be posing a problem
with this. I did it that way because I couldn't figure out how to prevent
multiple Fac Locs from being listed. The "DISTINCT" and "DISTINCT ROW"
wouldn't work for me. Also, for the cboX_ID combo box I also used a value
list instead of extracting it from the table.

Here's what's happening...

When I first open the form, the X_ID combo box displays all possible values
from the value list I gave (of course). But what I really need is to list
only the X_ID values associated with the Fac Loc selected in cboFacLoc, which
comes earlier in the form. So, the cboFacLoc displays all appropriate Fac
Locs. When I select one, the values in the cboX_ID displays ONLY "[L
Codes].X_ID".

What do you think?

Thanks again for all of the time you've spent helping me.

ETC


"Douglas J. Steele" wrote:

Yeah, I'd use something like this

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc

Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery

End Sub

This assumes that Fac Loc is a numeric field. If it's a text field, you'll
need


strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Chr$(34) & Me!cboFacLoc & Chr$(34)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
I put it in the Row Source of cboX_ID...

SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L Codes]
WHERE [Fac Loc]=Me!cboFacLoc;

Would it be easier to put it in the After Update portion of cboFacLoc?



"Douglas J. Steele" wrote:

Are you putting that as the Row Source for the combobox, or are you
creating
the Row Source in code (the AfterUpdate event for the combobox)?

If the latter, post your exact code.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
What it's specifically doing is it keeps asking for the Parameter Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help me.
=)

"Douglas J Steele" wrote:

If that's an actual cut-and-paste, the semi-colon after [L Codes]
needs
to
be removed. As well, your WHERE clause is incorrect. Replace the
quotes
around the field name with square brackets.

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ETC" wrote in message
...
Hi Sprinks,

My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which
takes
the
values from a table. I'm trying to "limit the choices of a
downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No"
is
the
primary key in the table and it was automatically included when I
did
the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac
Loc" =
Me!cboFacLoc

...But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid
expression,
such
as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in your
table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which
case
data
entered in the control is stored in the field OR a calculation,
but
not
both
simultaneously.

If you wish to limit the choices of a downstream combo box by a
prior
field,
you change the former's Row Source as appropriate. For example,
once
having
chosen Region, you might want to limit the SalesRep field to those
in
the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the
form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another
field's
results
are?

I'm not sure I understand what you're saying.

What do you mean by "SO close"? Are you encountering some sort of error with
that?

When you say that when you select a record, the values in the cboX_ID
displays ONLY "[L Codes].X_ID", that's normal: while a combobox may display
multiple columns when it's dropped down, only one value will show when it's
not.

SELECT DISCTINCT will give you unique combinations of No, X_ID and Fac Loc.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
Argh! SO close! Here's what I got...

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc

Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery

End Sub

By the way, I used a value list for the data in this combo box instead of
extracting it from the table. I don't know if that might be posing a
problem
with this. I did it that way because I couldn't figure out how to prevent
multiple Fac Locs from being listed. The "DISTINCT" and "DISTINCT ROW"
wouldn't work for me. Also, for the cboX_ID combo box I also used a value
list instead of extracting it from the table.

Here's what's happening...

When I first open the form, the X_ID combo box displays all possible
values
from the value list I gave (of course). But what I really need is to list
only the X_ID values associated with the Fac Loc selected in cboFacLoc,
which
comes earlier in the form. So, the cboFacLoc displays all appropriate Fac
Locs. When I select one, the values in the cboX_ID displays ONLY "[L
Codes].X_ID".

What do you think?

Thanks again for all of the time you've spent helping me.

ETC


"Douglas J. Steele" wrote:

Yeah, I'd use something like this

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc

Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery

End Sub

This assumes that Fac Loc is a numeric field. If it's a text field,
you'll
need


strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Chr$(34) & Me!cboFacLoc & Chr$(34)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
I put it in the Row Source of cboX_ID...

SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L
Codes]
WHERE [Fac Loc]=Me!cboFacLoc;

Would it be easier to put it in the After Update portion of cboFacLoc?



"Douglas J. Steele" wrote:

Are you putting that as the Row Source for the combobox, or are you
creating
the Row Source in code (the AfterUpdate event for the combobox)?

If the latter, post your exact code.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
What it's specifically doing is it keeps asking for the Parameter
Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help
me.
=)

"Douglas J Steele" wrote:

If that's an actual cut-and-paste, the semi-colon after [L Codes]
needs
to
be removed. As well, your WHERE clause is incorrect. Replace the
quotes
around the field name with square brackets.

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac
Loc] =
Me!cboFacLoc

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ETC" wrote in message
...
Hi Sprinks,

My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which
takes
the
values from a table. I'm trying to "limit the choices of a
downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L
Codes.No"
is
the
primary key in the table and it was automatically included when I
did
the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac
Loc" =
Me!cboFacLoc

...But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display
a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid
expression,
such
as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in
your
table,
since it can be calculated on-the-fly in a query. To do so
also
requires VBA
code, since a ControlSource can either be a fieldname, in which
case
data
entered in the control is stored in the field OR a calculation,
but
not
both
simultaneously.

If you wish to limit the choices of a downstream combo box by a
prior
field,
you change the former's Row Source as appropriate. For
example,
once
having
chosen Region, you might want to limit the SalesRep field to
those
in
the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the
form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another
field's
results
are?

Unfortunately, yes, I'm still encountering an error with that. What I meant
was that when I select an item from cboFacLoc, it currently displays "[L
Codes].X_ID" in cboX_ID rather than displaying the actually IDs that are
associated with what's selected in cboFac Loc. Here's what the table looks
like...

Fac Loc X_ID
A P
A P
A Q
A R
B S
B T
B T
B U

So, I made a combo box for each field (cboFacLoc and cboX_ID). So, if they
chose "A" from cboFacLoc then I only want "P", "Q" and "R" displayed in
cboX_ID.

Does that help?

"Douglas J. Steele" wrote:

I'm not sure I understand what you're saying.

What do you mean by "SO close"? Are you encountering some sort of error with
that?

When you say that when you select a record, the values in the cboX_ID
displays ONLY "[L Codes].X_ID", that's normal: while a combobox may display
multiple columns when it's dropped down, only one value will show when it's
not.

SELECT DISCTINCT will give you unique combinations of No, X_ID and Fac Loc.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
Argh! SO close! Here's what I got...

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc

Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery

End Sub

By the way, I used a value list for the data in this combo box instead of
extracting it from the table. I don't know if that might be posing a
problem
with this. I did it that way because I couldn't figure out how to prevent
multiple Fac Locs from being listed. The "DISTINCT" and "DISTINCT ROW"
wouldn't work for me. Also, for the cboX_ID combo box I also used a value
list instead of extracting it from the table.

Here's what's happening...

When I first open the form, the X_ID combo box displays all possible
values
from the value list I gave (of course). But what I really need is to list
only the X_ID values associated with the Fac Loc selected in cboFacLoc,
which
comes earlier in the form. So, the cboFacLoc displays all appropriate Fac
Locs. When I select one, the values in the cboX_ID displays ONLY "[L
Codes].X_ID".

What do you think?

Thanks again for all of the time you've spent helping me.

ETC


"Douglas J. Steele" wrote:

Yeah, I'd use something like this

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc

Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery

End Sub

This assumes that Fac Loc is a numeric field. If it's a text field,
you'll
need


strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Chr$(34) & Me!cboFacLoc & Chr$(34)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
I put it in the Row Source of cboX_ID...

SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L
Codes]
WHERE [Fac Loc]=Me!cboFacLoc;

Would it be easier to put it in the After Update portion of cboFacLoc?



"Douglas J. Steele" wrote:

Are you putting that as the Row Source for the combobox, or are you
creating
the Row Source in code (the AfterUpdate event for the combobox)?

If the latter, post your exact code.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ETC" wrote in message
...
What it's specifically doing is it keeps asking for the Parameter
Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help
me.
=)

"Douglas J Steele" wrote:

If that's an actual cut-and-paste, the semi-colon after [L Codes]
needs
to
be removed. As well, your WHERE clause is incorrect. Replace the
quotes
around the field name with square brackets.

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac
Loc] =
Me!cboFacLoc

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ETC" wrote in message
...
Hi Sprinks,

My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which
takes
the
values from a table. I'm trying to "limit the choices of a
downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L
Codes.No"
is
the
primary key in the table and it was automatically included when I
did
the
combo box wizard.)

In the Control Source of the "X_ID" combo box I have...

SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac
Loc" =
Me!cboFacLoc

...But it's not working for me. Can you help?

ETC



"Sprinks" wrote:

It depends on what you're trying to do. If you want to display
a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid
expression,
such
as:

=[Qty] * [UnitPrice]

99.999% of the time, there is no need to store this value in
your
table,
since it can be calculated on-the-fly in a query. To do so
also
requires VBA
code, since a ControlSource can either be a fieldname, in which
case
data
entered in the control is stored in the field OR a calculation,
but
not
both
simultaneously.

If you wish to limit the choices of a downstream combo box by a
prior
field,
you change the former's Row Source as appropriate. For
example,
once
having
chosen Region, you might want to limit the SalesRep field to
those
in
the
region:

Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl

By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the
form's
underlying RecordSource.

Hope that helps.
Sprinks

"dawnykins" wrote:

How do you make one field on a form dependent on what another
field's
results
are?