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

[Douglas J. Steele]

If you're using SQL, you must have the Row Source Type property set to
Table/Query.

Also, you don't have a field named No in what you displayed below: is there
such a field in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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?

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

(no e-mails, please!)

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


:

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

(no e-mails, please!)



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?



:

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

(no e-mails, please!)



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

:

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

(no e-mails, please!)


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



:

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

:

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

 

[Guest]

Yes, I have a field named No in my table. It's the primary key. If I set
the Row Source Type property set to Table/Query, then I don't know how to
keep objects from repeating on the dropdown list. But here, let me try this
again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1 $5.00
2 A P Z 3 $10.00
3 A Q Y 2 $20.00
4 A Q Z 3 $10.00
5 B R Y 1 $5.00
6 B R Z 3 $10.00
7 B S Y 2 $20.00
8 C S Z 2 $20.00
9 C T Y 3 $10.00
10 C T Z 4 $30.00

So, here's what I've done: On my Form, I deleted both combo boxes and
started all over again. This time I used the wizard to have it take the
values from the existing table "L Codes". At that point all values
(including repeats) are listed. I went into cboFacLoc and in the AfterUpdate
event I have the following Code:

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

After I do this, the FacLoc lists the same data (of course) and the cboPk_ID
list is blank. No data is displayed for selecting.

