Filtering contents of list box based on value in prior field

[Guest]

Hello all

Apologies if this is something basic, as I'm new to Access.

I have a table that stores staff information with a column (call it ColB)
that looks up a value for StaffID from the StaffID table. That works fine.

I then have a column (call it ColC) that looks up project details for each
staff member; that, again, is a lookup from another table. It works but
shows me the information for ALL staff members and all projects.

Is there a way that the ColC lookup can show me ONLY those projects related
to the StaffID already chosen in ColB?

I've tried various parameters on the lookup for ColC but only succeeded in
getting into an endless loop of error messages!

Thanks in advance
Sue

 

[Guest]

Hi Sue
Just to be sure, when you say colB and ColC do you refer to two different
list box, when you want to limit the list in ColC acording to the selection
in colB?

If that the case, set the Row Source of colC to have a link to ColB

Select project From [Projects table name] Where StaffId = Forms![Form
Name]![ColB Name]

On the after update event of ColB write the code
Me.ColC.Requery

 

[Guest]

Hi Ofer

Many thanks for responding so quickly. It nearly works ... and I suspect
the reason it doesn't is my fault!

Firstly, I can't find out where to set the update event for ColB. This is
me being dumb I think!

Secondly, the lookup for ColC brings up a popup window where I need to type
in the ID number, then it returns the right row(s) from which to choose to
populate ColC. However, this doesn't refresh when I move on to entering data
for a different StaffID and I am sure that's because of not setting the
update event.

Thanks again - I know the only way to learn is to make mistakes but it's
still frustrating and I really appreciate the help I find in the forum.
SJB

Hi Sue
Just to be sure, when you say colB and ColC do you refer to two different
list box, when you want to limit the list in ColC acording to the selection
in colB?

If that the case, set the Row Source of colC to have a link to ColB

Select project From [Projects table name] Where StaffId = Forms![Form
Name]![ColB Name]

On the after update event of ColB write the code
Me.ColC.Requery

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

Hello all

Apologies if this is something basic, as I'm new to Access.

I have a table that stores staff information with a column (call it ColB)
that looks up a value for StaffID from the StaffID table. That works fine.

I then have a column (call it ColC) that looks up project details for each
staff member; that, again, is a lookup from another table. It works but
shows me the information for ALL staff members and all projects.

Is there a way that the ColC lookup can show me ONLY those projects related
to the StaffID already chosen in ColB?

I've tried various parameters on the lookup for ColC but only succeeded in
getting into an endless loop of error messages!

Thanks in advance
Sue

 

[Guest]

One of the properties of ColB is AfterUpdate event, when you pot the cursor
there, youll see a button on the right side with three dots, click on it,
select Code View.
And there put the code
Me.[ColC].Requery

About the prompt of list ColC, can you post the SQL?

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

Hi Ofer

Many thanks for responding so quickly. It nearly works ... and I suspect
the reason it doesn't is my fault!

Firstly, I can't find out where to set the update event for ColB. This is
me being dumb I think!

Secondly, the lookup for ColC brings up a popup window where I need to type
in the ID number, then it returns the right row(s) from which to choose to
populate ColC. However, this doesn't refresh when I move on to entering data
for a different StaffID and I am sure that's because of not setting the
update event.

Thanks again - I know the only way to learn is to make mistakes but it's
still frustrating and I really appreciate the help I find in the forum.
SJB

Hi Sue
Just to be sure, when you say colB and ColC do you refer to two different
list box, when you want to limit the list in ColC acording to the selection
in colB?

If that the case, set the Row Source of colC to have a link to ColB

Select project From [Projects table name] Where StaffId = Forms![Form
Name]![ColB Name]

On the after update event of ColB write the code
Me.ColC.Requery

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

Hello all

Apologies if this is something basic, as I'm new to Access.

I have a table that stores staff information with a column (call it ColB)
that looks up a value for StaffID from the StaffID table. That works fine.

I then have a column (call it ColC) that looks up project details for each
staff member; that, again, is a lookup from another table. It works but
shows me the information for ALL staff members and all projects.

Is there a way that the ColC lookup can show me ONLY those projects related
to the StaffID already chosen in ColB?

I've tried various parameters on the lookup for ColC but only succeeded in
getting into an endless loop of error messages!

Thanks in advance
Sue

 

[Guest]

Ofer

Thanks again for this. I am going to put it on the backburner for now as
it's working well enough to get on with the task I need to complete, but I'll
revisit this when I have some time!

Best wishes
SJB

One of the properties of ColB is AfterUpdate event, when you pot the cursor
there, youll see a button on the right side with three dots, click on it,
select Code View.
And there put the code
Me.[ColC].Requery

About the prompt of list ColC, can you post the SQL?

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

Hi Ofer

Many thanks for responding so quickly. It nearly works ... and I suspect
the reason it doesn't is my fault!

Firstly, I can't find out where to set the update event for ColB. This is
me being dumb I think!

Secondly, the lookup for ColC brings up a popup window where I need to type
in the ID number, then it returns the right row(s) from which to choose to
populate ColC. However, this doesn't refresh when I move on to entering data
for a different StaffID and I am sure that's because of not setting the
update event.

Thanks again - I know the only way to learn is to make mistakes but it's
still frustrating and I really appreciate the help I find in the forum.
SJB

Hi Sue
Just to be sure, when you say colB and ColC do you refer to two different
list box, when you want to limit the list in ColC acording to the selection
in colB?

If that the case, set the Row Source of colC to have a link to ColB

Select project From [Projects table name] Where StaffId = Forms![Form
Name]![ColB Name]

On the after update event of ColB write the code
Me.ColC.Requery

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


:

Hello all

Apologies if this is something basic, as I'm new to Access.

I have a table that stores staff information with a column (call it ColB)
that looks up a value for StaffID from the StaffID table. That works fine.

I then have a column (call it ColC) that looks up project details for each
staff member; that, again, is a lookup from another table. It works but
shows me the information for ALL staff members and all projects.

Is there a way that the ColC lookup can show me ONLY those projects related
to the StaffID already chosen in ColB?

I've tried various parameters on the lookup for ColC but only succeeded in
getting into an endless loop of error messages!

Thanks in advance
Sue