multi select list box question

[Slez via AccessMonster.com]

I'm having a bit of difficulty trying to get a multi select list box to
display related records to a form's control. I would like to achieve one of
two different options. I'm hoping that my explanation makes enough sense
that someone can offer their opinion of what is the proper way to set this up.


I have a control on frmMainScreen called cboProjectName. Its Row Source is:
SELECT Project.ProjectID, Project.ProjectName FROM Project ORDER BY
ProjectName;

On that form is a subform called frmProjectSubformTab, which has a tab
control on it. On those tabs are various controls and forms which display
related records to whatever is selected in cboProjectName. I wish to add
another subform called frmEstimateSelector to one of the tabs which will
serve as a place where users can select 1 or multiple records and print
various reports listed in an Option Group.

Here's where the option comes in. I believe I can either reference
cboProjectName on frmMainScreen OR a control ProjectID on frmEstimateSelector.
I've been doing some trial and error and can't quite get the proper records
to display. My multi select list box is named "lstBidSelect", and its Row
Source is currently:
SELECT Bid.BidNumber, Bid.BidType, Bid.BidStatus FROM Bid WHERE (((Bid.
ProjectID)=[Forms]![frmEstimateSelector]![ProjectID])) ORDER BY BidNumber;

I have been partially successful when I had the following:
...WHERE (((Bid.ProjectID)=[Forms]![frmMainScreen]![cboProjectName]))...
It displayed the correct records when I opened frmEstimateSelector on its own,
but not when I viewed it through frmMainScreen.

I also have the following event in the control ProjectID on
frmEstimateSelector, but I don't know if that is necessary to return the
related records:
Private Sub ProjectID_AfterUpdate()
On Error Resume Next

lstBidSelect.RowSource = "Select Bid.BidNumber " & _
"FROM Bid " & _
"WHERE Bid.ProjectID = " & Me.ProjectID & _
" ORDER BY Bid.BidNumber;"
End Sub

I am quite certain my Row Source is incorrect, and welcome any guidance!
Slez

 

[Guest]

:

I have a control on frmMainScreen called cboProjectName.

On that form is a subform called frmProjectSubformTab, which has a tab
control on it. On those tabs are various controls and forms which display
related records to whatever is selected in cboProjectName. I wish to add
another subform called frmEstimateSelector to one of the tabs which will
serve as a place where users can select 1 or multiple records and print
various reports listed in an Option Group.

My multi select list box is named "lstBidSelect", and its Row
Source is currently:
SELECT Bid.BidNumber, Bid.BidType, Bid.BidStatus FROM Bid WHERE (((Bid.
ProjectID)=[Forms]![frmEstimateSelector]![ProjectID])) ORDER BY BidNumber;

I have been partially successful when I had the following:
...WHERE (((Bid.ProjectID)=[Forms]![frmMainScreen]![cboProjectName]))...
It displayed the correct records when I opened frmEstimateSelector on its own,
but not when I viewed it through frmMainScreen.

Slez

Hi Slez, you mention that you are successful when viewing independantly. You
may like to consider that you need to reference a control using a hierarchy
starting with the parent form, then the child subform control (emphasis on
control and not the subform itself). for example...

forms!parentForm!control
forms!parentForm!subformControl.form!control

.... and so forth. You can use the expression builder within a query design
view to explore this hierarchy.

Luck,
Jonathan Parminter

 

[Steve Schapel]

Slez,

I am not sure I have grasped the full picture of what you are doing
there. But at least part of the problem relates to your reference to
the frmEstimateSelector form. If this is a subform, it is not open in
its own right, but rather is displayed via the subform control on the
main form. Therefore, it has to be referred to as such.
[Forms]![frmEstimateSelector]![ProjectID] can not be evaluated unless
the frmEstimateSelector form is open, which it isn't. I think you need
like this...
[Forms]![frmMainScreen]![frmEstimateSelector].[Form]![ProjectID]
Well, that's if it is a subform on the frmMainScreen form. If it is a
subform on another subform, which it appears may be the case from your
description, you will need something else, for example...