(I'm sorry this is taking so long! I really appreciate all of your time.
Please don't give up on me!)

ETC

If you're using SQL, you must have the Row Source Type property set to
Table/Query.

Also, you don't have a field named No in what you displayed below: is there
such a field in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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?

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

(no e-mails, please!)



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


:

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

(no e-mails, please!)



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?



:

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

(no e-mails, please!)



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

:

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

(no e-mails, please!)


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



:

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

 

[Douglas J Steele]

You'll never get a list of unique X_ID values if you include No as well. And
since you know what Fac Loc value you're looking at, there's really no need
to include it in the query. The fact that Fac Loc is a text value means you
need to include quotes around the value you're passing:

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

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

End Sub

Exagerated for clarity, strSQL is:

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



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, I have a field named No in my table. It's the primary key. If I set
the Row Source Type property set to Table/Query, then I don't know how to
keep objects from repeating on the dropdown list. But here, let me try this
again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1 $5.00
2 A P Z 3 $10.00
3 A Q Y 2 $20.00
4 A Q Z 3 $10.00
5 B R Y 1 $5.00
6 B R Z 3 $10.00
7 B S Y 2 $20.00
8 C S Z 2 $20.00
9 C T Y 3 $10.00
10 C T Z 4 $30.00

So, here's what I've done: On my Form, I deleted both combo boxes and
started all over again. This time I used the wizard to have it take the
values from the existing table "L Codes". At that point all values
(including repeats) are listed. I went into cboFacLoc and in the AfterUpdate
event I have the following Code:

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

After I do this, the FacLoc lists the same data (of course) and the cboPk_ID
list is blank. No data is displayed for selecting.

(I'm sorry this is taking so long! I really appreciate all of your time.
Please don't give up on me!)

ETC

If you're using SQL, you must have the Row Source Type property set to
Table/Query.

Also, you don't have a field named No in what you displayed below: is there
such a field in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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?

:

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

(no e-mails, please!)



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


:

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

(no e-mails, please!)



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?



:

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

(no e-mails, please!)



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

:

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

(no e-mails, please!)


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



:

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

 

[Guest]

Doug,

I am SO sorry, but it's just not working. The cboPk_ID combo box is still
showing up blank. I think I'll just resort to the uglier,
"more-complicated-for-the-user" way of doing it. But I thank you for your
time.

ETC

You'll never get a list of unique X_ID values if you include No as well. And
since you know what Fac Loc value you're looking at, there's really no need
to include it in the query. The fact that Fac Loc is a text value means you
need to include quotes around the value you're passing:

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

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

End Sub

Exagerated for clarity, strSQL is:

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



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, I have a field named No in my table. It's the primary key. If I set
the Row Source Type property set to Table/Query, then I don't know how to
keep objects from repeating on the dropdown list. But here, let me try this
again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1 $5.00
2 A P Z 3 $10.00
3 A Q Y 2 $20.00
4 A Q Z 3 $10.00
5 B R Y 1 $5.00
6 B R Z 3 $10.00
7 B S Y 2 $20.00
8 C S Z 2 $20.00
9 C T Y 3 $10.00
10 C T Z 4 $30.00

So, here's what I've done: On my Form, I deleted both combo boxes and
started all over again. This time I used the wizard to have it take the
values from the existing table "L Codes". At that point all values
(including repeats) are listed. I went into cboFacLoc and in the AfterUpdate
event I have the following Code:

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

After I do this, the FacLoc lists the same data (of course) and the cboPk_ID
list is blank. No data is displayed for selecting.

(I'm sorry this is taking so long! I really appreciate all of your time.
Please don't give up on me!)

ETC

If you're using SQL, you must have the Row Source Type property set to
Table/Query.

Also, you don't have a field named No in what you displayed below: is there
such a field in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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?

:

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

(no e-mails, please!)



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


:

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

(no e-mails, please!)



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?



:

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

(no e-mails, please!)



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

:

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

 

[Douglas J Steele]

What are the properties for your combobox?

And one test to try. Add a Debug.Print to your AfterUpdate routine, to write
the SQL statement out to the Debug window (Ctrl-G):

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

Debug.Print strSQL

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

End Sub

Copy what's printed out for strSQL in the Debug window and paste it into a
new query. Does the query run properly?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I am SO sorry, but it's just not working. The cboPk_ID combo box is still
showing up blank. I think I'll just resort to the uglier,
"more-complicated-for-the-user" way of doing it. But I thank you for your
time.

ETC

You'll never get a list of unique X_ID values if you include No as well. And
since you know what Fac Loc value you're looking at, there's really no need
to include it in the query. The fact that Fac Loc is a text value means you
need to include quotes around the value you're passing:

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

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

End Sub

Exagerated for clarity, strSQL is:

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



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, I have a field named No in my table. It's the primary key. If I set
the Row Source Type property set to Table/Query, then I don't know how to
keep objects from repeating on the dropdown list. But here, let me

try
this

again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1 $5.00
2 A P Z 3 $10.00
3 A Q Y 2 $20.00
4 A Q Z 3 $10.00
5 B R Y 1 $5.00
6 B R Z 3 $10.00
7 B S Y 2 $20.00
8 C S Z 2 $20.00
9 C T Y 3 $10.00
10 C T Z 4 $30.00

So, here's what I've done: On my Form, I deleted both combo boxes and
started all over again. This time I used the wizard to have it take the
values from the existing table "L Codes". At that point all values
(including repeats) are listed. I went into cboFacLoc and in the AfterUpdate
event I have the following Code:

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

After I do this, the FacLoc lists the same data (of course) and the cboPk_ID
list is blank. No data is displayed for selecting.

(I'm sorry this is taking so long! I really appreciate all of your time.
Please don't give up on me!)

ETC

:

If you're using SQL, you must have the Row Source Type property set to
Table/Query.

Also, you don't have a field named No in what you displayed below:

is
there

such a field in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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?

:

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

(no e-mails, please!)



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


:

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

(no e-mails, please!)



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?



:

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

(no e-mails, please!)



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

:

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

 

[Guest]

Properties...

Name: cboFacLoc
Control Source: Facility Location
Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].[Facility Location]
FROM [Lawson Codes];
Column Count: 2
Colmn Heads: No
Colmn Widths 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit to List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mod: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 0
Left: 0.9583"
Top: 0.125"
Width: 0.7083"
Height: 0.1771"
Back Style: Normal
Back Color: -2147483643
Special Effect: Sunken
Border Style: Solid
Border Color: 0
Border Width: Hairline
Fore Color: -2147483640
Help Context Id: 0
After Update: Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Lawson Codes].Pk_ID " & _
"FROM [Lawson Codes] " & _
"WHERE [Facility Location] = '" & Me!cboFacLoc & "'"

Debug.Print strSQL


Me.cboPk_ID.RowSource = strSQL
Me.cboPk_ID.Requery
End Sub

Reading Order: Context
Keyborad Language: Shystem
Scroll Bar align: System
Numeral Shapes: System
Is Hyperlink: No


The other combo box...
Name: cboPk_ID
Control Source: Pk_ID

Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].Pk_ID FROM [Lawson
Codes];
Column Count: 2
Column Heads: No
Column Widths: 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit To List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mode: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 1
Help Context Id: 0
Reading Order: Context
Keyboard Language: System
Scroll Bar Align: System
Numeral Shapes: System
Is Hyperlink: No

I left out the font and border properties (I didn't think you cared to know
about those). All other fields are blank in these two combo boxes. As you
can see I put in the Debug.Print in the AfterUpdate Event procedure, but I'm
not understanding what it's supposed to do.

What are the properties for your combobox?

And one test to try. Add a Debug.Print to your AfterUpdate routine, to write
the SQL statement out to the Debug window (Ctrl-G):

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

Debug.Print strSQL

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

End Sub

Copy what's printed out for strSQL in the Debug window and paste it into a
new query. Does the query run properly?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I am SO sorry, but it's just not working. The cboPk_ID combo box is still
showing up blank. I think I'll just resort to the uglier,
"more-complicated-for-the-user" way of doing it. But I thank you for your
time.

ETC

You'll never get a list of unique X_ID values if you include No as well. And
since you know what Fac Loc value you're looking at, there's really no need
to include it in the query. The fact that Fac Loc is a text value means you
need to include quotes around the value you're passing:

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

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

End Sub

Exagerated for clarity, strSQL is:

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



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes, I have a field named No in my table. It's the primary key. If I set
the Row Source Type property set to Table/Query, then I don't know how to
keep objects from repeating on the dropdown list. But here, let me try
this
again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1 $5.00
2 A P Z 3 $10.00
3 A Q Y 2 $20.00
4 A Q Z 3 $10.00
5 B R Y 1 $5.00
6 B R Z 3 $10.00
7 B S Y 2 $20.00
8 C S Z 2 $20.00
9 C T Y 3 $10.00
10 C T Z 4 $30.00

So, here's what I've done: On my Form, I deleted both combo boxes and
started all over again. This time I used the wizard to have it take the
values from the existing table "L Codes". At that point all values
(including repeats) are listed. I went into cboFacLoc and in the
AfterUpdate
event I have the following Code:

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

After I do this, the FacLoc lists the same data (of course) and the
cboPk_ID
list is blank. No data is displayed for selecting.

(I'm sorry this is taking so long! I really appreciate all of your time.
Please don't give up on me!)

ETC

:

If you're using SQL, you must have the Row Source Type property set to
Table/Query.

Also, you don't have a field named No in what you displayed below: is
there
such a field in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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?

:

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

(no e-mails, please!)



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


:

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] " & _

 

[Douglas J. Steele]

The details for cboPk_ID are inappropriate for what you set its RowSource to
in cboFacLoc_AfterUpdate: it's saying 2 columns, while your row source is
changed to SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] ...,
which only has one column.

