Me.Filter having no effect.

[94Sport5sp]

Hi:

I have a form bound to a Crosstab Query. The form has a list box from which the user can select the items to be displayed. With code I then set the filter to this selected item. However, the form displays all records returned by the Query. I have used Debug.Print me.filter to verify that the filter is correct and now I am stuck.

Some background info:

The purpose of the form is to display summary information. The list box is tied to a different query which presents a list of areas to the user. The Crosstab Query does a summary of all areas. Because of the nature of the Crosstab Query my form does not have any fields bound to the Crosstab Query. Instead I have several unbound fields and then I use code to set the Control Source of each Unbound field to a field from the Crosstab Query. All of that works fine and my form displays the data properly. However, what I now want is to limit the display to the user selected area but my filter is having no effect.

Any thoughts.

My Crosstab Query:

TRANSFORM Sum(qryGLVendor.NetAmt) AS SumOfNetAmt
SELECT qryGLVendor.SaleDate, qryGLVendor.RegisterName
FROM qryGLVendor
GROUP BY qryGLVendor.SaleDate, qryGLVendor.RegisterName
PIVOT qryGLVendor.Item ;

My code to set the filter:

Me.Form.Filter = "RegisterName=" & """" & RegisterNameGlbl & """"

Debug.Print Me.Filter


My code to locate the open form and assign Control Source to the unbound fields:

Private Sub subFillCol()

Dim frm As Form, intI As Integer
Dim intJ As Integer
Dim vAmount As Variant
Dim intControls As Integer, intForms As Integer

Dim rst As DAO.Recordset
Dim fld As Field
Dim intCnt As Integer

Set rst = Me.Recordset

intForms = Forms.Count ' Number of open forms.
'Check if any forms are open
If intForms > 0 Then
' Loop thru all open forms looking for frmGLSum
For intI = 0 To intForms - 1

Set frm = Forms(intI)
Select Case frm.Name
'Populate form from query
Case "frmGLSum"
intCnt = 1
For Each fld In rst.Fields ' Print field names.

Select Case VarType(fld.Value)
Case 0 To 1 'Value is a Null or empty
'Debug.Print " Fld 01 "; intCnt; " Name "; fld.Name; " "; fld.Value
Me("Hdn" & intCnt) = fld.Name
Me("Col" & intCnt).ControlSource = fld.Name '""

More similar code to populate unbound fields.

End Select
intCnt = intCnt + 1
Next
intCnt = 1

End Select
Next intI
Else
MsgBox "No open forms.", vbExclamation, "Form Controls"
'End check for any forms open
End If



End Sub

 

[RainLover]

Why isn't the filter part of the query.

qryGLVendor I think is the right one.

 

[94Sport5sp]

Why isn't the filter part of the query.

qryGLVendor I think is the right one.

As in define a criteria in the query? Well the criteria needs to change based on the selection the user makes from the list box.

Thanks

 

[RainLover]

So is there a problem doing that.

If so there is a Wizard in the Query's Criteria field.

Just right click in the criteria box. This will open the Wizard. Click on Build.

You should end up with something like Forms![frmName]![ControlName]

Untested

 

[94Sport5sp]

So is there a problem doing that.

Ok, I am missing something in my understanding of your solution or my level of understanding Access does not match your level of understanding.. I understand the creating the criteria part but I do not understand how the form information is dynamically passed back to a query that the query that runs my form is based on.

My form uses as a source a Query called qryGLSum which is the Crosstab Query. That query is based on query called qryGLVendor and in the qryGLVendor I put in the criteria linking it to the list box (lbArea) in my form frmGLSum. Now when the user selects and item from lbArea the qryGLVendor refreshes which updates the qryGLSum. The user and then reselect from lbArea and a new list displays. At least that is what I believe you are telling me will happen.

Thanks

 

[RainLover]

I think I am wrong here.

Is the only form you have frmGLSum. If so you cant use a criteria from that Form to create a Query as the record sourse as the form is not yet open.

