• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • 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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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