How to limit Access listbox lookup query using another field

[PJD]

In a table I'm wanting to have a field be a listbox where the selectable
values are grabbed from a second table. I want to limit this using the value
in a different field of the first table... how do I specify this in the query.

Lets say I have fields Level1 and Level2 in Table1 and a list in a table
called Topics that has TopicL1 and TopicL2 as the allowable level 1 and level
2 topics. In the first table I've got a query that limits the entry for
Level1 to the values in TopicL1 of Topics. I can't figure out how to
construct a query for a listbox for Level2 that uses the value in Level1 to
limit the TopicL2 entries shown to only those with a matching TopicL1 =
Level1 value.

I'm fairly new to databases and Access... all help greatly appreciated.

Thanks

 

[Ken Snell \(MVP\)]

See this article -- it discusses comboboxes, but is equally applicable to
listboxes:
http://www.mvps.org/access/forms/frm0058.htm

 

[PJD]

Given that I'm using this just in a table, not a form, I'm still not sure how
to refer to the listbox in a different field. Is this not possible in a
table? I can't figure out how to refer to the value in another field to put
in a WHERE clause of a query for another field's listbox.

 

[Duane Hookom]

You can't do it in a table but that's okay since you shouldn't be using
tables for user interface. Use forms. I also don't like lookup fields defined
in tables.

 

[PJD]

In any form that shows multi records, split form or datasheet view, the same
problem occurs. I can't seem to have a query in one field of a row that uses
the value from a different field of that same row. I got something that at
first seemed to work but it uses the value for the specified field always in
record #1 not the current record. This is as shown...


For the RowSource for the listbox of Level2 I have the folloing query

SELECT Topics.ID, Topics.TopicL2, Topics.TopicL1
FROM Topics
WHERE (((Topics.Topics.TopicL1)=[Level1]))
ORDER BY Topics.TopicL1, Topics.TopicL2;


PROBLEM is that Level1 here seems to always refer to the Level1 field of
record #1 even if I'm selecting the Level1 field of a different row in the
datasheet. How do I make this reference to the field "Level1" refer to the
correct current record?

Please... if anyone knows how to do this, I would be very grateful.

Thanks

 

[Duane Hookom]

If your list boxes depend on a value in the current record, you will need to
requery the list box in the On Current event of the form.

--
Duane Hookom
Microsoft Access MVP

In any form that shows multi records, split form or datasheet view, the same
problem occurs. I can't seem to have a query in one field of a row that uses
the value from a different field of that same row. I got something that at
first seemed to work but it uses the value for the specified field always in
record #1 not the current record. This is as shown...


For the RowSource for the listbox of Level2 I have the folloing query

SELECT Topics.ID, Topics.TopicL2, Topics.TopicL1
FROM Topics
WHERE (((Topics.Topics.TopicL1)=[Level1]))
ORDER BY Topics.TopicL1, Topics.TopicL2;


PROBLEM is that Level1 here seems to always refer to the Level1 field of
record #1 even if I'm selecting the Level1 field of a different row in the
datasheet. How do I make this reference to the field "Level1" refer to the
correct current record?

Please... if anyone knows how to do this, I would be very grateful.

Thanks

You can't do it in a table but that's okay since you shouldn't be using
tables for user interface. Use forms. I also don't like lookup fields defined
in tables.

 

[PJD]

I did discover the need to requery after changing the Level1 field. However,
the problem still exists that all of the queries to set the values for the
listbox of Level2 field are using the value of the Level1 field in the first
record... not the record for which I am currently selecting the listbox.

Thanks for the offer.

If your list boxes depend on a value in the current record, you will need to
requery the list box in the On Current event of the form.

--
Duane Hookom
Microsoft Access MVP

In any form that shows multi records, split form or datasheet view, the same
problem occurs. I can't seem to have a query in one field of a row that uses
the value from a different field of that same row. I got something that at
first seemed to work but it uses the value for the specified field always in
record #1 not the current record. This is as shown...


For the RowSource for the listbox of Level2 I have the folloing query

