Solved

Search form based on option group option

Posted on 2011-09-27
13
324 Views
Last Modified: 2012-05-12
Hi.  I'm not a newbie to Access but not sure how to do this.  I have one unbound textbox called 'text1" in the control.  I want to search by multiple things based on the answer in an option group.  For instance, the one search field and say 3 option group options based on a table or query.  The 3 option groups are "search by ID", "search by name" or "search by city."

I want Access to be able to search the right field and pull up the correct results based on what the user has chosen.  I think I saw this someplace in a sample Access database but I can't find it on the web anywhere.  If no options are chosen, I want all records.  If the first option is chosen of "search by ID" and they type in "12" in the textbox, I want everything with "12" to come up in the search results.

Thanks experts!
0
Comment
Question by:palmtreeinfotech
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 36708406
In it's most basic form, you would need something like this in the click event of a button (this is assuming that everything is on the same form and that you are using your option group and textbox to filter the records):

Select Case Me.YourOptionGroupName
        Case Null
                Me.Filter = ""
                Me.FilterOn = False
       Case 1
               Me.Filter = "[YourIDField] = " & me.YourTextboxName
                Me.FilterOn = True
       Case 2
               Me.Filter = "[YourNameField] = " & chr(34) & me.YourTextboxName & chr(34)
                Me.FilterOn = True
       Case 3
               Me.Filter = "[YourCityField] = " & chr(34) & me.YourTextboxName & chr(34)
                Me.FilterOn = True
End Select

Open in new window


That should handle the basic logic for the searches.  You should also include data validation checks and error handling to make a solution for this complete.
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 125 total points
ID: 36708420
You are going to have to code this.
dim crit as string
if not isnull(me.TextBox) then
      IF me.OptionGroup = 1 then
               crit = "SearchById = " & me.testbox
      elseif me.optiongroup = 2 then
               crit = "SecondField = " & me.textbox
      end if
   end if
docmd.openform "YourForm",,,crit
(the ,,, crit,, is in the WHERE position of the docmd)

You might want to put quotes around the   ...   & "'" & me.textbox & "'"     if the field is text.

Scott C
   
         
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 36708429
PS.  my example uses a 'search form' prior to opening the data form.
Scott C
0
 
LVL 14

Assisted Solution

by:Bill Ross
Bill Ross earned 125 total points
ID: 36708441
Hi,

Follow these steps:
1. Create the option group with 4 buttons.  Let's name it ogFind.  Make the default value 0.  Set each button with Option Value of:
   0 - All records
   1 - Search by ID
   2 - Search By Name
   3 - Search by City
2.  text1 is the text box to hold the criteria.
3.  Put the following code in the after update of the text box:

Private Sub ogFind_AfterUpdate()
    Dim vSQL
    Select Case ogFind
    Case Is = 0
        vSQL = "SELECT * FROM myTable;"
    Case Is = 1
        vSQL = "SELECT * FROM myTable where ID=" & Text1 & ";"
    Case Is = 2
        vSQL = "SELECT * FROM myTable where name='" & Text1 & "';"
    Case Is = 3
        vSQL = "SELECT * FROM myTable where city'" & Text1 & "';"
    End Select
    Me.RecordSource = vSQL
   
End Sub

How it works.  User enters criteria and clicks oFind.  Form displays data based on criteria.

Hope this helps.

Bill
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 36708508
Also... I generally use 'search forms' to filter data forms.  I put all the unbound textboxes, combos, etc. for fields I may want to search.  Then in code:

dim crit as string
if not isnull(me.txtbox1) then
        crit = "Field1 = " & me.txtbox1
end if
if not isnull(me.txtbox2) then
        if crit <> "" then crit = crit & " and "
        crit = crit & "Field2 = " & chr(34) & me.txtbox2 & chr(34)  '  --- chr(34) = "
end if
'--- and so on

docmd.openform YourForm,,, crit

Scott C
0
 

Author Comment

by:palmtreeinfotech
ID: 36708583
Thank you for all the great responses.  I have tried a couple of these and BillDenver's seems to work for me.  I followed your instructions Bill.  Now do I have to put this in the query criteria line:

Like "*" & [Forms]![Form1]![text1] & "*" for it to work?  I can't seem to get it to work exactly right as of yet.

On the "onclick" of the button I am running Query1 then I guess it's looking at the after update?

Thank you mbizup and clarkscott for the suggestions.  I will try those as well just so I understand everything.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:palmtreeinfotech
ID: 36708629
I think I may have it now.  I put the LIKE statement in the "OR" instead of the "AND" criteria and it seems to be working now.  Thanks guys for all your help!
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 36708648
Hi,

If your form is named Form1 then what is the recordsource?  If it's a table then the code will work but the user would need to type in the City exactly.  You could add a Like expression to each of the SQL statements like this:

vSQL = "SELECT * FROM myTable where city LIKE '*" & Text1 & "*';"

Noting the code above:  If the City is say London then the * goes inside the single quote on both sides and the statement converts to:

... city LIKE '*London*';

If you set a break you can evalueate the vSQL
OOPS.  I jus tnoticed I left out the = sign in the code above.  Should be:

"SELECT * FROM myTable where city='" & Text1 & "';"

Regards,

Bill
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36708669
Palmtreeinfotech,

Just a note regarding your name field...

Using  CHR(34)'s (double-quotes) as delimiters for your text fields instead of single quotes serves the purpose of preventing errors if you have any Irish names such as O'Brien (or other data that contains single quotes):

>>  & chr(34) & me.YourTextboxName & chr(34)

Such data will cause problems if you delimit it with single quotes.

If you are using Wildcards in your SQL, you can use the CHR(34)'s like this:

"WHERE YourNameField LIKE " & chr(34) & me.YourTextbox & chr(34)


0
 
LVL 61

Expert Comment

by:mbizup
ID: 36708675
Sorry -

With wildcards, that should have been:

"WHERE YourNameField LIKE *" & chr(34) & me.YourTextbox & chr(34) & "*"
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 36708679
Need more coffee.  Like this:

"WHERE YourNameField LIKE " & chr(34) & "*" & me.YourTextbox &  & "*" & chr(34)
0
 

Author Comment

by:palmtreeinfotech
ID: 36708717
That is totally awesome and thanks again.  I will split the points up.  Again, I appreciate all your answers!
Chris
0
 

Author Closing Comment

by:palmtreeinfotech
ID: 36708726
I learned something today.  Thanks everyone.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now