This would be a case of which comes first the Chicken or the egg. Form or Query.

I assumed you were using the crosstab somewhere differently.

Can you reconfirm this.

 

[RainLover]

As a matter of interest. Is Access your first use of a Database or did you learn a different program first.

 

[Galaxiom]

Me.FilterOn = True

http://office.microsoft.com/en-au/access-help/filteron-property-HA001232737.aspx

 

[RainLover]

94Sport5sp

Did Galaxiom's advise help. He is usually pretty good at this kind of stuff.

 

[94Sport5sp]

94Sport5sp

Did Galaxiom's advise help. He is usually pretty good at this kind of stuff.

Hi Rain:

Thanks for sticking with me. As to your questions:


Although there are other forms that I have designed for this application, frmGLSum is the only form that would be in effect at this time.

The frmGLSum which is the Crosstab Query is not used or connected to any other form.

So just to be clear the Crosstab Query is NOT being used in or connected to any other form.

As for programming: Access and VBA are not my first, however, it has been many years since I last wrote a programm in any language. So, yes rust is a major factor and I can use all the help I can get.

Thanks for your thoughts

Hi Galaxiom:

Yes I have done that as well.

Thanks for the thought.

 

[RainLover]

In your Code for the filter you go searching for other Forms.

What does it matter if another Form is open.

I totally miss this bit.

Why is not the form Bound to the Source Query. This would take away he need to populate the controls on the Form.

Your general approach is something I have never seen before.

Can you explain your reasoning for doing things this way. Just in simple English.

 

[94Sport5sp]

Why is not the form Bound to the Source Query. This would take away he need to populate the controls on the Form.

Your general approach is something I have never seen before.

Can you explain your reasoning for doing things this way. Just in simple English.

It is due to the nature of the Crosstab feature. The Crosstab uses your data for column heading (for Access column heading is a field name) which means that he field names change.

In your Code for the filter you go searching for other Forms.

What does it matter if another Form is open.

I totally miss this bit.

For me, I am not concerned if any other forms are open I just need to find my form for the reason stated above.

Your general approach is something I have never seen before.

Actually I cannot take credit for it. I found a sample on the Net where to individual was using that method to populate a report. I simply adapted it to work with forms.

It is a neat way to handle the changing field names produced by the Crosstab.

Now I just need to find a means of filtering or doing a dynamic where clause based on the user selection.

Thanks

 

[RainLover]

To be honest this is getting more difficult than I may be able to handle.

If your code works and your only problem is the filter this may help.

First load your form but do not run the code that you have. So this way you open a blank form.

Set up one of the queries, I don't know which one but you should, the one that filters the records based upon the contents of the Combo Box.

This query should have the criteria of YourForm.YourComboBox.

On the "After Update" event of the "Combo Box" run the code that loads the various fields.

I think my theory is correct so give it a go.

Do you realise that this form will not be updateable.
Also with a bit of code you can standardise the Column headings.
Normally one would be doing this with a report not a form.
Finally I would say that your table design is incorrect which is forcing you to use a cross tab.

 

[94Sport5sp]

To be honest this is getting more difficult than I may be able to handle.

By asking me to explain you help me check my logic. So, thanks .

If your code works and your only problem is the filter this may help.

First load your form but do not run the code that you have. So this way you open a blank form.

Set up one of the queries, I don't know which one but you should, the one that filters the records based upon the contents of the Combo Box.

This query should have the criteria of YourForm.YourComboBox.

On the "After Update" event of the "Combo Box" run the code that loads the various fields.

I think my theory is correct so give it a go.

I will try that and let you know the results later.

Do you realise that this form will not be updateable.

Updateable how? If you mean that the user cannot change the data from this form then that is ok because this is a summary for display only.

Also with a bit of code you can standardise the Column headings.
Normally one would be doing this with a report not a form.

Standardise the Column headings how? Care to elaborate? I not too old to learn.

