query left$

On this Area code tracker db I am working on, it puts the filter/query permanently in the forms property sheet. When I try and do another query, either by area code, state, or city it wants the parameter for the last used query. While goggling this I came across many others that had the same problem, but none of the post fixed my problem. As a test to see if it would give me, or someone else, an idea of what’s going on I made 3 copies of the form and changed the names slightly and use them instead of the load form when I did each of the above filter/query. It works, but, of course, that is not the way to program. Anybody have an idea?
 
If you are working with Access why not eliminate the filter and simply adjust your SQL Query as needed. Personally I rarely use any of the stuff that ties my code to tightly to Access in case I want to port that code to something else someday. Further this often has the benefit of not encountering a lot of the pitfalls that seem to plague others that do tie their code more closely to Access.

If I am doing a query I generally set "in code" the Form.Recordsource = sqlQuery where the sqlQuery is a String in which I build my query thus I can change my query and then do a Forms.Requery to change what is being displayed
 
Gasman said:
Don't you need to clear the first filter, and set it to other criteria.?
So set the filter in the load event, and give the user someway of selecting a different filter (combobox?)

Why not just give the form the data with no filter and then allow selection of the filter required?

https://msdn.microsoft.com/en-us/library/office/ff197950.aspx

http://stackoverflow.com/questions/4645971/access-filter-vba
Click to expand...

Thanks for answering!
Yes, that is my problem I can't clear the first filter. It loads with all records. I use four cmd buttons, for four other filters. Area Code, city, State. and All. The form loads with All, but once I use Area Code, City, or State it puts that filter permanently in the propert sheet. Now when I try and use All it wants to use whatever filter was last used.
 
DennisOJensen said:
If you are working with Access why not eliminate the filter and simply adjust your SQL Query as needed. Personally I rarely use any of the stuff that ties my code to tightly to Access in case I want to port that code to something else someday. Further this often has the benefit of not encountering a lot of the pitfalls that seem to plague others that do tie their code more closely to Access.

If I am doing a query I generally set "in code" the Form.Recordsource = sqlQuery where the sqlQuery is a String in which I build my query thus I can change my query and then do a Forms.Requery to change what is being displayed
Click to expand...

Thanks for answering!
That sounds like something I want to try, if for nothing else, at least for the learning experience.
 
Okay well if you need any help with the how I will keep an eye on the thread.
 
DennisOJensen said:
Okay well if you need any help with the how I will keep an eye on the thread.
Click to expand...

Thank You very much. That's the nicest thing anybody has said to me all day, except the flirty waitress, of course.:)

This db as I stated earlier is a "Keep Mamma Happy" db. She is in a Rehab Hospital and it gives her something to do. Also my hobby is Access, although I do, do simple db for other ministries.
 
Well I am all for keeping Mama happy :) as well as teaching others a safer more industrial way of using Access
 
DennisOJensen said:
If you are working with Access why not eliminate the filter and simply adjust your SQL Query as needed. Personally I rarely use any of the stuff that ties my code to tightly to Access in case I want to port that code to something else someday. Further this often has the benefit of not encountering a lot of the pitfalls that seem to plague others that do tie their code more closely to Access.

If I am doing a query I generally set "in code" the Form.Recordsource = sqlQuery where the sqlQuery is a String in which I build my query thus I can change my query and then do a Forms.Requery to change what is being displayed
Click to expand...
BINGO
Code: 
Private Sub Form_Load()
DoCmd.OpenForm "frmAreaCodes"
Me.RecordSource = "qryAlpha"  ' Form.Recordsource = sqlQuery where the sqlQuery is a String                                               ' 
End Sub
[/CODE]
Code: 
Private Sub cmdAll_Click()
DoCmd.OpenForm "frmAreaCodes"
Me.RecordSource = "qryAlpha"
sqlQuery
End Sub
Thanks, that is the only two areas of code I had to change. I will now be able to bring mamma a present tomorrow. That still doesn't explain why the other method wasn't working. I have other db that use the direct apply filter that doesn't give me that problem, but I am putting this "baby" to bed. I have three more db that I am revamping.
 
Gasman said:
Does this help at all?

http://www.access-programmers.co.uk/forums/showthread.php?t=175908

http://www.pcreview.co.uk/threads/vba-code-to-clear-all-filters.3765367/
Click to expand...

Thanks for answering
I just read them quickly and if I understand it correctly the first one is similar to what Dennis suggested. I will read it more carefully later, as it still bugs me that I could not block the filter.
The second one I had come across before and it didn't seem to apply. I had spend untold hours goggling before I posted on AWF. If I find it on my own, it gives me more "Bragging Rights.":eek:
D7A
 
Well as I have found in all things Microsoft and especially the MS Access / MS Excel VBA areas -- it is best to take it to the roots and do not use any of their fancy add-ons (aka read this as anything that makes the code require Access or Excel to use).

As for your particular issue I do tend to take it one step further

Code: 
[FONT="Courier New"]
Dim sqlQuery As String

