Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2013
    Posts
    19

    Unanswered: VBA code unable to check for result

    To All,

    I am running the following VBA procedure, which looks at a combo search field to find what field from the database it is going to look at. Then it looks on another field named “txtSearchString” which is the criteria to look for. Ex: cboSearchField = “DeptName” and txtSearchString = “Vault”. This works well until I enter an invalid dept. name or something misspelled (the result shows the form empty). I would like to check if in fact the procedure found something or if there’s nothing, I want to be able to provide a message such as: “No records found” and leave the procedure. Any ideas?

    Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
    MsgBox "You must select a field to search."

    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
    MsgBox "You must enter a search string."

    Else

    'Generate search criteria
    'GCriteria is a global variable used to store search criteria
    GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

    'Filter frmCustomers based on search criteria
    Form_frmAssets.RecordSource = "select * from tblAssets where " & GCriteria
    Form_frmAssets.Caption = "tblAssets (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

    'Close frmSearch
    DoCmd.Close acForm, "frmSearch"
    'Provide a message
    MsgBox "Found some results!"

    End If

    Thanks for your help!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so validate that the txtsearchstring and column name are legit before openign the form using say a dlookup
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2013
    Posts
    19
    Thanks. I will try it that way.

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    you can use the dlookup - but I believe (and I couldbe wrong) if you use SQL tables or are going to ever use sql then the dlookup will not work.

    I wrote a function along time ago that does this quite easily
    in your code if you want to evaluate whether a row exists for the criteria put this function in a module and run the following code

    if selectrecord(yourtablename, yourfieldname, your where clause) = true then
    do something
    else
    criteria was not met (no records)
    end if


    Public Sub SelectRecord(TableName As String, Optional SelectFieldName As String, _
    Optional WhereClause As String) as boolean
    On Error GoTo SelectRecord_Err
    dim strs as string, myrecordset as recordset
    If SelectFieldName = "" Then
    strs = "SELECT *"
    Else
    strs = "SELECT [" & SelectFieldName & "]"
    End If
    strs = strs & " FROM [" & TableName & "]"
    If WhereClause <> "" Then
    strs = strs & " WHERE " & WhereClause
    End If
    strs = strs & ";"
    Set MyRecordset = CurrentDb.OpenRecordset(strs)
    if not myrecordset.eof then
    SelectRecord = true
    else
    SelectRecord = false
    end if



    SelectRecord_Exit:
    Exit Sub
    SelectRecord_Err:
    MsgBox Err.Description & " in SelectRecord"
    Resume SelectRecord_Exit
    End Sub
    Dale Houston, TX

  5. #5
    Join Date
    Jul 2013
    Posts
    19
    Thank you for your response. I will incorporate it into my program and let you how it went. Thanks again.

  6. #6
    Join Date
    Jul 2013
    Posts
    19
    Dale,

    I am getting the following error:

    Public Sub SelectRecord (TableName As String, Optional SelectFieldName As String, _
    Optional WhereClause As String) As Boolean

    Compile Error
    Expected: end of statement.

    Would you know why this is happening?

    Jorge

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    exactly where and what is the compile error
    Dale Houston, TX

  8. #8
    Join Date
    Jul 2013
    Posts
    19
    Never mind. This should be a function not a sub. Am I right?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by jema53 View Post
    Never mind. This should be a function not a sub. Am I right?
    what happens if you change it to a function?
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    yes you have to call it - sorry i should have been more clear

    you can test it in the immediate window by placing ? in fron og the function name
    Dale Houston, TX

  11. #11
    Join Date
    Jul 2013
    Posts
    19
    Hi Dale,

    This is the code I'm using to search for records. Within the search I have you function "SelectRecord" to test for records. In the cmdSearch_Click() procedure is where I am getting the error. Could it be the way I am using the function or perhaps the location. I know I'm missing something, but I cannot pinpoint it. Thanks for your help.

    Private Sub cmdSearch_Click()

    'Make sure combo search field has been selected
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
    MsgBox "You must select a field to search."

    'Make sure the search string field is populated
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
    MsgBox "You must enter a search string."

    Else
    'Generate search criteria
    GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

    ' Make sure there's a valid search. SelectRecord function will be checking this.

    Call SelectRecord
    ‘(The compiler points to the preceding line and a message box states”
    ‘Compile Error: Argument not optional)

    'Filter frmCustomers based on search criteria
    Form_frmAssets.RecordSource = "select * from tblAssets where " & GCriteria
    Form_frmAssets.Caption = "tblAssets (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

    'Close frmSearch
    DoCmd.Close acForm, "frmSearch"
    'Provide a message
    MsgBox "Found some results!"

    End If

    End Sub
    _____________________________________ *** ______________________________

    Public Function SelectRecord(tblAssets As String, Optional cboSearchField As String, _
    Optional GCriteria As String) As Boolean

    On Error GoTo SelectRecord_Err

    Dim strs As String, myrecordset As Recordset

    If SelectFieldName = "" Then
    strs = "SELECT *"
    Else
    strs = "SELECT [" & cboSearchField & "]"
    End If

    strs = strs & " FROM [" & tblAssets & "]"

    If WhereClause <> "" Then
    strs = strs & " WHERE " & GCriteria
    End If

    strs = strs & ";"

    Set myrecordset = CurrentDb.OpenRecordset(strs)
    If Not myrecordset.EOF Then
    SelectRecord = True
    Else
    SelectRecord = False

    End If

    SelectRecord_Exit:
    Exit Function

    SelectRecord_Err:
    MsgBox Err.Description & " in SelectRecord"

    Resume SelectRecord_Exit

    End Function

  12. #12
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Argument not optional the function is looking for another peice of the arguments
    the function requires the table name , the field name, and the wherclause

    I just ran the function from immediate window for the following criteria;
    ?selectrecord("tblmaterials","description","id = 6944")

    Public Function SelectRecord(TableName As String, Optional SelectFieldName As String, _
    Optional WhereClause As String) As Boolean
    On Error GoTo SelectRecord_Err

    If SelectFieldName = "" Then
    strs = "SELECT *"
    Else
    strs = "SELECT [" & SelectFieldName & "]"
    End If
    strs = strs & " FROM [" & TableName & "]"
    If WhereClause <> "" Then
    strs = strs & " WHERE " & WhereClause
    End If
    strs = strs & ";"
    Set MyRecordset = CurrentDb.OpenRecordset(strs)
    If Not MyRecordset.EOF Then
    MsgBox True
    Else
    MsgBox False
    End If

    SelectRecord_Exit:
    Exit Function
    SelectRecord_Err:
    MsgBox Err.Description & " in SelectRecord"
    Resume SelectRecord_Exit
    End Function
    Dale Houston, TX

  13. #13
    Join Date
    Jul 2013
    Posts
    19
    Thanks Dale.
    Since I do not want to keep entering those arguments, I thought that by hardcoding those fields into your function, this would allow the function to capture whatever text was on those fields and be able to check for a valid record or not. How do I get your function to get the info from those fields? Ex: table = tblAssets; combobox = Dept Name; GCriteria = Vault.

  14. #14
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    whereever you want to check and validate simply type
    selectrecord("table name","fieldname","where clause")

    my example that I just tested on my materials table was
    selectrecord("tblmaterials","description","id = 6944")

    the result was true so how you would do this is:

    if selectrecord("tblmaterials","description","id = 6944") = true then
    your criteria has been met - do what it is you want to do
    else
    MsgbOx "You have entered or selected invalid criteria.",vbexclamation "Error"
    exit sub
    end if
    Dale Houston, TX

  15. #15
    Join Date
    Jul 2013
    Posts
    19
    Pardon my ignorance Dale.
    I thought I would start my search procedure and then from it, I would call SelectRecord to test that, indeed, there is a record to check. Once this is done, then I can do "if SelectRecord is true done this and that, Else, you have no valid record and exit sub.

Posting Permissions

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