As mentioned earlier, this code was from a person who had to apply it to a report and since my user has no intention of printing this report, I do not see the advantage of using a report.

Finally I would say that your table design is incorrect which is forcing you to use a cross tab.

The table collects daily sales data by area. What the user want is a total of sales by item sold within an area. Without using cross tab how else can it be done. Again, I am not to old to learn.

Thanks for your time.

 

[RainLover]

Standardise the Column headings how? Care to elaborate? I not too old to learn.

I did this once and once only. It had to do with reporting over a monthly period. I had it set up to show the Date as the column header. Similar to what you want but not exactly the same.

To find out how to do this you would be better off starting a new thread. I don't feel I am experienced enough to help you properly.

I was only able to do it with the help of others from a different Forum.

The table collects daily sales data by area. What the user want is a total of sales by item sold within an area. Without using cross tab how else can it be done. Again, I am not to old to learn.

I could not say too much about this without seeing your Relationships. If you could post a pic I could comment more.

 

[94Sport5sp]

I did this once and once only. It had to do with reporting over a monthly period. I had it set up to show the Date as the column header. Similar to what you want but not exactly the same.

To find out how to do this you would be better off starting a new thread. I don't feel I am experienced enough to help you properly.

I was only able to do it with the help of others from a different Forum.

Ok, but I am not hopeful since working with dates it is easier to create standard heading. You are limited by date grouping. Since I am working with item names not so easy.

I could not say too much about this without seeing your Relationships. If you could post a pic I could comment more.

Actually what I have done is stripped this down to a bare minimum. I am working with one table which really has only 5 fields.

SaleDate (date of sale), RegisterName (area doing the selling), ItemName( Item sold), SalesQty (Quantity sold today), SalesAmount (Total value for this quantity of item).

I know I could create a relations between RegisterName and a Registertbl table and with ItemName and a Itemtbl but it would do nothing to help with the problem.

The problem really revolves around the cross tab feature and that ItemNames become field names.

Thanks for your time.

 

[94Sport5sp]

Hi:

HOORAY! HOORAY! HOORAY!

I have a working solution to this problem.

I figured if the filter would not work then lets try a different approach. I knew from a different post about QueryDef as a means to change a stored query. So I changed my form to use QueryDef to open my query, locate a WHERE clause, change the WHERE clause to match what the user selected, and then save the query.

This worked fine for changing the query to get the records I wanted but I had to close the form to get to the updated records. (Me.refresh, Me.requery, and qdf.execute all had no effect execpt to cause errors.) After doing some more searching and reading I found this post http://www.access-programmers.co.uk/forums/showthread.php?p=1091968#post1091920 from Michael Lueck of Lueck Data Systems and now the form behaves like I want.

User selects and area, I QueryDef the selection into a the underlying query, I use Michael's work around fix and bingo I get the display I want.

Thank you, Thank you all for your help.

It works, It works.

 

[RainLover]

Great news. I was starting to think we would never get there. The fact that others did not jump in is usually a sign that they don't know either.

But you got there so this is good.

My only advise at this stage is to TEST then TEST and TEST again. If there is a fault you need to find it now.

Late thought. I have not see any Error Handling in your code. This is a must.

If you don't have something do a search. Use something that is not too complicated and make sure you understand it.

 

[94Sport5sp]

Great news. I was starting to think we would never get there. The fact that others did not jump in is usually a sign that they don't know either.

But you got there so this is good.

Sometimes the best part of coding is to find a really tough situation which seems to have no solution and then to solve it. I have been on this problem, off and on, for several weeks and it feels good to solve it even if the original question still remains unanswered.

Thanks for trying to help.

My only advise at this stage is to TEST then TEST and TEST again. If there is a fault you need to find it now.

One of the challenges in coding is to anticipate all the unknown situations the user will take the program to. So yes THINK like a user and TEST your code.

Error handling and checking for takes place before the routine is called.

Thanks again