sqlQuery = ""
sqlQuery = sqlQuery + "SELECT * "
sqlQuery = sqlQuery + "  FROM myTable "
sqlQuery = sqlQuery + " WHERE KeyId = " + SearchValue

Me.Recordsource = sqlQuery 
Me.Requery
[/FONT]

I have found that the more explicit you are with VBA the better it works for you and the easier it is to maintain.

I also do the above to help me more easily read what I have wrote I could have put the Query all on one line but this arrangement is easier to read, and a lot easier to edit if I want to add or change something later
 
From the associated posts it seems that you need to do two things.

Clear the filter (else it will get used when filter is set). Though just replacing it would be nice
Set filteron to false

Dennis has provided another method, but I'm so stubborn, I'd be wanting to find out why it did not work as I thought it should.:banghead:

Maybe I'll have a play on a form tomorrow. :D


Dick7Access said:
Thanks for answering
I just read them quickly and if I understand it correctly the first one is similar to what Dennis suggested. I will read it more carefully later, as it still bugs me that I could not block the filter.
The second one I had come across before and it didn't seem to apply. I had spend untold hours goggling before I posted on AWF. If I find it on my own, it gives me more "Bragging Rights.":eek:
D7A
Click to expand...
 
Yep you can -- beat your head on a wall and figure out how Microsoft implemented it -- or you can be done with that stress and simply do it the clean easier way. One of the problems doing it the Microsoft way is that if they change something -- which they do from time to time -- they could break your code.
 
Gasman said:
From the associated posts it seems that you need to do two things.

Clear the filter (else it will get used when filter is set). Though just replacing it would be nice
Set filteron to false

Dennis has provided another method, but I'm so stubborn, I'd be wanting to find out why it did not work as I thought it should.:banghead:

Maybe I'll have a play on a form tomorrow. :D
Click to expand...
Same here my friend, same here. Time constants, however is what leads me to poor coding.
 
DennisOJensen said:
Yep you can -- beat your head on a wall and figure out how Microsoft implemented it -- or you can be done with that stress and simply do it the clean easier way. One of the problems doing it the Microsoft way is that if they change something -- which they do from time to time -- they could break your code.
Click to expand...
How true. I have a great calendar/planner that I made years ago. It show three months at a time, so what does Bill G do, he drops that calanderXXX so that every time windows OS changes, I have to hunt around and find the fix.
 
Well I just had a quick test and used MSGBOX to see what the filter was.

I noticed that although it cleared the filter as I expected it to, if I went into design view it showed the last filter I had used, however in Forms view, no filter was actually set, and that was confirmed by the number of records shown.

Strange behaviour, but in Gina's post she said she always set the filter to nothing on the unload of a form.
 
Gasman said:
Well I just had a quick test and used MSGBOX to see what the filter was.

I noticed that although it cleared the filter as I expected it to, if I went into design view it showed the last filter I had used, however in Forms view, no filter was actually set, and that was confirmed by the number of records shown.

Strange behaviour, but in Gina's post she said she always set the filter to nothing on the unload of a form.
Click to expand...

When I have time I will try setting filter to "" on unload, although I think I have tried it but can't be sure as I tried so many combo's I don't remember them all.
 
Dick7Access said:
When I have time I will try setting filter to "" on unload, although I think I have tried it but can't be sure as I tried so many combo's I don't remember them all.
Click to expand...

I know where you are coming from, sometimes you can try two or three things at a time to save time, and hey! it works, but you never then know which change actually worked. :D

The good thing with programming is there are always several ways of doing something. People might argue which is the best, but in the end they get the job done.

I remember when I was first using Foxpro for my own use.
I wrote a few lines of code that went trough a database and amended a field in each record. That is how it was done in COBOL which I knew.

A bit like going through a recordset in Access or an update query.

In Foxpro, it's a one liner. :-)
 
Gasman said:
I know where you are coming from, sometimes you can try two or three things at a time to save time, and hey! it works, but you never then know which change actually worked. :D

The good thing with programming is there are always several ways of doing something. People might argue which is the best, but in the end they get the job done.

I remember when I was first using Foxpro for my own use.
I wrote a few lines of code that went trough a database and amended a field in each record. That is how it was done in COBOL which I knew.

A bit like going through a recordset in Access or an update query.

In Foxpro, it's a one liner. :-)
Click to expand...

You are so right. It also depends how you look at things. Some people who look at my main db 13000 records and wonder why we need to front ends. One for me and one for her. When I use it, I mainly want phone number. when she uses it she is doing data entry. The web's that she gets data from list the data in different order, plus there is always the "keep Mamma Happy Function ( )"
BTW: I delivered the finish product this morning and the ""keep Mamma Happy Function ( )"" is working properly. Thanks for all that helped.
Dick
 
You must log in or register to reply here.

Similar threads

N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
714
Minty
Minty
Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
1,512
Gasman
Gasman
S
Recordset.Requery still showing deleted record
Replies
6
Views
516
dalski
D
A
Solved Connection String mysterious Trim
Replies
3
Views
603
cheekybuddha
cheekybuddha
Y
New with a problem
Replies
3
Views
630
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom