Solved

Search form based on option group option

Posted on 2011-09-27
13
330 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

914 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

16 Experts available now in Live!

Get 1:1 Help Now