Search Button

[piano_playr]

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]

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]

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]

@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]

Here is my db . I am trying to search the Quoting form accessed through the A Main Menu

 

[CarlettoFed]

The solution can be seen in the attachment however the structure of a database is not built like you did.

 

[Isaac]

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]

I was under the impression that if you change the recordset source, it is automatically requeried, no need to issue a requery?

 

[Isaac]

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]

Sorry, my memory is so bad these days, plus I know I have mentioned on other forums.

 

[Isaac]

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]

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]

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.

 

[Isaac]

@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:

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