Filter Options

[Don]

I have a form with a subform. In the form, I have a select query as follows:

SELECT tblOrders.OrderID, tblOrders.CustomerId, tblCustomers.CompanyName
FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID =
tblOrders.CustomerId
ORDER BY tblOrders.OrderID;

In the subform (SubfrmOrderLines) with the following query:

SELECT tblPictures.Style, tblPictures.FilePath, tblPictures.FileName,
qryOrderLinesWPics.OrderID, qryOrderLinesWPics.LineID,
qryOrderLinesWPics.FinishItemID, qryOrderLinesWPics.QtyOrderd,
qryOrderLinesWPics.QtyShipped, qryOrderLinesWPics.ProducedInFactory,
qryOrderLinesWPics.ShpFactory, qryOrderLinesWPics.Status,
qryOrderLinesWPics.Month, qryOrderLinesWPics.RequestDate,
[QtyOrderd]<>[QtyShipped] AS ShowIf
FROM qryOrderLinesWPics INNER JOIN tblPictures ON qryOrderLinesWPics.Shoe =
tblPictures.Style
ORDER BY qryOrderLinesWPics.RequestDate;

The OrderID field is the Child - Master linked field.

What I am trying to do is create filter option via an Option Group. I cannot
seem to get the filter to work as I guess I do not know the correct way to
identify the subform's field to insert a filter in it's query. The following
is what I have:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove the filter for the option the user chose.
If FilterOptions = 1 Then
Me.Filter = "qryOrderLinesWithPics.ShowIf = '-1'"
Me.FilterOn = True 'Apply filter.
If FilterOptions = 2 Then
Me.Filter = "qryOrderLinesWithPics.ShowIf = '0'"
Me.FilterOn = True 'Apply filter.
Else
Me.FilterOn = False 'Remove filter.
End If
End If


End Sub

In the Sub form, the query returns a value in the ShowIf field of -1 for
each Line Item of pending orders and 0 for each Line Item if the order's Line
Item is complete. If I could get the code to work there will be a second
problem.

If I filter the records of -1, all of the forms records OrderID, CustomerID
and CustomerName show up for completed orders but in the subform of course,
there are no records. So my second question is how can I skip those records
in the form when I activate the filter to begin with?

 

[J. Goddard]

Hi Don -

Check your code in the filteroptions_after_Update; the code for
filteroptions = 2 will never execute, because it is inside the IF for
filteroptions = 1.

Try this:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove the filter for the option the user chose.
If FilterOptions = 1 Then
Me.Filter = "qryOrderLinesWithPics.ShowIf = '-1'"
Me.FilterOn = True 'Apply filter.
ElseIf FilterOptions = 2 Then
Me.Filter = "qryOrderLinesWithPics.ShowIf = '0'"
Me.FilterOn = True 'Apply filter.
Else
Me.FilterOn = False 'Remove filter.
End If

See the difference?

Hope this helps

John

 

[Don]

Any Ideas how I can get the code to recognize the field I wish to put the
filter in?

I have tried "Me.ShowIf", the subform form name "subfrmOrderLines.ShowIf"
and the name of the query used to retrieve the info in the subform
"qryOrderLinesWithPics.ShowIf". Any Ideas?
--
Thanks,

Dennis

Hi Don -

Check your code in the filteroptions_after_Update; the code for
filteroptions = 2 will never execute, because it is inside the IF for
filteroptions = 1.

Try this:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove the filter for the option the user chose.
If FilterOptions = 1 Then
Me.Filter = "qryOrderLinesWithPics.ShowIf = '-1'"
Me.FilterOn = True 'Apply filter.
ElseIf FilterOptions = 2 Then
Me.Filter = "qryOrderLinesWithPics.ShowIf = '0'"
Me.FilterOn = True 'Apply filter.
Else
Me.FilterOn = False 'Remove filter.
End If

See the difference?

Hope this helps

John

I have a form with a subform. In the form, I have a select query as follows:

SELECT tblOrders.OrderID, tblOrders.CustomerId, tblCustomers.CompanyName
FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID =
tblOrders.CustomerId
ORDER BY tblOrders.OrderID;

