query across all fields with one text box

[sdimaggio]

I have a form with an underlying query with many fields, I am trying to figure out a way to either requery or filter across all the fields as a search tool.

I built a text box to enter the text and have it fire on an after update event.


I am using the following code.

Set qdf = CurrentDb.QueryDefs("qry_ApprovalProfile&quot
qdf.SQL = strSQL
qdf.Close

me.requery
me.refresh

This should update the query and refresh the form view of the underlying data, but it does not work. where am I going wrong?

thanks

steve

 

[tunsarod]

For the sake of experiment I just used the following code successfully. For what it's worth it works by resetting the SQL for the query "JunctionList" so its resulting output is dependent on the random value of randVal.


Sub Reset_QueryDef()
Dim qdf As QueryDef, strSQL As String, dbs As Database, qry As String
Dim randVal As Integer

qry = "JunctionList"

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(qry)

randVal = Int((2 * Rnd) + 1) ' Generate random value between 1 and 2.

If randVal = 1 Then
qdf.SQL = "SELECT Junctions.Junction, Junctions.Northing As FirstField, Junctions.Westing As SecondField FROM Junctions;"
Else
qdf.SQL = "SELECT Junctions.Junction, Junctions.Mpart As FirstField, Junctions.Jpart As SecondField FROM Junctions;"
End If

qdf.Close

End Sub


I also created a form with three text boxes and set the textbox control source to the three query fields, "Junctions", "FirstField" and "SecondField". In the form's Open Event the "Reset_Query" procedure is called and the form opens with whichever data set is determined by "Reset_Query".

Using the FirstField, SecondField alias names avoids having to reset the ControlSource value for the textboxes every time the SQL changes but in a practical situation you might also need to change the text box titles according to which SQL statement is used.

I hope this helps.

 

[sdimaggio]

Thanks for reponding. The first part of your answer is where I want to end up. (I think?) But I am trying to incorporate one text box which is good for all the fields of the query.

I have a form called "frm_ApprovalProfile" which is based off of "qry_ApprovalProfile" it has 50,000 records.
"qry_ApprovalProfile" has the fields: [Name], [SalesRep], [street], etc.

I created a text box call [Searchbox]in the frm_ApprovalProfile and when I type the text "Smith" in the [Searchbox], I am trying to scan all the fields in "qry_ApprovalProfile" which are like "Smith".

For example suppose I typed the name "Smith" in the [searchbox]. I would want anything with the name Smith in it to appear whether it was in the field [name] or [salesrep], etc.

I don't really follow your random value example.

could you elaborate?

thanks

steve

 

[JeremyNYC]

Steve,

Are you simply trying to filter the records on the form according to a value put into your text box? If so, it's a lot simpler than all this.

In the after update event of the text box, just put this

if len(nz(me!YourTextBox)) > 0 then
me.filter = "FieldRelatedToThisControl = '" & me!YourTextBox & "'"
me.filteron = true
else
me.filteron = false
end if

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[tunsarod]

Forget about the use of the random number thing. That was just to get the sub routine to select one of two possibilities. It's not important.

Now I've read your replies my suggestion is that you run a query which copmbines all the fields your serch string might appear in and then search the composite string like this:


SELECT [Field1] & [Field2] & [Field3] & [Field4] AS Expr1
FROM YourTable
WHERE ((([Field1] & [Field2] & [Field3] & [Field4]) Like "*" & Forms!YourForm!SearchBox & "*&quot);


With a textbox called SearchBox on a form called YourForm the SQL will list any records with a value in any field matching the textbox value.