[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Creating a Search Field

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
dgi001
Asked:
dgi001
  • 3
  • 2
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
if the [Fieldname] is Number Data Type, use this

me.filter="[Fieldname] = " & me.txtSearch
me.filteron=true
0
 
Rey Obrero (Capricorn1)Commented:
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
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!

 
peter57rCommented:
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
 
dgi001Author Commented:
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
 
peter57rCommented:
Me.Filter = "[SIMPhoneNO] = '" & Me.txtSearch & "' And [SIMStatus] = 'Active' "

If you still get a problem you must check the datatype of SimStatus.
0
 
dgi001Author Commented:
Hi Guys

That worked great.

Thank you

Kind regards
DGI
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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