Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    10

    Unanswered: Search plus error message

    I would like to write VBA code into a button on a form to search a table for an existing record, if the record does not exist, then continue, to a form, if not then show an error message.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Generally speaking, when you want to search for a specific row in a data set (table or query), you can (using DAO):
    1. Assemble a criteria with what you're looking for in a string variable.
    2. Open a Recordset on the table (or query).
    3. Use the FindFirst method of the RecordSet, using the criteria previously defined.
    4. Check the value of the NoMatch property of the RecordSet, knowing that:
    - NoMatch = True means that no row (record) in the data set matches the criteria (--> not found).
    - NoMatch = False means that at least one row in the data set matches the criteria and that the current record of the RecordSet points on that row (--> found).

    Example:
    a) Table: Tbl_People
    - RowID Numeric Auto
    - FirstName Text
    - LastName Text
    - Other columns...

    b) To retrieve the RowID (which is a Unique Identifier of each row), knowing the FirstName and LastName:
    Code:
    Function FindRowID(Byval FirstName As String, Byval LastName As String) As Long
    
        Dim rst As DAO.RecordSet
        Dim strCriteria As String
    
        strCriteria = "FirstName = '" & FirstName & "' AND LastName = '" & LastName & "'"
        Set rst = CurrentDb.OpenRecordSet("Tbl_People", dbOpenSnapshot)
        rst.FindFirst strCriteria
        If rst.NoMatch = False Then FindRowID = rst!RowID 
        rst.Close
        Set rst = Nothing
    
    End Function
    Another method consists in opening the Recordset with a dynamic query that limits the records in the RecordSet to those matching the criteria then test the BOF (or EOF) property of the Recordset (BOF = True or BOF = True means that the Recodset is empty --> not found):
    Code:
    Function FindRowID(Byval FirstName As String, Byval LastName As String) As Long
    
        Dim rst As DAO.RecordSet
        Dim strSQL As String
    
        strSQL = "SELECT RowID FROM Tbl_People WHERE FirstName = '" & FirstName & "' AND LastName = '" & LastName & "'"
        Set rst = CurrentDb.OpenRecordSet(STRSQL, dbOpenSnapshot)
        If rst.BOF= False Then FindRowID = rst!RowID 
        rst.Close
        Set rst = Nothing
    
    End Function
    If the code is used in the module of a form that's bound to the data set, things are easier because there already is an open Recordset:
    Code:
    Function FindRowID(Byval FirstName As String, Byval LastName As String) As Long
    
        Dim rst As DAO.RecordSet
        Dim strCriteria As String
    
        strCriteria = "FirstName = '" & FirstName & "' AND LastName = '" & LastName & "'"
        Set rst = Me.RecordsetClone
        rst.FindFirst strCriteria
        If rst.NoMatch = False Then FindRowID = rst!RowID 
        rst.Close
        Set rst = Nothing
    
    End Function
    Since you want to open a form that's probably bound the the data set, you can simply test for the existence of a row matching the criteria (so you don't open the form is there are no matching records), then open the form with the WhereCondition parameter included in the DoCmd.OpenForm method:
    Code:
    Sub OpenSecondForm(Byval FirstName As String, Byval LastName As String) 
    
        strCriteria = "FirstName = '" & FirstName & "' AND LastName = '" & LastName & "'"
        If DCount("*", "Tbl_People", strCriteria) > 0 Then
                DoCmd.OpenForm "FormName", , , strCriteria
        End If
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Mar 2013
    Posts
    10
    Thank you!!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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