Solved

Search form based on option group option

Posted on 2011-09-27
13
356 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

756 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