That may well be the issue.

The Debug statement is supposed to print the actual SQL you're trying to use
in the Immediate window. Use Ctrl-G to go into the VB Editor once you've
selected a value in cboFacLoc, and you should see the SQL printed out in the
window in the bottom right.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Properties...

Name: cboFacLoc
Control Source: Facility Location
Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].[Facility
Location]
FROM [Lawson Codes];
Column Count: 2
Colmn Heads: No
Colmn Widths 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit to List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mod: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 0
Left: 0.9583"
Top: 0.125"
Width: 0.7083"
Height: 0.1771"
Back Style: Normal
Back Color: -2147483643
Special Effect: Sunken
Border Style: Solid
Border Color: 0
Border Width: Hairline
Fore Color: -2147483640
Help Context Id: 0
After Update: Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Lawson Codes].Pk_ID " & _
"FROM [Lawson Codes] " & _
"WHERE [Facility Location] = '" & Me!cboFacLoc & "'"

Debug.Print strSQL


Me.cboPk_ID.RowSource = strSQL
Me.cboPk_ID.Requery
End Sub

Reading Order: Context
Keyborad Language: Shystem
Scroll Bar align: System
Numeral Shapes: System
Is Hyperlink: No


The other combo box...
Name: cboPk_ID
Control Source: Pk_ID

Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].Pk_ID FROM [Lawson
Codes];
Column Count: 2
Column Heads: No
Column Widths: 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit To List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mode: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 1
Help Context Id: 0
Reading Order: Context
Keyboard Language: System
Scroll Bar Align: System
Numeral Shapes: System
Is Hyperlink: No

