Search Button (1 Viewer)

piano_playr

Member
Local time
Today, 10:53
Joined
Oct 31, 2014
Messages
30
I think you should switch the filter on and off?, you are relying with that code that filteron = True ? --Gasman
I agree with Gasman. You're trying to do too much with the filter properties of the form. You don't need to set any property other than Me.Filter. If you mess around with Me.FilterOn, Me.FilterOff, Me.FilterOnLoad, Me.Requery, it will only lead to trouble. Follow the example I posted previously and you'll be fine.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:53
Joined
Sep 21, 2011
Messages
10,853
I agree with Gasman. You're trying to do too much with the filter properties of the form. You don't need to set any property other than Me.Filter. If you mess around with Me.FilterOn, Me.FilterOff, Me.FilterOnLoad, Me.Requery, it will only lead to trouble. Follow the example I posted previously and you'll be fine.
No I was saying completely the opposite.
I assume that changing the filter while filteron is true will refresh the filter.?
However if filteron is false, then the filter will not work?
I have always issued the two commands together but that is me?
 

piano_playr

Member
Local time
Today, 10:53
Joined
Oct 31, 2014
Messages
30
No I was saying completely the opposite.
Sorry, Gasman for misunderstanding your post. I can relate to you my experience trying to reproduce the OP's issue. When I used his code, which included Me.FilterOff, Me.Filter = <criteria>, and Me.FilterOn, I got parameter prompts all over the place. None of the prompts were appropriate. It worked when there was only one property being set, i.e. Me.Filter = <criteria.>. Of course, the OP's criteria statement was improperly formed. He needs to address that. Once that is squared away, I believe all that is necessary is Me. Filter = <criteria>. I cannot give you a Microsoft engineer's explanation for what I experienced. I can tell you about the result of my unit testing.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:53
Joined
Feb 19, 2002
Messages
36,869
@Gasman You are trying to correct a post that we have no idea has anything at all to do with the problem at hand. @MatthewB is the person who started the thread. @piano_playr offered some alternative but isn't trying to solve Matthew's problem
 

MatthewB

Member
Local time
Today, 10:53
Joined
Mar 30, 2022
Messages
73
Here is my db . I am trying to search the Quoting form accessed through the A Main Menu
 

Attachments

  • MyTestDatabase.accdb
    3.9 MB · Views: 54

CarlettoFed

Member
Local time
Today, 19:53
Joined
Jun 10, 2020
Messages
51
The solution can be seen in the attachment however the structure of a database is not built like you did.
 

Attachments

  • MyTestDatabase.zip
    513.9 KB · Views: 21

Isaac

Lifelong Learner
Local time
Today, 10:53
Joined
Mar 14, 2017
Messages
6,678
Just one man's opinion: I have found it to be much simpler, and much fewer "moving parts" to memorize the interplay between, to simply code search buttons as:

Me.Recordset = "select * from mainformsavedquery where columnname='" & me.txtStringValue.Value & "'"
Me.Requery

then a reset button to reset to "all", which is the main query underlying the form, one which you are always OK with that user seeing.

MUUUUCH simpler than the 15 components you have to remember for all this filtering stuff.

I have almost never used a form Filter and in this case ignorance was bliss, and worked well
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:53
Joined
Sep 21, 2011
Messages
10,853
I was under the impression that if you change the recordset source, it is automatically requeried, no need to issue a requery?
 

Isaac

Lifelong Learner
Local time
Today, 10:53
Joined
Mar 14, 2017
Messages
6,678
I was under the impression that if you change the recordset source, it is automatically requeried, no need to issue a requery?
Deja vu, I feel like we've had this conversation before.
You could be right - or we both could be, as I think I've tried it without the requery and witnessed a less-than-ideal result.
I have no scientific or intelligent basis, just what I have done.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:53
Joined
Sep 21, 2011
Messages
10,853
Sorry, my memory is so bad these days, plus I know I have mentioned on other forums.
 

Isaac

Lifelong Learner
Local time
Today, 10:53
Joined
Mar 14, 2017
Messages
6,678
Sorry, my memory is so bad these days, plus I know I have mentioned on other forums.

I use Access very little these days, so soon I will be in the same boat!
I'm fine with that (as the gists of Access haven't changed tremendously over the years), as long as I don't become like a certain person whose name rhymes with ink, not a real developer but just copies and pastes answers from other forums. Seriously, ban me if that ever happens.
 

piano_playr

Member
Local time
Today, 10:53
Joined
Oct 31, 2014
Messages
30
Here is my db . I am trying to search the Quoting form accessed through the A Main Menu