SELECT Topics.ID, Topics.TopicL2, Topics.TopicL1
FROM Topics
WHERE (((Topics.Topics.TopicL1)=[Level1]))
ORDER BY Topics.TopicL1, Topics.TopicL2;


PROBLEM is that Level1 here seems to always refer to the Level1 field of
record #1 even if I'm selecting the Level1 field of a different row in the
datasheet. How do I make this reference to the field "Level1" refer to the
correct current record?

Please... if anyone knows how to do this, I would be very grateful.

Thanks

You can't do it in a table but that's okay since you shouldn't be using
tables for user interface. Use forms. I also don't like lookup fields defined
in tables.

--
Duane Hookom
Microsoft Access MVP


:

Given that I'm using this just in a table, not a form, I'm still not sure how
to refer to the listbox in a different field. Is this not possible in a
table? I can't figure out how to refer to the value in another field to put
in a WHERE clause of a query for another field's listbox.

:

See this article -- it discusses comboboxes, but is equally applicable to
listboxes:
http://www.mvps.org/access/forms/frm0058.htm
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


In a table I'm wanting to have a field be a listbox where the selectable
values are grabbed from a second table. I want to limit this using the
value
in a different field of the first table... how do I specify this in the
query.

Lets say I have fields Level1 and Level2 in Table1 and a list in a table
called Topics that has TopicL1 and TopicL2 as the allowable level 1 and
level
2 topics. In the first table I've got a query that limits the entry for
Level1 to the values in TopicL1 of Topics. I can't figure out how to
construct a query for a listbox for Level2 that uses the value in Level1
to
limit the TopicL2 entries shown to only those with a matching TopicL1 =
Level1 value.

I'm fairly new to databases and Access... all help greatly appreciated.

Thanks

 

[Duane Hookom]

The requery of the list box worked for me. The form was a single form view
but it should also work on a continuous although I'm not sure how/why you
would have a list box (not a combo box) in a continuous form.

Can you share your code?

--
Duane Hookom
Microsoft Access MVP

I did discover the need to requery after changing the Level1 field. However,
the problem still exists that all of the queries to set the values for the
listbox of Level2 field are using the value of the Level1 field in the first
record... not the record for which I am currently selecting the listbox.

Thanks for the offer.

If your list boxes depend on a value in the current record, you will need to
requery the list box in the On Current event of the form.

--
Duane Hookom
Microsoft Access MVP

In any form that shows multi records, split form or datasheet view, the same
problem occurs. I can't seem to have a query in one field of a row that uses
the value from a different field of that same row. I got something that at
first seemed to work but it uses the value for the specified field always in
record #1 not the current record. This is as shown...


For the RowSource for the listbox of Level2 I have the folloing query

SELECT Topics.ID, Topics.TopicL2, Topics.TopicL1
FROM Topics
WHERE (((Topics.Topics.TopicL1)=[Level1]))
ORDER BY Topics.TopicL1, Topics.TopicL2;


PROBLEM is that Level1 here seems to always refer to the Level1 field of
record #1 even if I'm selecting the Level1 field of a different row in the
datasheet. How do I make this reference to the field "Level1" refer to the
correct current record?

Please... if anyone knows how to do this, I would be very grateful.

Thanks


:

You can't do it in a table but that's okay since you shouldn't be using
tables for user interface. Use forms. I also don't like lookup fields defined
in tables.

--
Duane Hookom
Microsoft Access MVP


:

Given that I'm using this just in a table, not a form, I'm still not sure how
to refer to the listbox in a different field. Is this not possible in a
table? I can't figure out how to refer to the value in another field to put
in a WHERE clause of a query for another field's listbox.

:

See this article -- it discusses comboboxes, but is equally applicable to
listboxes:
http://www.mvps.org/access/forms/frm0058.htm
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


In a table I'm wanting to have a field be a listbox where the selectable
values are grabbed from a second table. I want to limit this using the
value
in a different field of the first table... how do I specify this in the
query.

Lets say I have fields Level1 and Level2 in Table1 and a list in a table
called Topics that has TopicL1 and TopicL2 as the allowable level 1 and
level
2 topics. In the first table I've got a query that limits the entry for
Level1 to the values in TopicL1 of Topics. I can't figure out how to
construct a query for a listbox for Level2 that uses the value in Level1
to
limit the TopicL2 entries shown to only those with a matching TopicL1 =
Level1 value.