I left out the font and border properties (I didn't think you cared to
know
about those). All other fields are blank in these two combo boxes. As
you
can see I put in the Debug.Print in the AfterUpdate Event procedure, but
I'm
not understanding what it's supposed to do.

What are the properties for your combobox?

And one test to try. Add a Debug.Print to your AfterUpdate routine, to
write
the SQL statement out to the Debug window (Ctrl-G):

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

Debug.Print strSQL

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

End Sub

Copy what's printed out for strSQL in the Debug window and paste it into
a
new query. Does the query run properly?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I am SO sorry, but it's just not working. The cboPk_ID combo box is
still
showing up blank. I think I'll just resort to the uglier,
"more-complicated-for-the-user" way of doing it. But I thank you for
your
time.

ETC


:

You'll never get a list of unique X_ID values if you include No as
well. And
since you know what Fac Loc value you're looking at, there's really
no need
to include it in the query. The fact that Fac Loc is a text value
means you
need to include quotes around the value you're passing:

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

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

End Sub

Exagerated for clarity, strSQL is:

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



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes, I have a field named No in my table. It's the primary key.
If I set
the Row Source Type property set to Table/Query, then I don't know
how to
keep objects from repeating on the dropdown list. But here, let me try
this
again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1
$5.00
2 A P Z 3
$10.00
3 A Q Y 2
$20.00
4 A Q Z 3
$10.00
5 B R Y 1
$5.00
6 B R Z 3
$10.00
7 B S Y 2
$20.00
8 C S Z 2
$20.00
9 C T Y 3
$10.00
10 C T Z 4
$30.00

So, here's what I've done: On my Form, I deleted both combo boxes
and
started all over again. This time I used the wizard to have it
take the
values from the existing table "L Codes". At that point all values
(including repeats) are listed. I went into cboFacLoc and in the
AfterUpdate
event I have the following Code:

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

After I do this, the FacLoc lists the same data (of course) and the
cboPk_ID
list is blank. No data is displayed for selecting.

(I'm sorry this is taking so long! I really appreciate all of your time.
Please don't give up on me!)

ETC

:

If you're using SQL, you must have the Row Source Type property
set to
Table/Query.

Also, you don't have a field named No in what you displayed
below: is
there
such a field in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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?

:

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

(no e-mails, please!)



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


:

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] " & _

 

[Guest]

So, what exactly should I change in the property for which combo box?

Also, when I hit Ctrl+G I get the following...

SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] WHERE [Facility
Location] = '4'

The details for cboPk_ID are inappropriate for what you set its RowSource to
in cboFacLoc_AfterUpdate: it's saying 2 columns, while your row source is
changed to SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] ...,
which only has one column.

That may well be the issue.

The Debug statement is supposed to print the actual SQL you're trying to use
in the Immediate window. Use Ctrl-G to go into the VB Editor once you've
selected a value in cboFacLoc, and you should see the SQL printed out in the
window in the bottom right.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Properties...

Name: cboFacLoc
Control Source: Facility Location
Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].[Facility
Location]
FROM [Lawson Codes];
Column Count: 2
Colmn Heads: No
Colmn Widths 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit to List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mod: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 0
Left: 0.9583"
Top: 0.125"
Width: 0.7083"
Height: 0.1771"
Back Style: Normal
Back Color: -2147483643
Special Effect: Sunken
Border Style: Solid
Border Color: 0
Border Width: Hairline
Fore Color: -2147483640
Help Context Id: 0
After Update: Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Lawson Codes].Pk_ID " & _
"FROM [Lawson Codes] " & _
"WHERE [Facility Location] = '" & Me!cboFacLoc & "'"

Debug.Print strSQL


Me.cboPk_ID.RowSource = strSQL
Me.cboPk_ID.Requery
End Sub

Reading Order: Context
Keyborad Language: Shystem
Scroll Bar align: System
Numeral Shapes: System
Is Hyperlink: No


