Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Creating a Search Field

Posted on 2011-02-24
7
Medium Priority
?
304 Views
Last Modified: 2012-08-13
Hi

I am creating a search field to find records in my database using an unbound text called txtSearch, a command button called cmdSearch, and a the field cboSIMNO as the search field. Pleas see below.

Private Sub cmdSearch_Click()
    Dim strSIMNo As String
    Dim strSearch As String
   

    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search!"
        Me![txtSearch].SetFocus
    Exit Sub
End If
       
    DoCmd.ShowAllRecords
    DoCmd.GoToControl "cboSIMPhoneNo"
    DoCmd.FindRecord Me!txtSearch
       
    Me.cboSIMPhoneNo.SetFocus
    strSIMNo = Me.cboSIMPhoneNo.Text
    Me.txtSearch.SetFocus
    strSearch = Me.txtSearch.Text

I know that i have records in my database with the same details but it will only show the first record. How do i get it to show the other records? I need to use a loop somewhere or find next record but i am not sure how to add it into my code.

kind regards
DGI
0
Comment
Question by:dgi001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 34971743
you may just need to apply a filter to show all the records..


Private Sub cmdSearch_Click()
    Dim strSIMNo As String
    Dim strSearch As String
   

    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search!"
        Me![txtSearch].SetFocus
    Exit Sub
End If

me.filter="[Fieldname] = '" & me.txtSearch & "'"
me.filteron=true

end sub

change "[Fieldname]" with actual name of the field



0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34971756
if the [Fieldname] is Number Data Type, use this

me.filter="[Fieldname] = " & me.txtSearch
me.filteron=true
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34971771
if you want to do a search on partial info entered, use something like this


me.filter="[Fieldname]  like '*" & me.txtSearch & "*'"
me.filteron=true
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 77

Expert Comment

by:peter57r
ID: 34971776
Use the form filter instead.

Private Sub cmdSearch_Click()
    Dim strSIMNo As String
    Dim strSearch As String

    DoCmd.ShowAllRecords

    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search!"
        Me![txtSearch].SetFocus
    Exit Sub
End If
       
      me.filter = "strSIMNo ='" & Me.txtSearch &"'"
      me.filteron = true
end sub
0
 

Author Comment

by:dgi001
ID: 34998374
HI Capricorn1

That worked but i need it to search more than one criteria. i am adding this

Me.Filter = "[SIMPhoneNO] = '" & Me.txtSearch & "'" And "[SIMStatus] = 'Active' "

but i receive an error "Run-time error 13, type mismatch"

Any Ideas?

If you feel i need to open a separate question for this,i will.

Kind regards
Nick
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 1000 total points
ID: 34998901
Me.Filter = "[SIMPhoneNO] = '" & Me.txtSearch & "' And [SIMStatus] = 'Active' "

If you still get a problem you must check the datatype of SimStatus.
0
 

Author Closing Comment

by:dgi001
ID: 35025417
Hi Guys

That worked great.

Thank you

Kind regards
DGI
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

618 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