Unbound listbox in continuous subform

[Simon P]

Goodday! I've posted this request on the database group, but maybe
it's better suited in here.

I have the following tables : CONTACTS (ContactID, FirstName,
LastName, Company, etc.), SHOWS (ShowID, ShowDescription) and
CONTACTSHOW (links the previous tables together so not to have a
many-to-many relationship -- has the ContactID and ShowID fields).

I have a main form with a couple of listboxes which are used for
querying the CONTACTS table. The results populate bound textboxes of a
CONTINUOUS subform. This is fairly simple and works just fine. The
problem starts with an UNBOUND ShowAttended listbox on the SUBFORM
(which appears on each record shows, just as the bound textboxes do).
I
need to populate those listboxes depending on the value of the
ContactID
textbox of each record in the subform.
Ex.: "ContactID 27 attended the Nashville and Las Vegas shows", then
only those two shows should show up in the listbox.

I'm able to programmatically reference each of ContactID's values by
moving through the recordset, but I'm unable to assign its value to a
particular record listbox -- I'd kinda need to reach
"lstShowAttended(i)"...

I'v tried binding the listbox by setting it's rowsource property a
Query :
"SELECT ContactShow.ShowID, Shows.ShowDescription
FROM Shows INNER JOIN detailsShow ON Shows.ShowID = detailsShow.ShowID
WHERE detailsShow.ContactID =
[forms]![frmQuery]![sfrmQuery]![txtContactID]"

If I try to "hard-input" the value of ContactID, the listboxes get
populated with the Shows attended by the ContactID I enter :
"SELECT detailsShow.ShowID, Shows.ShowDescription
FROM Shows INNER JOIN detailsShow ON Shows.ShowID = detailsShow.ShowID
WHERE detailsShow.ContactID = 22"

In the above query, which is the ROWSOURCE property of the Shows
listbox, I entered ContactID 22. After opening the form, all the
listboxes on the subform contain "Las Vegas", which is the show
ContactID 22 attended.

Thus, if anyone has experienced this sort of problem, I'd really
appreciate any input. I can go more in details, with code and such, if
needed.

Thanks in advance to anyone who helps,
Simon.

 

[Gerald Stanley]

Unbound controls on continuous forms/subforms do not behave
as you are expecting. The best way of thinking of them is
as multiple instances of the same object. So, if you set
the RowSource of one instance, then all instances get the
same RowSource.

Hope This Helps
Gerald Stanley MCSD

 

[Rick Brandt]

Goodday! I've posted this request on the database group, but maybe
it's better suited in here.

I have the following tables : CONTACTS (ContactID, FirstName,
LastName, Company, etc.), SHOWS (ShowID, ShowDescription) and
CONTACTSHOW (links the previous tables together so not to have a
many-to-many relationship -- has the ContactID and ShowID fields).

I have a main form with a couple of listboxes which are used for
querying the CONTACTS table. The results populate bound textboxes of a
CONTINUOUS subform. This is fairly simple and works just fine. The
problem starts with an UNBOUND ShowAttended listbox on the SUBFORM
(which appears on each record shows, just as the bound textboxes do).
I
need to populate those listboxes depending on the value of the
ContactID
textbox of each record in the subform.
Ex.: "ContactID 27 attended the Nashville and Las Vegas shows", then
only those two shows should show up in the listbox.

I'm able to programmatically reference each of ContactID's values by
moving through the recordset, but I'm unable to assign its value to a
particular record listbox -- I'd kinda need to reach
"lstShowAttended(i)"...

I'v tried binding the listbox by setting it's rowsource property a
Query :
"SELECT ContactShow.ShowID, Shows.ShowDescription
FROM Shows INNER JOIN detailsShow ON Shows.ShowID = detailsShow.ShowID
WHERE detailsShow.ContactID =
[forms]![frmQuery]![sfrmQuery]![txtContactID]"

If I try to "hard-input" the value of ContactID, the listboxes get
populated with the Shows attended by the ContactID I enter :
"SELECT detailsShow.ShowID, Shows.ShowDescription
FROM Shows INNER JOIN detailsShow ON Shows.ShowID = detailsShow.ShowID
WHERE detailsShow.ContactID = 22"

In the above query, which is the ROWSOURCE property of the Shows
listbox, I entered ContactID 22. After opening the form, all the
listboxes on the subform contain "Las Vegas", which is the show
ContactID 22 attended.

Thus, if anyone has experienced this sort of problem, I'd really
appreciate any input. I can go more in details, with code and such, if
needed.

In a continuous form you have ONLY ONE ListBox. It is merely being drawn
multiple times for each displayed record. All instances will always share
the same properties and will therefore show the same on ALL rows displayed.

The only way controls like this can vary "per record" is if they are bound
to data in the RecordSet or at least use an expression based on data from
the RecordSet.

 

[Simon P]

Thank you Gerald and Rick.
You both comfirmed what I thought. Then is it possible to base the
subform's recordsource on a query that would include the ShowID field
so as to bind the listbox on this field?
If so what would be that query and the listbox's rowsource query?

Goodday! I've posted this request on the database group, but maybe
it's better suited in here.

I have the following tables : CONTACTS (ContactID, FirstName,
LastName, Company, etc.), SHOWS (ShowID, ShowDescription) and
CONTACTSHOW (links the previous tables together so not to have a
many-to-many relationship -- has the ContactID and ShowID fields).

I have a main form with a couple of listboxes which are used for
querying the CONTACTS table. The results populate bound textboxes of a
CONTINUOUS subform. This is fairly simple and works just fine. The
problem starts with an UNBOUND ShowAttended listbox on the SUBFORM
(which appears on each record shows, just as the bound textboxes do).
I
need to populate those listboxes depending on the value of the
ContactID
textbox of each record in the subform.
Ex.: "ContactID 27 attended the Nashville and Las Vegas shows", then
only those two shows should show up in the listbox.

I'm able to programmatically reference each of ContactID's values by
moving through the recordset, but I'm unable to assign its value to a
particular record listbox -- I'd kinda need to reach
"lstShowAttended(i)"...

I'v tried binding the listbox by setting it's rowsource property a
Query :
"SELECT ContactShow.ShowID, Shows.ShowDescription
FROM Shows INNER JOIN detailsShow ON Shows.ShowID = detailsShow.ShowID
WHERE detailsShow.ContactID =
[forms]![frmQuery]![sfrmQuery]![txtContactID]"

If I try to "hard-input" the value of ContactID, the listboxes get
populated with the Shows attended by the ContactID I enter :
"SELECT detailsShow.ShowID, Shows.ShowDescription
FROM Shows INNER JOIN detailsShow ON Shows.ShowID = detailsShow.ShowID
WHERE detailsShow.ContactID = 22"

In the above query, which is the ROWSOURCE property of the Shows
listbox, I entered ContactID 22. After opening the form, all the
listboxes on the subform contain "Las Vegas", which is the show
ContactID 22 attended.

Thus, if anyone has experienced this sort of problem, I'd really
appreciate any input. I can go more in details, with code and such, if
needed.

In a continuous form you have ONLY ONE ListBox. It is merely being drawn
multiple times for each displayed record. All instances will always share
the same properties and will therefore show the same on ALL rows displayed.

The only way controls like this can vary "per record" is if they are bound
to data in the RecordSet or at least use an expression based on data from
the RecordSet.