The other combo box...
Name: cboPk_ID
Control Source: Pk_ID

Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].Pk_ID FROM [Lawson
Codes];
Column Count: 2
Column Heads: No
Column Widths: 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit To List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mode: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 1
Help Context Id: 0
Reading Order: Context
Keyboard Language: System
Scroll Bar Align: System
Numeral Shapes: System
Is Hyperlink: No

I left out the font and border properties (I didn't think you cared to
know
about those). All other fields are blank in these two combo boxes. As
you
can see I put in the Debug.Print in the AfterUpdate Event procedure, but
I'm
not understanding what it's supposed to do.

What are the properties for your combobox?

And one test to try. Add a Debug.Print to your AfterUpdate routine, to
write
the SQL statement out to the Debug window (Ctrl-G):

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

Debug.Print strSQL

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

End Sub

Copy what's printed out for strSQL in the Debug window and paste it into
a
new query. Does the query run properly?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

I am SO sorry, but it's just not working. The cboPk_ID combo box is
still
showing up blank. I think I'll just resort to the uglier,
"more-complicated-for-the-user" way of doing it. But I thank you for
your
time.

ETC


:

You'll never get a list of unique X_ID values if you include No as
well.
And
since you know what Fac Loc value you're looking at, there's really
no
need
to include it in the query. The fact that Fac Loc is a text value
means
you
need to include quotes around the value you're passing:

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

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

End Sub

Exagerated for clarity, strSQL is:

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



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes, I have a field named No in my table. It's the primary key.
If I
set
the Row Source Type property set to Table/Query, then I don't know
how
to
keep objects from repeating on the dropdown list. But here, let me
try
this
again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1
$5.00
2 A P Z 3
$10.00
3 A Q Y 2
$20.00
4 A Q Z 3
$10.00
5 B R Y 1
$5.00
6 B R Z 3
$10.00
7 B S Y 2
$20.00
8 C S Z 2
$20.00
9 C T Y 3
$10.00
10 C T Z 4
$30.00

So, here's what I've done: On my Form, I deleted both combo boxes
and
started all over again. This time I used the wizard to have it
take
the
values from the existing table "L Codes". At that point all values
(including repeats) are listed. I went into cboFacLoc and in the
AfterUpdate
event I have the following Code:

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

After I do this, the FacLoc lists the same data (of course) and the
cboPk_ID
list is blank. No data is displayed for selecting.

(I'm sorry this is taking so long! I really appreciate all of your
time.
Please don't give up on me!)

ETC

:

If you're using SQL, you must have the Row Source Type property
set
to
Table/Query.

Also, you don't have a field named No in what you displayed
below:
is
there
such a field in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"ETC" <[email protected]> wrote in message

 

[Douglas J Steele]

The following properties should be changed for cboPk_ID:

Row Source: leave it blank (you're setting it later in code)
Column Count: 1
Column Widths: 1"

Okay, take that SQL you got from the Immediate window (SELECT DISTINCT
[Lawson Codes].Pk_ID FROM [Lawson Codes] WHERE [Facility Location] = '4')
and copy it into the clipboard.

Create a new query, and don't select any tables from the list that appears.
Select SQL View from the View menu, and paste the SQL into the window that
appears. Click on the Exclamation Point button on the button bar to run the
query (or select Query | Run from the menu).

Does the SQL return the appropriate information?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, what exactly should I change in the property for which combo box?

Also, when I hit Ctrl+G I get the following...

SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] WHERE [Facility
Location] = '4'

The details for cboPk_ID are inappropriate for what you set its RowSource to
in cboFacLoc_AfterUpdate: it's saying 2 columns, while your row source is
changed to SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] ...,
which only has one column.

That may well be the issue.

The Debug statement is supposed to print the actual SQL you're trying to use
in the Immediate window. Use Ctrl-G to go into the VB Editor once you've
selected a value in cboFacLoc, and you should see the SQL printed out in the
window in the bottom right.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Properties...

