Results 1 to 14 of 14

Thread: sql search

  1. #1
    Join Date
    May 2004
    Posts
    86

    Unanswered: sql search

    I have a form with three fields, lastname,CFR_EHPID, and CFR_PATOFFSET. now i have two text boxes that i want to search by, Text5(where user types in EHPID) and text169(where user types in Offset) once the user fills in the two text boxes i want to go to that record based on the data that was filled in. i have a sample code im using that i placed in my search button but i am getting setfocus errors. my table is CFR

    any help appreciated... am i missing code???


    Private Sub Command103_Click()
    Dim sSQL

    sSQL = "SELECT * FROM CFR WHERE CFR_EHPID = '" & Text5.text & "' AND CFR_PATOFFSET = '" & Text169.text & "';"
    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So uh... that's not the actual sub right?

    sSQL hasn't been defined, and you never did anything with your string once you built it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Posts
    86

    ok well im very new to this

    could i have some tips or what cod eim missing i got this far but i dont know what else to add.... thanks

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here's one way to do it:

    Code:
    Private Sub Command103_Click()
       Dim sSQL as String
       Dim rstForm as Recordset
       SET rstForm = me.recordsetclone
       rstform.findfirst "CFR_EHPID = '" & Text5.text & "' AND CFR_PATOFFSET  
          ='"     & Text169.text & "'"
       If rstForm.NoMatch then
         'no records were found meeting that criteria
       else
          Me.BookMark = rstForm.Bookmark
       End If
       rstform.close
       set rstform = nothing
    End Sub
    This code was written on the fly and hasn't been tested, but it should get you pointed in the right direction
    Last edited by RedNeckGeek; 02-23-05 at 10:28.
    Inspiration Through Fermentation

  5. #5
    Join Date
    May 2004
    Posts
    86

    ok cool one last ?

    how can i make a message box appear if test5 and text 169 are blank? Like please fill the required fields.....I dont know where i would put this code.


    Private Sub Command103_Click()
    Dim sSQL as String
    Dim rstForm as Recordset
    SET rstForm = me.recordsetclone
    rstform.findfirst "CFR_EHPID = '" & Text5.text & "' AND CFR_PATOFFSET
    ='" & Text169.text & "'"
    If rstForm.NoMatch then
    MsgBox "Match Not Found For: " & EHPID & " - Please Try Again.", _
    , "Invalid Search!"
    Text5.SetFocus
    Text5 = ""
    Text169 = ""
    else
    Me.BookMark = rstForm.Bookmark
    End If
    rstform.close
    set rstform = nothing

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Try throwing this in the beginning of your code:

    Code:
    If ISNULL(Me!test5)
       Msgbox ("Put a value in", vbokonly)
       me!test5.setfocus
       Exit sub
    End if
    Inspiration Through Fermentation

  7. #7
    Join Date
    May 2004
    Posts
    86

    ok well

    yeah that works but when you click the command button it says match not found first. I need it to display a mesbox letting the user know to fill in the field then when no records are found display the no records found message?
    basically when the button is pressed its going to the norecord match first instead of the please neter a EHPID message box? thanks

    Private Sub Command103_Click()
    Dim sSQL As String
    Dim rstForm As Recordset
    Dim EHPID As String
    If (Me!Text5) = "" Then
    MsgBox "Please enter a EHP ID", vbOKOnly, "Invalid Search Criterion!"
    Me!Text5.SetFocus
    Exit Sub
    End If
    Text5.SetFocus
    EHPID = Text5.text
    Set rstForm = Me.RecordsetClone
    rstForm.FindFirst "CFR_EHPID = '" & Text5 & "' AND CFR_PATOFFSET ='" & Text169 & "'"
    If rstForm.NoMatch Then
    MsgBox "Match Not Found For: " & EHPID & " - Please Try Again.", _
    , "Invalid Search!"
    Text5.SetFocus
    Text5 = ""
    Text169 = ""
    Else
    MsgBox "History Found For EHP ID#: " & EHPID & ""
    Me.Bookmark = rstForm.Bookmark
    End If

    rstForm.Close
    Set rstForm = Nothing
    End Sub

  8. #8
    Join Date
    May 2004
    Posts
    86

    ok well

    the no matching records box appears before any other messagebox..? i need a message box when the feilds are blank..... Right now its showing me the .nomatch record messagebox before the message box that tells the user the field is blank. Much help needed thanks.....


    Private Sub Command103_Click()
    Dim sSQL As String
    Dim rstForm As Recordset
    Dim EHPID As String
    If (Me!Text5) = "" Then
    MsgBox "Please enter a EHP ID", vbOKOnly, "Invalid Search Criterion!"
    Me!Text5.SetFocus
    Exit Sub
    End If
    Text5.SetFocus
    EHPID = Text5.text
    Set rstForm = Me.RecordsetClone
    rstForm.FindFirst "CFR_EHPID = '" & Text5 & "' AND CFR_PATOFFSET ='" & Text169 & "'"
    If rstForm.NoMatch Then
    MsgBox "Match Not Found For: " & EHPID & " - Please Try Again.", _
    , "Invalid Search!"
    Text5.SetFocus
    Text5 = ""
    Text169 = ""
    Else
    MsgBox "History Found For EHP ID#: " & EHPID & ""
    Me.Bookmark = rstForm.Bookmark
    End If

    rstForm.Close
    Set rstForm = Nothing
    End Sub

  9. #9
    Join Date
    May 2004
    Posts
    86

    one more time

    my no matching records box appears every time. I need a message box to show if the field is blank.... The no matching record box shows all the time????


    Private Sub Command103_Click()
    Dim sSQL As String
    Dim rstForm As Recordset
    Dim EHPID As String
    If (Me!Text5) = "" Then
    MsgBox "Please enter a EHP ID", vbOKOnly, "Invalid Search Criterion!"
    Me!Text5.SetFocus
    Exit Sub
    End If
    Text5.SetFocus
    EHPID = Text5.text
    Set rstForm = Me.RecordsetClone
    rstForm.FindFirst "CFR_EHPID = '" & Text5 & "' AND CFR_PATOFFSET ='" & Text169 & "'"
    If rstForm.NoMatch Then
    MsgBox "Match Not Found For: " & EHPID & " - Please Try Again.", _
    , "Invalid Search!"
    Text5.SetFocus
    Text5 = ""
    Text169 = ""
    Else
    MsgBox "History Found For EHP ID#: " & EHPID & ""
    Me.Bookmark = rstForm.Bookmark
    End If

    rstForm.Close
    Set rstForm = Nothing
    End Sub

  10. #10
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Quote Originally Posted by petenyce105
    I have a form with three fields, lastname,CFR_EHPID, and CFR_PATOFFSET. now i have two text boxes that i want to search by, Text5(where user types in EHPID) and text169(where user types in Offset) once the user fills in the two text boxes i want to go to that record based on the data that was filled in. i have a sample code im using that i placed in my search button but i am getting setfocus errors. my table is CFR

    any help appreciated... am i missing code???


    Private Sub Command103_Click()
    Dim sSQL

    sSQL = "SELECT * FROM CFR WHERE CFR_EHPID = '" & Text5.text & "' AND CFR_PATOFFSET = '" & Text169.text & "';"
    End Sub
    You need to use code along these lines:

    ' Place this code in a global module so it can be used anywhere in your code

    Public Function IsInValidWord(ctlControl As Access.Control) As Boolean
    ' Tests to see if there is any data in the control of interest
    ' Tests for zero length or a null value. Returns True if either is true
    IsInValidWord = (Len(ctlControl.Value) = 0 Or IsNull(ctlControl.Value))
    End Function

    Private Sub cmdFindRecords_Click()
    Dim strSQL As String
    If IsInValidWord(Me("txtCFR_PATOFFSET")) Then
    MsgBox "You must enter an CFR_PATOFFSET value", vbInformation, "No CFR_PATOFFSET Value"
    Me("txtCFR_PATOFFSET").SetFocus
    Exit Sub
    End If

    If IsInValidWord(Me("txtEPHID")) Then
    MsgBox "You must enter an EPHID value", vbInformation, "No EPHID Value"
    Me("txtEPHID").SetFocus
    Exit Sub
    End If
    strSQL="SELECT * FROM CFR WHERE CFR_EHPID = '" & Me("txtEPHID").Value & "' AND CFR_PATOFFSET = '" & Me("txtCFR_PATOFFSET").Value & "'"
    Me("sybMuSubForm").Form.RecordSource = strSQL
    End sub

    Regards
    Justin

  11. #11
    Join Date
    Apr 2003
    Posts
    280
    Where does the data goes after the query has been executed?

    Here is my code that goes to a listbox when there has been a change in the text box.

    *This code is for the FacilityID text box.*
    Private Sub txtFacilityID_Change()

    lstScanData.RowSource = "SELECT [MinUnitData].[FacilityID], [MinUnitData].[PatientID] FROM MinUnitData Where PatientID Like '" & txtPatientID & "*' And FacilityID Like '" & txtFacilityID.Text & "*'"

    End Sub

    *This code is for the PatientID text box*
    Private Sub txtPatientID_Change()

    lstScanData.RowSource = "SELECT [MinUnitData].[FacilityID], [MinUnitData].[PatientID] FROM MinUnitData Where PatientID Like '" & txtPatientID.Text & "*' And FacilityID Like '" & txtFacilityID & "*'"

    End Sub

    What these two code does is that everytime you enter a data into the textbox, the listbox will change in real time. But the listbox has to be connected to the table as well, showing all of the records. Hope you understand the code.

  12. #12
    Join Date
    May 2004
    Posts
    86

    almost their

    Ok i tried added the sql code you provided that dosent work for me. Im trying to alert my suer if they forgot to enter a vaule in text5 and text 169. right now with my code that works all it says is that no matching record was found how can i incorporate a message box to alert the user???
    i think their is a problem with the no.match property???
    thanks


    Private Sub Command103_Click()
    Dim sSQL as String
    Dim rstForm as Recordset
    SET rstForm = me.recordsetclone
    rstform.findfirst "CFR_EHPID = '" & Text5.text & "' AND CFR_PATOFFSET
    ='" & Text169.text & "'"
    If rstForm.NoMatch then
    MsgBox "Match Not Found For: " & EHPID & " - Please Try Again.", _
    , "Invalid Search!"
    Text5.SetFocus
    Text5 = ""
    Text169 = ""
    else
    Me.BookMark = rstForm.Bookmark
    End If
    rstform.close
    set rstform = nothing

  13. #13
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I still think you want to make sure those to fields are populated properly
    before you attempt to find the record. Apparently, the field isn't null,
    but is possibly a zero length string. Let's try to account for either case with one command:
    Code:
    If LEN(NZ(text5,""))=0 THEN
       MSGBOX("BLAH", VBOKONLY)
       TEXT5.SETFOCUS
        EXIT SUB
    END IF
    You'll need to do the same thing for text169.

    You need to perform both tests before attempting the findfirst.
    Inspiration Through Fermentation

  14. #14
    Join Date
    May 2004
    Posts
    86

    All done

    thanks everyhting works

Posting Permissions

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