In the subform (SubfrmOrderLines) with the following query:

SELECT tblPictures.Style, tblPictures.FilePath, tblPictures.FileName,
qryOrderLinesWPics.OrderID, qryOrderLinesWPics.LineID,
qryOrderLinesWPics.FinishItemID, qryOrderLinesWPics.QtyOrderd,
qryOrderLinesWPics.QtyShipped, qryOrderLinesWPics.ProducedInFactory,
qryOrderLinesWPics.ShpFactory, qryOrderLinesWPics.Status,
qryOrderLinesWPics.Month, qryOrderLinesWPics.RequestDate,
[QtyOrderd]<>[QtyShipped] AS ShowIf
FROM qryOrderLinesWPics INNER JOIN tblPictures ON qryOrderLinesWPics.Shoe =
tblPictures.Style
ORDER BY qryOrderLinesWPics.RequestDate;

The OrderID field is the Child - Master linked field.

What I am trying to do is create filter option via an Option Group. I cannot
seem to get the filter to work as I guess I do not know the correct way to
identify the subform's field to insert a filter in it's query. The following
is what I have:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove the filter for the option the user chose.
If FilterOptions = 1 Then
Me.Filter = "qryOrderLinesWithPics.ShowIf = '-1'"
Me.FilterOn = True 'Apply filter.
If FilterOptions = 2 Then
Me.Filter = "qryOrderLinesWithPics.ShowIf = '0'"
Me.FilterOn = True 'Apply filter.
Else
Me.FilterOn = False 'Remove filter.
End If
End If


End Sub

In the Sub form, the query returns a value in the ShowIf field of -1 for
each Line Item of pending orders and 0 for each Line Item if the order's Line
Item is complete. If I could get the code to work there will be a second
problem.

If I filter the records of -1, all of the forms records OrderID, CustomerID
and CustomerName show up for completed orders but in the subform of course,
there are no records. So my second question is how can I skip those records
in the form when I activate the filter to begin with?

 

[J. Goddard]

where is the filter_options control? If it's on the main form, then the
"Me." in your code refers to the main form, not the subform.
What you need to do is set the filter for the subform. I have not
worked with subforms a great deal, but you might try this:

me![subform control name].form.filter = "Showif = -1"
and
me![subform control name].form.filteron = true

in your after_update code.

Note that I removed the quotes around the -1 - it is numeric and does
not need them.

Hope this helps -

John

(MVP's : you can throw bytes at me if I'm way off base.)

 

[Don]

The control is on the main form and the records to be filtered is on the
subform is called "OrderLines". OrderLines is the name in the property, name
field. The name of the field to be filtered in the subform is "ShowIf".

My code is:

Private Sub FilterOptions_AfterUpdate()

' Apply or remove the filter for the option the user chose.
If FilterOptions = 1 Then
Me.Filter = Me![OrderLines].Form.Filter = "ShowIf = -1"
Me![OrderLines].Form.FilterOn = True 'Apply filter.
ElseIf FilterOptions = 2 Then
Me.Filter = Me![OrderLines].Form.Filter = "ShowIf = 0"
Me![OrderLines].Form.FilterOn = True 'Apply filter.
Else
Me.FilterOn = False 'Remove filter.
End If


End Sub

Evidently I am not nameing the code correctly but do not know what to name
it. When using the filter, the caluclation happens without error but
everything returns from the filter (nothing seems to filter). Any other
suggestions?


--
Thanks,

Dennis

where is the filter_options control? If it's on the main form, then the
"Me." in your code refers to the main form, not the subform.
What you need to do is set the filter for the subform. I have not
worked with subforms a great deal, but you might try this:

me![subform control name].form.filter = "Showif = -1"
and
me![subform control name].form.filteron = true

in your after_update code.

Note that I removed the quotes around the -1 - it is numeric and does
not need them.

Hope this helps -

John

(MVP's : you can throw bytes at me if I'm way off base.)

Any Ideas how I can get the code to recognize the field I wish to put the
filter in?

I have tried "Me.ShowIf", the subform form name "subfrmOrderLines.ShowIf"
and the name of the query used to retrieve the info in the subform
"qryOrderLinesWithPics.ShowIf". Any Ideas?