[Forms]![frmMainScreen]![frmProjectSubformTab].[Form]![frmEstimateSelector].[Form]![ProjectID]

--
Steve Schapel, Microsoft Access MVP

I'm having a bit of difficulty trying to get a multi select list box to
display related records to a form's control. I would like to achieve one of
two different options. I'm hoping that my explanation makes enough sense
that someone can offer their opinion of what is the proper way to set this up.


I have a control on frmMainScreen called cboProjectName. Its Row Source is:
SELECT Project.ProjectID, Project.ProjectName FROM Project ORDER BY
ProjectName;

On that form is a subform called frmProjectSubformTab, which has a tab
control on it. On those tabs are various controls and forms which display
related records to whatever is selected in cboProjectName. I wish to add
another subform called frmEstimateSelector to one of the tabs which will
serve as a place where users can select 1 or multiple records and print
various reports listed in an Option Group.

Here's where the option comes in. I believe I can either reference
cboProjectName on frmMainScreen OR a control ProjectID on frmEstimateSelector.
I've been doing some trial and error and can't quite get the proper records
to display. My multi select list box is named "lstBidSelect", and its Row
Source is currently:
SELECT Bid.BidNumber, Bid.BidType, Bid.BidStatus FROM Bid WHERE (((Bid.
ProjectID)=[Forms]![frmEstimateSelector]![ProjectID])) ORDER BY BidNumber;

I have been partially successful when I had the following:
..WHERE (((Bid.ProjectID)=[Forms]![frmMainScreen]![cboProjectName]))...
It displayed the correct records when I opened frmEstimateSelector on its own,
but not when I viewed it through frmMainScreen.

I also have the following event in the control ProjectID on
frmEstimateSelector, but I don't know if that is necessary to return the
related records:
Private Sub ProjectID_AfterUpdate()
On Error Resume Next

lstBidSelect.RowSource = "Select Bid.BidNumber " & _
"FROM Bid " & _
"WHERE Bid.ProjectID = " & Me.ProjectID & _
" ORDER BY Bid.BidNumber;"
End Sub

I am quite certain my Row Source is incorrect, and welcome any guidance!
Slez

 

[Slez via AccessMonster.com]

Thanks for the replies!
It makes sense that the form is not "open" but rather displayed. I tried the
different references as you recommended but got the same result. No records
are returned when it is being displayed as a subform. It works properly when
I add a command button to open the form, and that may be the method I need to
follow to make this work, but do you feel that code could be written in an
event that may keep the information current when being displayed?
...Just a thought...and wondering if anyone has a similar opinion...
Thanks again!
Slez

Slez,

I am not sure I have grasped the full picture of what you are doing
there. But at least part of the problem relates to your reference to
the frmEstimateSelector form. If this is a subform, it is not open in
its own right, but rather is displayed via the subform control on the
main form. Therefore, it has to be referred to as such.
[Forms]![frmEstimateSelector]![ProjectID] can not be evaluated unless
the frmEstimateSelector form is open, which it isn't. I think you need
like this...
[Forms]![frmMainScreen]![frmEstimateSelector].[Form]![ProjectID]
Well, that's if it is a subform on the frmMainScreen form. If it is a
subform on another subform, which it appears may be the case from your
description, you will need something else, for example...

[Forms]![frmMainScreen]![frmProjectSubformTab].[Form]![frmEstimateSelector].[Form]![ProjectID]

I'm having a bit of difficulty trying to get a multi select list box to
display related records to a form's control. I would like to achieve one of

[quoted text clipped - 39 lines]

I am quite certain my Row Source is incorrect, and welcome any guidance!
Slez

 

[Steve Schapel]

Slez,

