filter form on subform

[Ellen Leonard via AccessMonster.com]

I am trying to filter a form by info in a subform.

I have read http://allenbrowne.com/ser-28.html but am missing something.

My form is based on a query which pulls records based on Employee

Main Form
Customer ID (PK)
Customer
Employee

SubForm
StatusID (PK)
CustomerID (FK)
Status

I added an unbound combo box to the main form (cbostatus)
Name cbostatus
Control Source = blank
RowSource = tblstatus

in the after_update I placed the following

Private Sub cbostatus_AfterUpdate()
On Error GoTo Err_cbostatus_AfterUpdate
Dim strSQL As String

' Purpose: Change the form's RecordSource
Dim sSQL As String
Dim bWasFilterOn As Boolean

' Save the FilterOn state. (It's lost during RecordSource change.)
bWasFilterOn = Me.FilterOn

' Change the RecordSource.
If IsNull(Me.cbostatus) Then
If Me.RecordSource <> "qry MT Form MS" Then
Me.RecordSource = "qry MT Form MS"
End If
Else
strSQL = "SELECT DISTINCTROW [qry MT Form MS].* FROM [qry MT Form MS]" &
_
"INNER JOIN tblStatus ON " & _
"[qry MT Form MS],CustomerID = tblStatus.CustomerID " & _
"WHERE tblStatus.client_status = " & Me.cbostatus & ";"
Me.RecordSource = strSQL
End If

Exit_cbostatus_AfterUpdate:
Exit Sub

Err_cbostatus_AfterUpdate:
MsgBox "Ooops" 'Err.Number & ": " & Err.Description, vbInformation, & _
' Me.Module.Name & ".cbostatus_AfterUpdate"
Resume Exit_cbostatus_AfterUpdate

End Sub


What am I missing?

Any and all help with be greatly appreciated.

I originally tried to change the query to be based on Employee and Status but
then the form is not editable which is why I am trying to go for the filter.

 

[Brian Bastl]

Ellen,

If you copy and pasted the code, you've got one glaring error in your SQL;
you've got a comma where a period should be:

"[qry MT Form MS],CustomerID = tblStatus.CustomerID " & _
should be
"[qry MT Form MS].CustomerID = tblStatus.CustomerID " & _

Also, if tblStatus.client_status is a numeric data type then you can modify
the where clause to:
"WHERE tblStatus.client_status = " & Me.cbostatus

otherwise if it is alphanumeric, then you need something like:
"WHERE tblStatus.client_status = '" & Me.cbostatus & "'"

HTH,
Brian

 

[Ellen Leonard via AccessMonster.com]

Thank you so much... so simply and yet so hard to see.

Ellen,

If you copy and pasted the code, you've got one glaring error in your SQL;
you've got a comma where a period should be:

"[qry MT Form MS],CustomerID = tblStatus.CustomerID " & _
should be
"[qry MT Form MS].CustomerID = tblStatus.CustomerID " & _

Also, if tblStatus.client_status is a numeric data type then you can modify
the where clause to:
"WHERE tblStatus.client_status = " & Me.cbostatus

otherwise if it is alphanumeric, then you need something like:
"WHERE tblStatus.client_status = '" & Me.cbostatus & "'"

HTH,
Brian

I am trying to filter a form by info in a subform.

[quoted text clipped - 60 lines]

I originally tried to change the query to be based on Employee and Status but
then the form is not editable which is why I am trying to go for the filter.