SELECT statement assistance

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

Hello. I have main form that has a subform on it. Within
the subform is another small subform that is a continuous
form. The small subform has a button associated with the
continuous line that will show a pop up list of items to
select from to fill the continuous form. However,the
listbox on the pop up form is not populating with the
information I need it to populate with based on a textbox
on the subform. I have the following select and it is in
the where part that is throwing me off. This is in the
Form Load of the pop up list.
The info needed for assistance is;
main form = usrfrmCarriers
subform = usrfrmCarriersLineOfBusiness
textbox = LineOfBusiness
small subform in subform = usrfrmCarriersCoverageType
textbox = CoverageTpe

The select statement is as follows;
SELECT
CoverageType
CoverageTypeItem
FROM tblCoverageType
WHERE CoverageType = ????

I have tried many combinations but cannot get the pop up
list to populate.

Does anyone have thoughts on the WHERE statement that is
throwing this off?

Thanks in advance to anyone who helps.
*** John
 
Could you use the LinkMasterFields/LinkChildFields properties of the subform
control instead of using the WHERE clause of the subform's RowSource?

If that is not suitable, you can theoretically set the subform's RowSource
to something like this:
SELECT ... FROM ... WHERE CoverageType =
[Forms]![MySub].[Form]![SomeControl];
However, Access can be unstable when trying to resolve these references.

The more stable alternative is to use the parent form's Current event to
assign a string to the subform's RowSource:

strSQL = "SELECT ... FROM ... WHERE CoverageType = " & Nz(Me.CoverageType,
0) & ";"
Me.MySub.Form.RecordSource = strSQL
 
Back
Top