Regarding "No records are returned when it is being displayed as a
subform", I'm sorry I can't see how this relates to your original
question. No records displayed in the frmEstimateSelector subform, you
mean? I didn't know that was the problem. I thought it had to do with
the Row Source of the lstBidSelect listbox. No? Where is that listbox?
I assumed it was on the frmMainScreen form. No?

 

[Slez via AccessMonster.com]

It relates because my initial post was that frmEstimateSelector was not
displaying records related to the control cboProjectName on frmMainScreen
when frmEstimateSelector is displayed on a tab control in the subform
frmProjectSubformTab. When I open the form via a command button, it displays
the proper records. When on frmProjectSubformTab, it displays no records.
I sure hope that makes sense!
Slez

Slez,

Regarding "No records are returned when it is being displayed as a
subform", I'm sorry I can't see how this relates to your original
question. No records displayed in the frmEstimateSelector subform, you
mean? I didn't know that was the problem. I thought it had to do with
the Row Source of the lstBidSelect listbox. No? Where is that listbox?
I assumed it was on the frmMainScreen form. No?

Thanks for the replies!
It makes sense that the form is not "open" but rather displayed. I tried the

[quoted text clipped - 6 lines]

Thanks again!
Slez

 

[Steve Schapel]

Slez,

Ok, thanks for the further clarification.

So, the problem is likely related either to the Record Source of
frmEstimateSelector, or to the Link Master Fields and Link Child Fields
settings of the frmEstimateSelector subform. Is frmEstimateSelector's
Record Source a query? If so, can you copy/paste the SQL view of that
query into your reply here please? And also, what have you got for the
subform's Link Master Fields and Link Child Fields property settings?

 

[Slez via AccessMonster.com]

I have tried changing the Record Source of frmEstimateSelector to the
underlying table, qryBid, or left it with "none". I get the same result with
each. Here is the SQL for qryBid:
SELECT Bid.ProjectID, Bid.BidNumber, Bid.BidStatus, Bid.BidDueDate, Bid.
PlanStatus, Bid.ProjectedMultiplier, Bid.BidType, Bid.EstimatedDelivery, Bid.
Estimator, Bid.BidDate, Bid.SalesTaxAmount, Bid.UseTaxAmount
FROM Bid
ORDER BY Bid.BidNumber;

I have the Link Master Fields and Link Child Fields both set to "ProjectID".
Incidently, something I just noticed is that when I open the form on its own,
it prompts me as expected. If I enter the ProjectID as a value of 19 (for
example), it displays the proper related records in the list box, but it does
not show that value in the control for ProjectID on the form. It shows the
correct ProjectID in the "permanent displayed" version, but there it does not
show, as previously mentioned, anything in the listbox. That seems to lead
me away from the Master/Child property settings being the cause.

Thanks again for all your extra attention to this topic!
Slez

Slez,

Ok, thanks for the further clarification.

So, the problem is likely related either to the Record Source of
frmEstimateSelector, or to the Link Master Fields and Link Child Fields
settings of the frmEstimateSelector subform. Is frmEstimateSelector's
Record Source a query? If so, can you copy/paste the SQL view of that
query into your reply here please? And also, what have you got for the
subform's Link Master Fields and Link Child Fields property settings?

It relates because my initial post was that frmEstimateSelector was not
displaying records related to the control cboProjectName on frmMainScreen

[quoted text clipped - 3 lines]

I sure hope that makes sense!
Slez

 

[Steve Schapel]

Slez,

Well, I was wondering when you were going to bring up this listbox
again! I think we will get there eventually. But I'm sorry, at
this stage I still can't figure out what the listbox is, and what form
it's on (I did ask you before), and where it fits in to the grand scheme
of things.

And here's something else that seems pertinent: "it prompts me as
expected". I wouldn't have expected that. You have a form
(frmEstimateSelector), based on qryBid, and that query includes a
ProjectID field. Right? So, opening the form independently should just
open. I can't see why you would be prompted for anything, much less the
ProjectID. Why would you expect that? At the moment, all I can see is
that if you are prompted for the ProjectID, it would perhaps indicate
that there is no ProjectD field in the Bid table, or else it is spelled
incorrectly in the query. If you open the datasheet view of qryBid, do
you get prompted for the ProjectID? Does the query datasheet display
the expected records?