Name: cboFacLoc
Control Source: Facility Location
Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].[Facility
Location]
FROM [Lawson Codes];
Column Count: 2
Colmn Heads: No
Colmn Widths 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit to List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mod: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 0
Left: 0.9583"
Top: 0.125"
Width: 0.7083"
Height: 0.1771"
Back Style: Normal
Back Color: -2147483643
Special Effect: Sunken
Border Style: Solid
Border Color: 0
Border Width: Hairline
Fore Color: -2147483640
Help Context Id: 0
After Update: Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Lawson Codes].Pk_ID " & _
"FROM [Lawson Codes] " & _
"WHERE [Facility Location] = '" & Me!cboFacLoc & "'"

Debug.Print strSQL


Me.cboPk_ID.RowSource = strSQL
Me.cboPk_ID.Requery
End Sub

Reading Order: Context
Keyborad Language: Shystem
Scroll Bar align: System
Numeral Shapes: System
Is Hyperlink: No


The other combo box...
Name: cboPk_ID
Control Source: Pk_ID

Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].Pk_ID FROM [Lawson
Codes];
Column Count: 2
Column Heads: No
Column Widths: 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit To List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mode: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 1
Help Context Id: 0
Reading Order: Context
Keyboard Language: System
Scroll Bar Align: System
Numeral Shapes: System
Is Hyperlink: No

I left out the font and border properties (I didn't think you cared to
know
about those). All other fields are blank in these two combo boxes. As
you
can see I put in the Debug.Print in the AfterUpdate Event procedure, but
I'm
not understanding what it's supposed to do.








:

What are the properties for your combobox?

And one test to try. Add a Debug.Print to your AfterUpdate routine, to
write
the SQL statement out to the Debug window (Ctrl-G):

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

Debug.Print strSQL

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

End Sub

Copy what's printed out for strSQL in the Debug window and paste it into
a
new query. Does the query run properly?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

I am SO sorry, but it's just not working. The cboPk_ID combo box is
still
showing up blank. I think I'll just resort to the uglier,
"more-complicated-for-the-user" way of doing it. But I thank you for
your
time.

ETC


:

You'll never get a list of unique X_ID values if you include No as
well.
And
since you know what Fac Loc value you're looking at, there's really
no
need
to include it in the query. The fact that Fac Loc is a text value
means
you
need to include quotes around the value you're passing:

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

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

End Sub

Exagerated for clarity, strSQL is:

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



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes, I have a field named No in my table. It's the primary key.
If I
set
the Row Source Type property set to Table/Query, then I don't know
how
to
keep objects from repeating on the dropdown list. But here, let me
try
this
again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1
$5.00
2 A P Z 3
$10.00
3 A Q Y 2
$20.00
4 A Q Z 3
$10.00
5 B R Y 1
$5.00
6 B R Z 3
$10.00
7 B S Y 2
$20.00
8 C S Z 2
$20.00
9 C T Y 3
$10.00
10 C T Z 4
$30.00

So, here's what I've done: On my Form, I deleted both combo boxes
and
started all over again. This time I used the wizard to have it
take
the
values from the existing table "L Codes". At that point all values
(including repeats) are listed. I went into cboFacLoc and in the
AfterUpdate
event I have the following Code:

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

After I do this, the FacLoc lists the same data (of course) and the
cboPk_ID
list is blank. No data is displayed for selecting.

(I'm sorry this is taking so long! I really appreciate all of your
time.
Please don't give up on me!)

ETC

:

If you're using SQL, you must have the Row Source Type property
set
to
Table/Query.

Also, you don't have a field named No in what you displayed
below:
is
there
such a field in your table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"ETC" <[email protected]> wrote in message

 

[Guest]

I made the changes. Did another Ctrl+G, pasted the line into SQL view and
ran it. It didn't display any values at all...just the field heading.

The following properties should be changed for cboPk_ID:

Row Source: leave it blank (you're setting it later in code)
Column Count: 1
Column Widths: 1"

Okay, take that SQL you got from the Immediate window (SELECT DISTINCT
[Lawson Codes].Pk_ID FROM [Lawson Codes] WHERE [Facility Location] = '4')
and copy it into the clipboard.

Create a new query, and don't select any tables from the list that appears.
Select SQL View from the View menu, and paste the SQL into the window that
appears. Click on the Exclamation Point button on the button bar to run the
query (or select Query | Run from the menu).

Does the SQL return the appropriate information?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, what exactly should I change in the property for which combo box?

Also, when I hit Ctrl+G I get the following...

SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] WHERE [Facility
Location] = '4'

The details for cboPk_ID are inappropriate for what you set its RowSource to
in cboFacLoc_AfterUpdate: it's saying 2 columns, while your row source is
changed to SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] ...,
which only has one column.