I am coding a search button on a form based on a Query. If I want my search to return on the value of one of the table fields does the code need to refer to the Query or the table?

The on click code
Me.SearchText.SetFocus
Me.FilterOn = False
Me.Filter = " [Calendar_Strata_1Q].[StrataPlanNr] like '% " & SearchText & "%' "
Me.FilterOn = True
Me.Requery

The StrataPlanNr originates in the StrataPlan_T. The Query references Calendar_T and StrataPlan_T
SearchText is an unbound text box.
Matthew,

I would like to help you with your issue. I took a look at the sample Access file you posted today. I would like to confirm a few things.

I believe we are working with the following objects:

Form: Calendar List_F_C
Record Source: Calendar_Strata_1Q

I need more information to properly debug. I notice one of the fields on the querydef (Calendar_Strata_1Q) was removed. The field was: Strataplan_t.SearchItem. You posted the SQL of the querydef last Saturday. After you posted the sample database today, Tuesday, the field was removed. Why did you remove that field?

I am not sure I am looking at the form where you are having problems. You said you are trying to search the Quoting Form (Quoting_Detail F_C) from the main menu. That form does not match the screenshot of the form you submitted today. I looked at all the forms and it appears the name of the form in the screenshot is Calendar_Strata_1Q. Is that the form in question? What form has the issues?

I could not find the VBA code you posted on Friday. What object, on what form has this code behind it? Is this object a button? What is the name of the button? You said the VBA code is attached to an "on click" event. Unfortunately, I can't find the VBA code you referenced on Friday in today's Access file. Where is this VBA code? Did you remove it?

Would you please confirm/answer my questions? Your answers will help the debugging process. Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:53
Joined
Feb 19, 2002
Messages
36,869
This thread is completely off the rails.

@MatthewB
I created a query using the SQL you posted back in #12. It ran fine for me so I was correct. The Querydef was corrupted. I don't know where that leaves you since the thread has gone sideways.

However, you have several severe design issues with the tables.
1. Some relationships are pathological. For example, you have StrataPlan_ID as a FK in Quoting but you have Quoting_ID in StrataPlan. You CANNOT have a 1-m relationship and have the m-side key in the 1-side table. Not possible.
2. You also have a number of repeating groups. Look at every column that has a numeric suffix. Those fields belong in a separate table. It doesn't matter if there's only four of them. You've made a hard limit where none should exist, plus, you've complicated all processing.
3. You are not using the Autonumber PK in the FK relationships. You are using some other field. The ONLY reason to ever have an autonumber in a table is because you are going to use it as the primary key. NEVER, include an autonumber in a table if you are going to use some other field as the PK. I know it "feels" easier to you to use StrataPlanNr as the FK and it is not actually wrong. I would not use any field that I do not control as the PK. I've been burned too many times by this. Someone changes the format of StrataPlanNr and you have to change the value of your PK. If you don't have RI enforced and you don't have Cascade Update specified, this is a nightmare. Besides, no PK should ever be allowed to change, hence the reliance on surrogate keys like the autonumber. StrataPlanNr becomes data. You can still use a unique index to ensure the business rule of uniqueness is followed. You can still search on it. You can still show it on every form where it is relevant. Users never have to see an autonumber. The only person who is inconvenienced is the developer who sometimes wants to just look at raw data in tables while testing and it is always annoying when that data is a meaningless autonumber rather than a meaningful StrataPlanNr. So, the answer is to use queries so you can join to the StrataPlan table. If you are going to use some other field as the PK, get rid of the autonumbers before they cause a problem. There is a lurking bug which may or may not be fixed that causes the autonumber seed to go wonky in tables where the autonumber is not being used as the PK.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 10:53
Joined
Mar 14, 2017
Messages
6,678
@MatthewB
I've attached a working copy of the database with an addition that accomplishes precisely what you described in your original post.
This is very simple - if you follow my advice about using recordsource instead of Filters!

1. I saved a query (same as your sql that was underlying the quoting form), called qry_QuotingForm_Main
2. I coded this behind the Search button:

Code:
Private Sub SearchQuotingBtn_Click()
Me.RecordSource = "select * from qry_QuotingForm_Main where [StrataPlanNr] like '*" & Me.SearchQuotingTxt.Value & "*'"
Me.Requery
End Sub

Works like a charm. HTH
 

Attachments

  • MyTestDatabase.accdb
    4.3 MB · Views: 15

Users who are viewing this thread

Top Bottom