By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,106 Members | 2,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,106 IT Pros & Developers. It's quick & easy.

Unbound listbox in continuous subform

P: n/a
Hello group, I'm in desperate need of help. Here goes :

I have the following tables : CONTACTS (ContactID, FirstName,
LastName, Company, etc.), SHOWS (ShowID, ShowDescription) and
SHOWDETAILS (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 fields of a
continuous subform. This is fairly simple and works just fine. The
problem starts with an UNBOUND ShowAttended listbox on the SUBFORM. I
need to populate this listbox 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 using the subform's RecordSource
property set to a Query on the CONTACTS and SHOWS tables, I'm still
not able to reference the ContactID of each record.

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.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Simon,

Just to make sure I understand this correctly:
1) You have a subform that shows contacts
2) On that form you have a list that you want to show all the shows this
contact has been to

There are many ways to do this, but let's have the recordsource of the
listbox reference the contactid on the subform. Basically, you'll create a
query for your listbox data. Let's say you want to return the ShowID and
the ShowDescription in the list box. The source could be:

SELECT showdetails.ShowID, showdetails.ShowDescription FROM showdetails JOIN
shows on showdetails.showid = showdescription.showid WHERE
showdescription.contactid = forms![frmFormName]![contactid]

Then, when the query is run, it'll check the value of contactid on the form,
and return only the relevant data.

If this isn't clear, please let me know. :o)

Best,

--
Jeremy Shapiro
Asandia, Corp.
www.asandia.com
1.866.ASANDIA (272.6342)
"Simon P" <po*****@aei.ca> wrote in message
news:ff**************************@posting.google.c om...
Hello group, I'm in desperate need of help. Here goes :

I have the following tables : CONTACTS (ContactID, FirstName,
LastName, Company, etc.), SHOWS (ShowID, ShowDescription) and
SHOWDETAILS (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 fields of a
continuous subform. This is fairly simple and works just fine. The
problem starts with an UNBOUND ShowAttended listbox on the SUBFORM. I
need to populate this listbox 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 using the subform's RecordSource
property set to a Query on the CONTACTS and SHOWS tables, I'm still
not able to reference the ContactID of each record.

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.

Nov 13 '05 #2

P: n/a
Hi Jeremy, thanks for your response.
I had already tried what you're suggesting, as it seemed the most
obvious thing to do. But since my subform is a CONTINUOUS form, I
can't seem to reference the ContactID textbox that appears for each
record.
I want to emphasize that there can be 300 records on the subform, thus
300 Show listboxes and 300 ContactID textboxes.

The correct ROWSOURCE for the Shows listbox would be :
"SELECT detailsShow.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.

I don't know if I made myself clear enough.
But anyway, thank you!

Simon.

"Jeremy B. Shapiro" <je***************@asandia.com> wrote in message news:<tc********************@comcast.com>...
Simon,

Just to make sure I understand this correctly:
1) You have a subform that shows contacts
2) On that form you have a list that you want to show all the shows this
contact has been to

There are many ways to do this, but let's have the recordsource of the
listbox reference the contactid on the subform. Basically, you'll create a
query for your listbox data. Let's say you want to return the ShowID and
the ShowDescription in the list box. The source could be:

SELECT showdetails.ShowID, showdetails.ShowDescription FROM showdetails JOIN
shows on showdetails.showid = showdescription.showid WHERE
showdescription.contactid = forms![frmFormName]![contactid]

Then, when the query is run, it'll check the value of contactid on the form,
and return only the relevant data.

If this isn't clear, please let me know. :o)

Best,

--
Jeremy Shapiro
Asandia, Corp.
www.asandia.com
1.866.ASANDIA (272.6342)
"Simon P" <po*****@aei.ca> wrote in message
news:ff**************************@posting.google.c om...
Hello group, I'm in desperate need of help. Here goes :

I have the following tables : CONTACTS (ContactID, FirstName,
LastName, Company, etc.), SHOWS (ShowID, ShowDescription) and
SHOWDETAILS (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 fields of a
continuous subform. This is fairly simple and works just fine. The
problem starts with an UNBOUND ShowAttended listbox on the SUBFORM. I
need to populate this listbox 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 using the subform's RecordSource
property set to a Query on the CONTACTS and SHOWS tables, I'm still
not able to reference the ContactID of each record.

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.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.