I'm fairly new to databases and Access... all help greatly appreciated.

Thanks

 

[PJD]

The requery part isn't the problem... at least I don't think so... that also
seems to work for me. The code is what I gave below... it is the SELECT
query for the RowSource for the listbox/combobox (seems to misbehave with
either of these). The problem is the WHERE
(((Topics.Topics.TopicL1)=[Level1])) part of the select in which the Level1
seems to always use the value of the Level1 field of Record #1, even for the
query for listbox/combobox for other rows.

Thanks

The requery of the list box worked for me. The form was a single form view
but it should also work on a continuous although I'm not sure how/why you
would have a list box (not a combo box) in a continuous form.

Can you share your code?

--
Duane Hookom
Microsoft Access MVP

I did discover the need to requery after changing the Level1 field. However,
the problem still exists that all of the queries to set the values for the
listbox of Level2 field are using the value of the Level1 field in the first
record... not the record for which I am currently selecting the listbox.

Thanks for the offer.

If your list boxes depend on a value in the current record, you will need to
requery the list box in the On Current event of the form.

--
Duane Hookom
Microsoft Access MVP


:

In any form that shows multi records, split form or datasheet view, the same
problem occurs. I can't seem to have a query in one field of a row that uses
the value from a different field of that same row. I got something that at
first seemed to work but it uses the value for the specified field always in
record #1 not the current record. This is as shown...


For the RowSource for the listbox of Level2 I have the folloing query

SELECT Topics.ID, Topics.TopicL2, Topics.TopicL1
FROM Topics
WHERE (((Topics.Topics.TopicL1)=[Level1]))
ORDER BY Topics.TopicL1, Topics.TopicL2;


PROBLEM is that Level1 here seems to always refer to the Level1 field of
record #1 even if I'm selecting the Level1 field of a different row in the
datasheet. How do I make this reference to the field "Level1" refer to the
correct current record?

Please... if anyone knows how to do this, I would be very grateful.

Thanks


:

You can't do it in a table but that's okay since you shouldn't be using
tables for user interface. Use forms. I also don't like lookup fields defined
in tables.

--
Duane Hookom
Microsoft Access MVP


:

Given that I'm using this just in a table, not a form, I'm still not sure how
to refer to the listbox in a different field. Is this not possible in a
table? I can't figure out how to refer to the value in another field to put
in a WHERE clause of a query for another field's listbox.

:

See this article -- it discusses comboboxes, but is equally applicable to
listboxes:
http://www.mvps.org/access/forms/frm0058.htm
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


In a table I'm wanting to have a field be a listbox where the selectable
values are grabbed from a second table. I want to limit this using the
value
in a different field of the first table... how do I specify this in the
query.

Lets say I have fields Level1 and Level2 in Table1 and a list in a table
called Topics that has TopicL1 and TopicL2 as the allowable level 1 and
level
2 topics. In the first table I've got a query that limits the entry for
Level1 to the values in TopicL1 of Topics. I can't figure out how to
construct a query for a listbox for Level2 that uses the value in Level1
to
limit the TopicL2 entries shown to only those with a matching TopicL1 =
Level1 value.

I'm fairly new to databases and Access... all help greatly appreciated.

Thanks

 

[Duane Hookom]

Your sql statement is not what I meant by "code". I was expecting you to
reply back with the code that is used to "requery the list box in the On
Current event of the form".

If you haven't written any code in the On Current event of your form then
that is your issue. If you have written some code in there then please reply
with the code.

Your code might look something like:
Private Sub Form_Current()
Me.lboLevel2.Requery
End Sub
--
Duane Hookom
Microsoft Access MVP

The requery part isn't the problem... at least I don't think so... that also
seems to work for me. The code is what I gave below... it is the SELECT
query for the RowSource for the listbox/combobox (seems to misbehave with
either of these). The problem is the WHERE
(((Topics.Topics.TopicL1)=[Level1])) part of the select in which the Level1
seems to always use the value of the Level1 field of Record #1, even for the
query for listbox/combobox for other rows.

