Search Button

[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.

 

[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