Solved: Setting rowsource for a subform from a listbox

[ibgreat]

Alright here's the deal,

I have:

frmPerson (MainForm)
conAgencyInfo (Control on Main Form)
lstEmployment (listbox on Main Form - that show all of the person's current employers)
fsubAgencyInfo (Sub Form - shows the selected employers contact info)

I am trying to set the fsubAgencyInfo.RowSoure to a sql statement that is based on lstEmployment.Column(3). I wasn't having problems when the sql statement was based on lstEmployments bound column, but now I need to change the bound column back to the PK so other things can reference it.

The problem I am having is figuring out how to pass the rowsource to the subform. I have attempted a number of different syntaxes (some I even know wouldn't work) without any luck. Here are a few and the associated error messages (obviously these were run seperately).

Forms!fsubAgencyInfo.RowSource = strSQL ' ERROR can't find the form fsubAgencyInfo

Me!fsubAgencyInfo.RowSource = strSQL ' ERROR: can't find the field fsubAgencyInfo

Me!fsubAgencyInfo.Form.RowSource = strSQL 'ERROR: can't find the field fsubAgencyInfo

Me.conAgencyInfo.Form.RowSource = strSQL

Me.conAgencyInfo.Form!fsubAgencyInfo.RowSource = strSQL 'ERROR: can't find the field fsubAgencyInfo

The only other potentially viable option that I could see is to somehow set the parent relationship to the needed column in the listbox. But based on what I am seeing, Access doesn't seem to allow this.

I have been working at this for several days. And am very frustrated as this would seem to be straightforward:motz2: . I have posted elsewhere without any luck. You guys (inclusive of gals) have always come through....Please Help!!

[Norie]

You don't actually seem to refer to any control on the subform.:huh:

[ibgreat]

Your correct I am not referencing a control on the subform. I am trying to set the Rowsource of the subform (fsubAgencyInfo) to a sql statement built in vba. What I don't understand is why I get an error that Access can't find the form when I try to set the Rowsource.

Forms!fsubAgencyInfo.Rowsource = strSQL

To clarify I have

frmPerson
conAgencyInfo (the control on the Mainform that contains the subform)
fsubAgencyInfo (the form within the control on the mainform)

[Norie]

Well the last time I looked a form/sub form didn't have a RowSource property.:huh:

[ibgreat]

Norie, I appreciate your help and again, you are correct....well kinda.

The control of the subform (on the main form) does not contain a rowsource property. But the form ("sub"form) within that control does. It is the form within the control that I trying to set the rowsource for. You can do this on the form's properties, like any other form. If you look at my initial post I differentiated this. Here it is again, to make life simple.

I have:

frmPerson (Main Form)
conAgencyInfo (this is the control on the Main Form or what is often referred to as the subform)
fsubAgencyInfo (is the actual form that is within the control conAgencyInfo)

lstEmployment (is the list box on frmPerson that helps assemble the query for fsubAgency.)

I hope that makes more sense.

[Norie]

Sorry but you've completely lost me.

A form doesn't have a RowSource property.:)

[Austin]

See if this helps

http://www.mvps.org/access/forms/frm0031.htm

[ibgreat]

Sorry but you've completely lost me.

A form doesn't have a RowSource property.:)

Again, your right. My apologies. I was incorrectly referencing rowsource. Should have been referencing recordsource. I have to play with it and I will post an update.

Norie, thank you (and everyone else). I'll post an update when I play with it more.

[ibgreat]

Yep, that wa all it was a silly error in choosing the wrong property. I greatly appreciated everyone's help and walking through this. Even as I was giving bad info.

You guys (inclusive of gals) are wonderful. Thanks again.


The correct line was:

Me.conAgencyInfo.Form.RecordSource = strSQL