Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

Search form based on option group option

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
palmtreeinfotech
Asked:
palmtreeinfotech
  • 4
  • 4
  • 3
  • +1
4 Solutions
 
mbizupCommented:
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
 
clarkscottCommented:
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
 
clarkscottCommented:
PS.  my example uses a 'search form' prior to opening the data form.
Scott C
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Bill RossCommented:
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
 
clarkscottCommented:
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
 
palmtreeinfotechAuthor Commented:
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
 
palmtreeinfotechAuthor Commented:
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
 
Bill RossCommented:
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
 
mbizupCommented:
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
 
mbizupCommented:
Sorry -

With wildcards, that should have been:

"WHERE YourNameField LIKE *" & chr(34) & me.YourTextbox & chr(34) & "*"
0
 
mbizupCommented:
Need more coffee.  Like this:

"WHERE YourNameField LIKE " & chr(34) & "*" & me.YourTextbox &  & "*" & chr(34)
0
 
palmtreeinfotechAuthor Commented:
That is totally awesome and thanks again.  I will split the points up.  Again, I appreciate all your answers!
Chris
0
 
palmtreeinfotechAuthor Commented:
I learned something today.  Thanks everyone.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now