Filtering subform

  • Thread starter Thread starter rahmad
  • Start date Start date
R

rahmad

Hi,
I'm creating a form and will insert a subform in it.
My subform have a Date column.I want to make filter
from my main form that will be applied to the date column
of the subform

I have created 2 text boxes,a combo box and two command button.I want the
filter have format 'between <date> and<date>' ,and chose a list from my
combo box,so the subform will display all record between date 1 and date 2
for the list I have choosen/selected from my combo box.

The first command button is used to apply filter and another to remove the
filter.

Would somebody like to help me how to make it work.Cause I need a guidance
in this case.
Thank's
 
Significant fields names from the subform's records source:
Date ---- control name ( Date )
Model -- control name ( Model )

Data type
Date ---- ( medium date )
Model -- ( Text use combo box )

Duane,I would not use the main form for inputting the data.
I just want the main form used for apply filter.Thank's
 
what if I want to filter by monthly.What is the code,Please...

Duane Hookom said:
I was hoping for the names of the two text boxes, combo box, and subform.
Assuming your controls on the main form are txtStart, txtEnd, cboModel, and
sfrmData ...
Create code in the on click event of the command button:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " And [Date]>=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " And [Date]<=#" & Me.txtEnd & "# "
End If
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " And [Model]=""" & Me.cboModel & """ "
End If
Me.sfrmData.Form.Filter = strWhere
Me.sfrmData.Form.FilterOn=True
--
Duane Hookom
Microsoft Access MVP


cho said:
Significant fields names from the subform's records source:
Date ---- control name ( Date )
Model -- control name ( Model )

Data type
Date ---- ( medium date )
Model -- ( Text use combo box )

Duane,I would not use the main form for inputting the data.
I just want the main form used for apply filter.Thank's

want
the date
2 remove
the
Click to expand...
Click to expand...
 
Oh ya,you're right.
And what about the another command button
to remove the filter.What is the code?

Duane Hookom said:
How is monthly different from entering a start and end date?

--
Duane Hookom
Microsoft Access MVP


cho said:
what if I want to filter by monthly.What is the code,Please...

Duane Hookom said:
I was hoping for the names of the two text boxes, combo box, and subform.
Assuming your controls on the main form are txtStart, txtEnd,
Click to expand...
cboModel,
and
sfrmData ...
Create code in the on click event of the command button:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " And [Date]>=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " And [Date]<=#" & Me.txtEnd & "# "
End If
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " And [Model]=""" & Me.cboModel & """ "
End If
Me.sfrmData.Form.Filter = strWhere
Me.sfrmData.Form.FilterOn=True
--
Duane Hookom
Microsoft Access MVP


:


Significant fields names from the subform's records source:
Date ---- control name ( Date )
Model -- control name ( Model )

Data type
Date ---- ( medium date )
Model -- ( Text use combo box )

Duane,I would not use the main form for inputting the data.
I just want the main form used for apply filter.Thank's

Please come back with your control names, significant field names
Click to expand...
from
the
subform's record source, subform control name, and data types of
significant
fields.

--
Duane Hookom
Microsoft Access MVP


:

Hi,
I'm creating a form and will insert a subform in it.
My subform have a Date column.I want to make filter
from my main form that will be applied to the date column
of the subform

I have created 2 text boxes,a combo box and two command button.I want
the
filter have format 'between <date> and<date>' ,and chose a list
Click to expand...
from
my
combo box,so the subform will display all record between date 1
Click to expand...
and
date
2
for the list I have choosen/selected from my combo box.

The first command button is used to apply filter and another to remove
the
filter.

Would somebody like to help me how to make it work.Cause I need a
guidance
in this case.
Thank's
Click to expand...
Click to expand...
Click to expand...
 
Duane,
I think there is something wrong.When I click the command button
VB send error report
" Data Member Not found "
And it highlighted .txtFrom
Any Idea?
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtFrom) Then
strWhere = strWhere & " And [Date]>=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtUntill) Then
strWhere = strWhere & " And [Date]<=#" & Me.txtEnd & "# "
End If
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " And [Model]=""" & Me.cboModel & """ "
End If
Me.sfrmData.Form.Filter = strWhere
Me.sfrmData.Form.FilterOn=True
Click to expand...
Click to expand...
Click to expand...
 
Do you have a control named "txtFrom"? The error "...Not found" suggests
there is no control on your form named "txtFrom".

--
Duane Hookom
Microsoft Access MVP


cho said:
Duane,
I think there is something wrong.When I click the command button
VB send error report
" Data Member Not found "
And it highlighted .txtFrom
Any Idea?
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtFrom) Then
strWhere = strWhere & " And [Date]>=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtUntill) Then
strWhere = strWhere & " And [Date]<=#" & Me.txtEnd & "# "
End If
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " And [Model]=""" & Me.cboModel & """ "
End If
Me.sfrmData.Form.Filter = strWhere
Me.sfrmData.Form.FilterOn=True
Click to expand...
Click to expand...
Click to expand...
 
Yes I have.My text boxes named From and Untill ( your code mentioned Start
and End ).And they are unbound textboxes.

Duane Hookom said:
Do you have a control named "txtFrom"? The error "...Not found" suggests
there is no control on your form named "txtFrom".

--
Duane Hookom
Microsoft Access MVP


cho said:
Duane,
I think there is something wrong.When I click the command button
VB send error report
" Data Member Not found "
And it highlighted .txtFrom
Any Idea?
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtFrom) Then
strWhere = strWhere & " And [Date]>=#" & Me.txtStart & "# "
End If
If Not IsNull(Me.txtUntill) Then
strWhere = strWhere & " And [Date]<=#" & Me.txtEnd & "# "
End If
If Not IsNull(Me.cboModel) Then
strWhere = strWhere & " And [Model]=""" & Me.cboModel & """ "
End If
Me.sfrmData.Form.Filter = strWhere
Me.sfrmData.Form.FilterOn=True
Click to expand...
Click to expand...
Click to expand...
 
Back
Top