That may well be the issue.

The Debug statement is supposed to print the actual SQL you're trying to use
in the Immediate window. Use Ctrl-G to go into the VB Editor once you've
selected a value in cboFacLoc, and you should see the SQL printed out in the
window in the bottom right.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Properties...

Name: cboFacLoc
Control Source: Facility Location
Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].[Facility
Location]
FROM [Lawson Codes];
Column Count: 2
Colmn Heads: No
Colmn Widths 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit to List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mod: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 0
Left: 0.9583"
Top: 0.125"
Width: 0.7083"
Height: 0.1771"
Back Style: Normal
Back Color: -2147483643
Special Effect: Sunken
Border Style: Solid
Border Color: 0
Border Width: Hairline
Fore Color: -2147483640
Help Context Id: 0
After Update: Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Lawson Codes].Pk_ID " & _
"FROM [Lawson Codes] " & _
"WHERE [Facility Location] = '" & Me!cboFacLoc & "'"

Debug.Print strSQL


Me.cboPk_ID.RowSource = strSQL
Me.cboPk_ID.Requery
End Sub

Reading Order: Context
Keyborad Language: Shystem
Scroll Bar align: System
Numeral Shapes: System
Is Hyperlink: No


The other combo box...
Name: cboPk_ID
Control Source: Pk_ID

Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].Pk_ID FROM [Lawson
Codes];
Column Count: 2
Column Heads: No
Column Widths: 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit To List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mode: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 1
Help Context Id: 0
Reading Order: Context
Keyboard Language: System
Scroll Bar Align: System
Numeral Shapes: System
Is Hyperlink: No

I left out the font and border properties (I didn't think you cared to
know
about those). All other fields are blank in these two combo boxes. As
you
can see I put in the Debug.Print in the AfterUpdate Event procedure, but
I'm
not understanding what it's supposed to do.








:

What are the properties for your combobox?

And one test to try. Add a Debug.Print to your AfterUpdate routine, to
write
the SQL statement out to the Debug window (Ctrl-G):

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

Debug.Print strSQL

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

End Sub

Copy what's printed out for strSQL in the Debug window and paste it into
a
new query. Does the query run properly?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

I am SO sorry, but it's just not working. The cboPk_ID combo box is
still
showing up blank. I think I'll just resort to the uglier,
"more-complicated-for-the-user" way of doing it. But I thank you for
your
time.

ETC


:

You'll never get a list of unique X_ID values if you include No as
well.
And
since you know what Fac Loc value you're looking at, there's really
no
need
to include it in the query. The fact that Fac Loc is a text value
means
you
need to include quotes around the value you're passing:

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

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

End Sub

Exagerated for clarity, strSQL is:

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



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes, I have a field named No in my table. It's the primary key.
If I
set
the Row Source Type property set to Table/Query, then I don't know
how
to
keep objects from repeating on the dropdown list. But here, let me
try
this
again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1
$5.00
2 A P Z 3
$10.00
3 A Q Y 2
$20.00
4 A Q Z 3
$10.00
5 B R Y 1
$5.00
6 B R Z 3
$10.00
7 B S Y 2
$20.00
8 C S Z 2
$20.00
9 C T Y 3
$10.00
10 C T Z 4
$30.00

 

[Douglas J. Steele]

If the query's not returning any rows, that would explain why you're not
getting anything in your combobox.

You sure you've got records with [Facility Location] = '4'?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I made the changes. Did another Ctrl+G, pasted the line into SQL view and
ran it. It didn't display any values at all...just the field heading.

The following properties should be changed for cboPk_ID:

Row Source: leave it blank (you're setting it later in code)
Column Count: 1
Column Widths: 1"

Okay, take that SQL you got from the Immediate window (SELECT DISTINCT
[Lawson Codes].Pk_ID FROM [Lawson Codes] WHERE [Facility Location] = '4')
and copy it into the clipboard.

Create a new query, and don't select any tables from the list that
appears.
Select SQL View from the View menu, and paste the SQL into the window
that
appears. Click on the Exclamation Point button on the button bar to run
the
query (or select Query | Run from the menu).

Does the SQL return the appropriate information?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, what exactly should I change in the property for which combo box?

Also, when I hit Ctrl+G I get the following...

SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes] WHERE
[Facility
Location] = '4'