Thanks

The requery of the list box worked for me. The form was a single form view
but it should also work on a continuous although I'm not sure how/why you
would have a list box (not a combo box) in a continuous form.

Can you share your code?

--
Duane Hookom
Microsoft Access MVP

I did discover the need to requery after changing the Level1 field. However,
the problem still exists that all of the queries to set the values for the
listbox of Level2 field are using the value of the Level1 field in the first
record... not the record for which I am currently selecting the listbox.

Thanks for the offer.

:

If your list boxes depend on a value in the current record, you will need to
requery the list box in the On Current event of the form.

--
Duane Hookom
Microsoft Access MVP


:

In any form that shows multi records, split form or datasheet view, the same
problem occurs. I can't seem to have a query in one field of a row that uses
the value from a different field of that same row. I got something that at
first seemed to work but it uses the value for the specified field always in
record #1 not the current record. This is as shown...


For the RowSource for the listbox of Level2 I have the folloing query

SELECT Topics.ID, Topics.TopicL2, Topics.TopicL1
FROM Topics
WHERE (((Topics.Topics.TopicL1)=[Level1]))
ORDER BY Topics.TopicL1, Topics.TopicL2;


PROBLEM is that Level1 here seems to always refer to the Level1 field of
record #1 even if I'm selecting the Level1 field of a different row in the
datasheet. How do I make this reference to the field "Level1" refer to the
correct current record?

Please... if anyone knows how to do this, I would be very grateful.

Thanks


:

You can't do it in a table but that's okay since you shouldn't be using
tables for user interface. Use forms. I also don't like lookup fields defined
in tables.

--
Duane Hookom
Microsoft Access MVP


:

Given that I'm using this just in a table, not a form, I'm still not sure how
to refer to the listbox in a different field. Is this not possible in a
table? I can't figure out how to refer to the value in another field to put
in a WHERE clause of a query for another field's listbox.

:

See this article -- it discusses comboboxes, but is equally applicable to
listboxes:
http://www.mvps.org/access/forms/frm0058.htm
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


In a table I'm wanting to have a field be a listbox where the selectable
values are grabbed from a second table. I want to limit this using the
value
in a different field of the first table... how do I specify this in the
query.

Lets say I have fields Level1 and Level2 in Table1 and a list in a table
called Topics that has TopicL1 and TopicL2 as the allowable level 1 and
level
2 topics. In the first table I've got a query that limits the entry for
Level1 to the values in TopicL1 of Topics. I can't figure out how to
construct a query for a listbox for Level2 that uses the value in Level1
to
limit the TopicL2 entries shown to only those with a matching TopicL1 =
Level1 value.

I'm fairly new to databases and Access... all help greatly appreciated.

Thanks

 

[PJD]

It seems like the the requery I'm using is working since the Level2 list does
change upon changing Level1. Problem is all the Level2 lists for any record
use the setting of the value for Level1 from Record #1. Perhaps there is
something about this that I'm fundamentally not getting. Could you give a
brief explanation for why you think it's a requery problem given the behavior
I'm describing? To me it seems more that the value of the term "Level1" in
my query does not mean what I think it should... i.e. the value of that field
for the particular record for which the listbox query is being performed. It
appears that different records in a datasheet list can't have different sets
of listbox values... but I figure there mus be some way around this.

Your sql statement is not what I meant by "code". I was expecting you to
reply back with the code that is used to "requery the list box in the On
Current event of the form".

If you haven't written any code in the On Current event of your form then
that is your issue. If you have written some code in there then please reply
with the code.

Your code might look something like:
Private Sub Form_Current()
Me.lboLevel2.Requery
End Sub
--
Duane Hookom
Microsoft Access MVP

The requery part isn't the problem... at least I don't think so... that also
seems to work for me. The code is what I gave below... it is the SELECT
query for the RowSource for the listbox/combobox (seems to misbehave with
either of these). The problem is the WHERE
(((Topics.Topics.TopicL1)=[Level1])) part of the select in which the Level1
seems to always use the value of the Level1 field of Record #1, even for the
query for listbox/combobox for other rows.

