Solved: Listbox reference to unbound column

[ibgreat]

Hello All,

I have two list boxes lstClientAgencies and lstSupports. The second's WHERE statement used info from the the first's. It references the third column from lstAgency. I am not sure why SQL in the ROWSOURCE is not working.

Here it is:

SELECT tblClientSupportPeople_D.ClientSupportPersonID,
tblClientSupportPeople_D.PersonID_Client,
tblClientSupportPeople_D.AgencyID, tblClientSupportPeople_D.[PersonID-Agency],
tblPerson_D.LastName, tblPerson_D.FirstName,
tblClientSupportPeople_D.Active

FROM tblPerson_D
INNER JOIN tblClientSupportPeople_D
ON tblPerson_D.PersonID = tblClientSupportPeople_D.[PersonID-Agency]

WHERE (((tblClientSupportPeople_D.PersonID_Client)=
[Forms]![frmPerson].[txtPersonID]) AND
((tblClientSupportPeople_D.AgencyID)=Forms!frmPers on.lstClientAgencies.Column(2)) AND
((tblClientSupportPeople_D.Active)=True));

You can see the reference:
Forms!frmPerson.lstClientAgencies.Column(2)

I posted this on another forum and have already worked through a number of possible issue. I haven't had any luck though. If you want to view the other post it is at:

http://forums.aspfree.com/microsoft-access-help-18/reference-the-unbound-column-242256.html

[CreganTur]

I'm pretty sure that this is because the .Column() property is not a valid property for SQL.

One way you can work around this is by setting the rowsource via VBA, instead of using your SQL statement as the rowsource for the combobox's rowsource on the property sheet. As long as the combobox values are numeric, then you can just use regular quotation marks. But, if the values are String data type, then you'll need to also use single quotes to declare in SQL that you're working with a string. Try this (You'll need to add in underscores in the correct places to link the string together):

lstSupport.RowSource = "

SELECT tblClientSupportPeople_D.ClientSupportPersonID,
tblClientSupportPeople_D.PersonID_Client,
tblClientSupportPeople_D.AgencyID, tblClientSupportPeople_D.[PersonID-Agency],
tblPerson_D.LastName, tblPerson_D.FirstName,
tblClientSupportPeople_D.Active


FROM tblPerson_D
INNER JOIN tblClientSupportPeople_D
ON tblPerson_D.PersonID = tblClientSupportPeople_D.[PersonID-Agency]


WHERE (((tblClientSupportPeople_D.PersonID_Client)=
" & [Forms]![frmPerson].[txtPersonID] & ") And
((tblClientSupportPeople_D.AgencyID)=" & Forms!frmPers on.lstClientAgencies.Column(2) & ") And

((tblClientSupportPeople_D.Active)=True));"

[ibgreat]

I'm pretty sure that this is because the .Column() property is not a valid property for SQL.


Doh, that makes perfect sense.

Thanks!!!