:

The details for cboPk_ID are inappropriate for what you set its RowSource to
in cboFacLoc_AfterUpdate: it's saying 2 columns, while your row
source is
changed to SELECT DISTINCT [Lawson Codes].Pk_ID FROM [Lawson Codes]
...,
which only has one column.

That may well be the issue.

The Debug statement is supposed to print the actual SQL you're trying
to use
in the Immediate window. Use Ctrl-G to go into the VB Editor once
you've
selected a value in cboFacLoc, and you should see the SQL printed out
in the
window in the bottom right.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Properties...

Name: cboFacLoc
Control Source: Facility Location
Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].[Facility
Location]
FROM [Lawson Codes];
Column Count: 2
Colmn Heads: No
Colmn Widths 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit to List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mod: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 0
Left: 0.9583"
Top: 0.125"
Width: 0.7083"
Height: 0.1771"
Back Style: Normal
Back Color: -2147483643
Special Effect: Sunken
Border Style: Solid
Border Color: 0
Border Width: Hairline
Fore Color: -2147483640
Help Context Id: 0
After Update: Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Lawson Codes].Pk_ID " & _
"FROM [Lawson Codes] " & _
"WHERE [Facility Location] = '" & Me!cboFacLoc & "'"

Debug.Print strSQL


Me.cboPk_ID.RowSource = strSQL
Me.cboPk_ID.Requery
End Sub

Reading Order: Context
Keyborad Language: Shystem
Scroll Bar align: System
Numeral Shapes: System
Is Hyperlink: No


The other combo box...
Name: cboPk_ID
Control Source: Pk_ID

Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [Lawson Codes].[No], [Lawson Codes].Pk_ID FROM [Lawson
Codes];
Column Count: 2
Column Heads: No
Column Widths: 0";1"
Bound Column: 1
List Rows: 8
List Width: 1"
Limit To List: Yes
Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mode: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow AutoCorrect: Yes
Tab Stop: Yes
Tab Index: 1
Help Context Id: 0
Reading Order: Context
Keyboard Language: System
Scroll Bar Align: System
Numeral Shapes: System
Is Hyperlink: No

I left out the font and border properties (I didn't think you cared
to
know
about those). All other fields are blank in these two combo boxes. As
you
can see I put in the Debug.Print in the AfterUpdate Event
procedure, but
I'm
not understanding what it's supposed to do.








:

What are the properties for your combobox?

And one test to try. Add a Debug.Print to your AfterUpdate
routine, to
write
the SQL statement out to the Debug window (Ctrl-G):

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

Debug.Print strSQL

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

End Sub

Copy what's printed out for strSQL in the Debug window and paste
it into
a
new query. Does the query run properly?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

I am SO sorry, but it's just not working. The cboPk_ID combo
box is
still
showing up blank. I think I'll just resort to the uglier,
"more-complicated-for-the-user" way of doing it. But I thank
you for
your
time.

ETC


:

You'll never get a list of unique X_ID values if you include
No as
well.
And
since you know what Fac Loc value you're looking at, there's really
no
need
to include it in the query. The fact that Fac Loc is a text
value
means
you
need to include quotes around the value you're passing:

Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String

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

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

End Sub

Exagerated for clarity, strSQL is:

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



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yes, I have a field named No in my table. It's the primary key.
If I
set
the Row Source Type property set to Table/Query, then I
don't know
how
to
keep objects from repeating on the dropdown list. But here, let me
try
this
again...

My L Codes Table looks like:

No Fac Loc X_ID X Type L Code Price
1 A P Y 1
$5.00
2 A P Z 3
$10.00
3 A Q Y 2
$20.00
4 A Q Z 3
$10.00
5 B R Y 1
$5.00
6 B R Z 3
$10.00
7 B S Y 2
$20.00
8 C S Z 2
$20.00
9 C T Y 3
$10.00
10 C T Z 4
$30.00