Thanks

The requery of the list box worked for me. The form was a single form view
but it should also work on a continuous although I'm not sure how/why you
would have a list box (not a combo box) in a continuous form.

Can you share your code?

--
Duane Hookom
Microsoft Access MVP


:

I did discover the need to requery after changing the Level1 field. However,
the problem still exists that all of the queries to set the values for the
listbox of Level2 field are using the value of the Level1 field in the first
record... not the record for which I am currently selecting the listbox.

Thanks for the offer.

:

If your list boxes depend on a value in the current record, you will need to
requery the list box in the On Current event of the form.

--
Duane Hookom
Microsoft Access MVP


:

In any form that shows multi records, split form or datasheet view, the same
problem occurs. I can't seem to have a query in one field of a row that uses
the value from a different field of that same row. I got something that at
first seemed to work but it uses the value for the specified field always in
record #1 not the current record. This is as shown...


For the RowSource for the listbox of Level2 I have the folloing query

SELECT Topics.ID, Topics.TopicL2, Topics.TopicL1
FROM Topics
WHERE (((Topics.Topics.TopicL1)=[Level1]))
ORDER BY Topics.TopicL1, Topics.TopicL2;


PROBLEM is that Level1 here seems to always refer to the Level1 field of
record #1 even if I'm selecting the Level1 field of a different row in the
datasheet. How do I make this reference to the field "Level1" refer to the
correct current record?

Please... if anyone knows how to do this, I would be very grateful.

Thanks


:

You can't do it in a table but that's okay since you shouldn't be using
tables for user interface. Use forms. I also don't like lookup fields defined
in tables.

--
Duane Hookom
Microsoft Access MVP


:

Given that I'm using this just in a table, not a form, I'm still not sure how
to refer to the listbox in a different field. Is this not possible in a
table? I can't figure out how to refer to the value in another field to put
in a WHERE clause of a query for another field's listbox.

:

See this article -- it discusses comboboxes, but is equally applicable to
listboxes:
http://www.mvps.org/access/forms/frm0058.htm
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


In a table I'm wanting to have a field be a listbox where the selectable
values are grabbed from a second table. I want to limit this using the
value
in a different field of the first table... how do I specify this in the
query.

Lets say I have fields Level1 and Level2 in Table1 and a list in a table
called Topics that has TopicL1 and TopicL2 as the allowable level 1 and
level
2 topics. In the first table I've got a query that limits the entry for
Level1 to the values in TopicL1 of Topics. I can't figure out how to
construct a query for a listbox for Level2 that uses the value in Level1
to
limit the TopicL2 entries shown to only those with a matching TopicL1 =
Level1 value.

I'm fairly new to databases and Access... all help greatly appreciated.

Thanks

 

[John Spencer]

There is not really a way to vary the list/combobox choices in a continuous
view or a datasheet view. The problem is that there is basically one control
that you see an image of on each "row" of data.

You can update the choices by moving from row to row and doing a requery in
the current event. But the list of choices will change for every image on the
screen.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

Agreed (with John).
I still don't think you are performing a requery since you have not
responded to my request to provide the code.

 

[PJD]

John,

Thanks... at least I won't be chashing after something that can't work.
What you say is consistent with the behavior I'm seeing. Doing the requery
indeed changes what is displayed on EVERY row, not just the row currently
selected.

The "programming model" for Access seems rather complicated... and not at
all documented, at least in the books I've bought about Access. Where does
one learn the underlying "object model"/"programming model" to learn how all
these disperate snippits of codes and queries work on the underlying objects?

Is there somewhere, for example, that documents that all rows in a datasheet
show common listbox instances, or is this something that one must simply
deduct from trial and error. It seems like this model of software
development is a big step backward.

Thanks

 

[Duane Hookom]

I guess I expect that since there is only one listbox control designed but
displayed multiple times, they would share the same attributes such as Row
Source. When this changes (conditional formatting) that gets documented.

It would be nice but take a lot of extra resources to have differing row
sources per record. I'm not sure a List Box on a continuous form is the ideal
solution. I can more easily picture a combo box.