Results 1 to 2 of 2

Thread: Search Form

  1. #1
    Join Date
    Jun 2003
    Location
    UK
    Posts
    15

    Unanswered: Search Form

    Hello,

    I have a piece of code that searches for an ID number I type into a txt box and matches it against the ID number in a table and displays the results on my form.
    This works fine, however, in my table I have another field called Fault_Closed this has a value of 0 or 1 (0 = Fault open, 1 = Fault Closed). When I perform a search I want it to return only the open (0) faults, and display a message saying “This fault is closed, Please try again” if the fault is closed (1).

    Is this possible?

    Thanks in advance.

    Steve

    Code on Search Button:

    Private Sub Command36_Click()
    Dim strFaultRef As String
    Dim strSearch As String

    'Check txtSearch for Null value or Nill Entry first.

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

    'Performs the search using value entered into txtSearch
    'and evaluates this against values in fault_id

    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("fault_id")
    DoCmd.FindRecord Me!txtSearch
    FAULT_ID.SetFocus
    strFaultRef = FAULT_ID.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text

    'If matching record found sets focus in strFaultID and shows msgbox
    'and clears search control

    If strFaultRef = strSearch Then
    MsgBox "Match Found For: " & strSearch, , "Congratulations!"
    FAULT_ID.SetFocus
    txtSearch = ""

    'If value not found sets focus back to txtSearch and shows msgbox
    Else
    MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
    , "Invalid Search Criterion!"
    txtSearch.SetFocus
    End If
    End Sub

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Search Form

    Originally posted by STEVE123456789
    Hello,

    I have a piece of code that searches for an ID number I type into a txt box and matches it against the ID number in a table and displays the results on my form.
    This works fine, however, in my table I have another field called Fault_Closed this has a value of 0 or 1 (0 = Fault open, 1 = Fault Closed). When I perform a search I want it to return only the open (0) faults, and display a message saying “This fault is closed, Please try again” if the fault is closed (1).

    Is this possible?

    Thanks in advance.

    Steve

    Code on Search Button:

    Private Sub Command36_Click()
    Dim strFaultRef As String
    Dim strSearch As String

    'Check txtSearch for Null value or Nill Entry first.

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

    'Performs the search using value entered into txtSearch
    'and evaluates this against values in fault_id

    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("fault_id")
    DoCmd.FindRecord Me!txtSearch
    FAULT_ID.SetFocus
    strFaultRef = FAULT_ID.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text

    'If matching record found sets focus in strFaultID and shows msgbox
    'and clears search control

    If strFaultRef = strSearch Then
    MsgBox "Match Found For: " & strSearch, , "Congratulations!"
    FAULT_ID.SetFocus
    txtSearch = ""

    'If value not found sets focus back to txtSearch and shows msgbox
    Else
    MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
    , "Invalid Search Criterion!"
    txtSearch.SetFocus
    End If
    End Sub
    I always like to work with recordsets when I am searching for things.
    I am using DAO for this.

    Dim rs as recordset, strSQL as string

    'Using your validation for the textbox and assuming FaultID is a number:

    strSQL = "FAULT_ID = " & txtSearch

    'Fault_ID as text:

    strSQL = "FAULT_ID = '" & txtSearch & "'"

    Set rs = Me.RecordsetClone
    rs.MoveLast
    rs.MoveFirst

    (I don't think you have to use the MoveLast and MoveFirst methods with FindFirst but its just habit for me and doesn't hurt anything)

    rs.FindFirst strSQL

    If rs.NoMatch Then
    ---Use your messages here because nothing was found.
    Else

    If rs.Fields("Fault_Closed") = 0 Then
    Me.Bookmark = rs.Bookmark
    ---Do Something with the info.
    Else
    ---This means it is 1 so do something else.
    End If
    End If

    This is one way. I hope it is not too much of a deviation from your original plan to be of help.

    Good luck.

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •