Setting RowSource from Sub Form

[Edgar Thoemmes]

I have a sub form which I am trying to use to create a SQL statement which I
can apply to the main forms rowsource. How do I apply strSQL to the Main
Forms rowsource and then refresh main form?

Private Sub CmdFilter_Click()
Dim strSQL As String
Dim strSuppStatus As Long

If Me.fraSuppStatus.Value = 1 Then
strSuppStatus = 1
ElseIf Me.fraSuppStatus.Value = 2 Then
strSuppStatus = 2
Else
strSuppStatus = ""
End If

Debug.Print strSuppStatus

strSQL = "SELECT tblSuppliers.SupplierID, tblSuppliers.SupplierName,
tblSuppliers.Address1, tblSuppliers.Address2, tblSuppliers.City,
tblSuppliers.CountyID, tblSuppliers.Postcode, tblSuppliers.ContactName,
tblSuppliers.TelephoneNumber, tblSuppliers.FaxNumber,
tblSuppliers.EmailAddress, tblSuppliers.MobilePhone, tblSuppliers.Notes,
tblSuppliers.StatusID, tblSuppliers.CommodityID FROM tblSuppliers WHERE
tblSuppliers.StatusID=" & strSuppStatus & ";"

Forms!frmsuppliermenu.RowSource = strSQL

End Sub

 

[Marshall Barton]

I have a sub form which I am trying to use to create a SQL statement which I
can apply to the main forms rowsource. How do I apply strSQL to the Main
Forms rowsource and then refresh main form?

[snip]

Forms do not have a RowSource property, that's for List and
combo boxes. You should use the RecordSource property.

Setting the RecordSource automatically requeries the form so
you don't need to do anything extra to refresh the form.