I'm obviously missing something here.

 

[Slez via AccessMonster.com]

Steve,
I expect it to prompt me for the ProjectID when I open the form because the
Row Source of the listbox lstBidSelect contains ...WHERE (((Bid.ProjectID)=
[Forms]![frmMainScreen]![cboProjectName]))...
If frmMainScreen isn't open when I try to open frmEstimateSelector, it needs
to know what records it should look up. I hope I'm not out in left field on
thinking that. It seems to me that is normal Access behavior.

I may be just confusing you further along the way, so I'll just recap the
location of the control and associated forms:
My list box is called lstBidSelect is located on a form called
frmEstimateSelector.
frmEstimateSelector is inserted as a subform on frmProjectSubformTab.
frmProjectSubformTab is inserted as a subform on frmMainScreen.
cboProjectName is a control on frmMainScreen.

Perhaps reading this and then re-reading my initial post would help make
everything clear!?
Slez

Slez,

Well, I was wondering when you were going to bring up this listbox
again! I think we will get there eventually. But I'm sorry, at
this stage I still can't figure out what the listbox is, and what form
it's on (I did ask you before), and where it fits in to the grand scheme
of things.

And here's something else that seems pertinent: "it prompts me as
expected". I wouldn't have expected that. You have a form
(frmEstimateSelector), based on qryBid, and that query includes a
ProjectID field. Right? So, opening the form independently should just
open. I can't see why you would be prompted for anything, much less the
ProjectID. Why would you expect that? At the moment, all I can see is
that if you are prompted for the ProjectID, it would perhaps indicate
that there is no ProjectD field in the Bid table, or else it is spelled
incorrectly in the query. If you open the datasheet view of qryBid, do
you get prompted for the ProjectID? Does the query datasheet display
the expected records?

I'm obviously missing something here.

I have tried changing the Record Source of frmEstimateSelector to the
underlying table, qryBid, or left it with "none". I get the same result with

[quoted text clipped - 16 lines]

Thanks again for all your extra attention to this topic!
Slez

 

[Steve Schapel]

Slez,

Ok. Thanks. It was not apparent that the lstBidSelect listbox is on
the frmEstimateSelector subform. Now I can see where the parameter
prompt is coming from, and yes, you are quite correct about this.

So, the basic problem remains that the frmEstimateSelector subform does
not show any records. Whereas when it is opened independently as a
form, it does show records. Right?

And you have the Link Master Fields property of the subform set to
ProjectID. So that means that there must be a ProjectID control on the
frmProjectSubformTab subform. Is that correct? So the next question
is: Does the ProjectID on frmProjectSubformTab show the correct value?

 

[Slez via AccessMonster.com]

Whereas when it is opened independently as a form, it does show records.
Right?
This is correct!

So that means that there must be a ProjectID control on the
frmProjectSubformTab subform. Is that correct?
This is correct!

Does the ProjectID on frmProjectSubformTab show the correct value?
Yes it does!

I feel as though I have all my bases covered. Perhaps because it is a list
box, an event might be required??
Slez

Slez,

Ok. Thanks. It was not apparent that the lstBidSelect listbox is on
the frmEstimateSelector subform. Now I can see where the parameter
prompt is coming from, and yes, you are quite correct about this.

So, the basic problem remains that the frmEstimateSelector subform does
not show any records. Whereas when it is opened independently as a
form, it does show records. Right?

And you have the Link Master Fields property of the subform set to
ProjectID. So that means that there must be a ProjectID control on the
frmProjectSubformTab subform. Is that correct? So the next question
is: Does the ProjectID on frmProjectSubformTab show the correct value?

Steve,
I expect it to prompt me for the ProjectID when I open the form because the

[quoted text clipped - 15 lines]

everything clear!?
Slez