Filling in subform from combobox on page of main form

[Richard1941]

I have a main form that has 3 pages. On the third page I want to retrive a list of records related to a combobox on that page, but not related to anything on the main form. First I tried to code the AfterUpdate action for the combobox with:

strSQL = "SELECT qryBusinessesByCategory.Category, qryBusinessesByCategory.FirmName, qryBusinessesByCategory.Address1, qryBusinessesByCategory.City, qryBusinessesByCategory.Email, qryBusinessesByCategory.ContactPerson, qryBusinessesByCategory.Phone, qryBusinessesByCategory.Website " & _
"FROM qryBusinessesByCategory WHERE (((qryBusinessesByCategory.Category)=" & "'" & Me!Category & "'))"
Me!subfrmBusinessesByCategory.Form!Category.RowSourceType = "Table/Query"
Me!subfrmBusinessesByCategory.Form!Category.[Record Source]
​

But that produced an error saying that control doesn't support the RowSource parameter.
So then I tried putting in the AfterUpdate action:

[TempVars]![BusinessCategory] = Me![Category].Value
​

and using for the RecordSource for the subform:

SELECT qryBusinessesByCategory.Category, qryBusinessesByCategory.FirmName, qryBusinessesByCategory.Address1, qryBusinessesByCategory.City, qryBusinessesByCategory.Email, qryBusinessesByCategory.ContactPerson, qryBusinessesByCategory.Phone, qryBusinessesByCategory.Website FROM qryBusinessesByCategory WHERE (((qryBusinessesByCategory.Category)=[TempVars]![BusinessCategory]));
​

No error messages, but no data in the subform either. How do I get the subform to populate after the control on the main form has had a selection?

 

[Richard1941]

Let me pose the question differently. How can I get a subform to open only when I do it from the VB code?

 

[nanscombe]

I would try ...

Private Sub yourComboBox_AfterUpdate()
  Dim strSql as String

  strSQL = "SELECT qryBusinessesByCategory.Category, qryBusinessesByCategory.FirmName, qryBusinessesByCategory.Address1, qryBusinessesByCategory.City, qryBusinessesByCategory.Email, qryBusinessesByCategory.ContactPerson, qryBusinessesByCategory.Phone, qryBusinessesByCategory.Website " & _
  "FROM qryBusinessesByCategory WHERE (((qryBusinessesByCategory.Category)=" & "'" & Me!Category & "'))"

  Me!subfrmBusinessesByCategory.Form.RecordSource = strSql
End Sub