Sub Form to Unbound main form control

[mattc66 via AccessMonster.com]

So I have a sub form and on the main form I have a text box (control). I have
it set to the Link Child and Master on the subform. It works great except if
I try to put a <0 in the text box on the main form.

I want to be able to filter the subform data and in this case I want to see
all te records that are less then zero based on the user input. Can this be
done?

Matt

 

[Dirk Goldgar]

So I have a sub form and on the main form I have a text box (control). I
have
it set to the Link Child and Master on the subform. It works great except
if
I try to put a <0 in the text box on the main form.

I want to be able to filter the subform data and in this case I want to
see
all te records that are less then zero based on the user input. Can this
be
done?

Not using the Link Master/Child Fields. Instead, leave the Link Master &
Child Fields properties fields blank, and use the AfterUpdate event of the
text box to construct and apply a filter expression to the subform. Your
code might look something like this:

'------ start of example code ------
Private Sub txtFilterExpression_AfterUpdate()

Dim strFilter As String

If Len(Me.txtFilterExpression & vbNullString) > 0 Then
strFilter = _
BuildCriteria(_
"SubformFieldName", _
dbSingle, _
Me.txtFilterExpression)
End If

With Me.sfYourSubform.Form
If Len(strFilter) > 0 Then
.Filter = strFilter
.FilterOn = True
Else
.FilterOn = False
.Filter = vbNullString
End If
End With

End Sub
'------ end of example code ------

Note that filtering this way will *not* automatically insert a parent field
value into a child record if a new record is created, the way the Link
Master and Link Child Fields do. So you can only use this technique
effectively if your subform doesn't allow records to be created, or code on
the subform can identify what parent value might need to be inserted in a
new record.

 

[mattc66 via AccessMonster.com]

I will try this out.

Thank you

So I have a sub form and on the main form I have a text box (control). I
have

[quoted text clipped - 7 lines]

be
done?

Not using the Link Master/Child Fields. Instead, leave the Link Master &
Child Fields properties fields blank, and use the AfterUpdate event of the
text box to construct and apply a filter expression to the subform. Your
code might look something like this:

'------ start of example code ------
Private Sub txtFilterExpression_AfterUpdate()

Dim strFilter As String

If Len(Me.txtFilterExpression & vbNullString) > 0 Then
strFilter = _
BuildCriteria(_
"SubformFieldName", _
dbSingle, _
Me.txtFilterExpression)
End If

With Me.sfYourSubform.Form
If Len(strFilter) > 0 Then
.Filter = strFilter
.FilterOn = True
Else
.FilterOn = False
.Filter = vbNullString
End If
End With

End Sub
'------ end of example code ------

Note that filtering this way will *not* automatically insert a parent field
value into a child record if a new record is created, the way the Link
Master and Link Child Fields do. So you can only use this technique
effectively if your subform doesn't allow records to be created, or code on
the subform can identify what parent value might need to be inserted in a
new record.