BobRodes, on 11 June 2013 - 03:11 AM, said:

Your problem is that you are confusing two different concepts and your thinking has therefore become disorganized. Now pay attention. />/>

If BOTH BOF and EOF are true, then your recordset is empty (you've gone beyond the beginning and beyond the end of the data both at once). On the other hand, if you call the Find method, and the value you're looking for isn't there, then you'll run past the end of the recordset and EOF will be true. BUT BOF won't; you've only gone beyond the end of the data.

So, let's backtrack to your first code sample. After line 4, you want to check and see if there are any records to find. So check both BOF and EOF and return false if both are true. After line 5, you want to see if a record got found (you don't need the additional step of first checking if the record you're on is a match; find will stop there if it is). If EOF is true, then it didn't. So, here, you check for EOF but NOT BOF.

Your main problem is your line 6 should only check BOF.

By the way, I prefer using Filter to using Find, personally:

'[open a recordset called rs]
'[create strCriteria]
with rs
   .Filter = strCriteria
   CheckIfSupplyWhateverIsValid = Not(.BOF And .EOF)
End With

As you can see, there's a lot less drama.

Private Function CheckIfCustomerTypeIsValid() As Boolean
    strCriteria = "Type=" & " '" & Trim(DataComboCustomerType.Text) & "' "
    Set rstCustomers = New ADODB.Recordset
    rstCustomers.Open "CustomerTypeOfAccounts", Cnn, adOpenDynamic, , adCmdTable
    rstCustomers.Find strCriteria
    If (rstCustomers.EOF = True) Or (rstCustomers.BOF = True) Then
    CheckIfCustomerTypeIsValid = False
    Else
    CheckIfCustomerTypeIsValid = True
    End If
 End Function

 Private Function CheckIfSupplierTypeIsValid() As Boolean
    strCriteria = "TruckType=" & " '" & Trim(DataComboSupplierType.Text) & "' "
    Set rst = New ADODB.Recordset
    rst.Open "TruckTypes", Cnn, adOpenForwardOnly, , adCmdTable
    
    If rst.BOF And rst.EOF Then
       CheckIfSupplierTypeIsValid = False
    End If
   rst.Find strCriteria, 1, adSearchForward
    If Trim(DataComboSupplierType.Text) = rst!TruckType Then
        CheckIfSupplierTypeIsValid = True
    Else
     rst.Find strCriteria, 1, adSearchForward
        CheckIfSupplierTypeIsValid = True
    End If
 End Function

 Private Function CheckIfSupplierTypeIsValid() As Boolean
    strCriteria = "TruckType=" & " '" & Trim(DataComboSupplierType.Text) & "' "
    Set rst = New ADODB.Recordset
    rst.Open "TruckTypes", Cnn, adOpenForwardOnly, , adCmdTable
    rst.Find strCriteria, 1, adSearchForward
    If rst.BOF And rst.EOF Then
       CheckIfSupplierTypeIsValid = False
    ElseIf rst!TruckType = Trim(DataComboSupplierType.Text) Then
        CheckIfSupplierTypeIsValid = True
    Else
        CheckIfSupplierTypeIsValid = True
    End If
 End Function

 Private Function CheckIfSupplierTypeIsValid() As Boolean
    strCriteria = "TruckType=" & " '" & Trim(DataComboSupplierType.Text) & "' "
    Set rst = New ADODB.Recordset
    rst.Open "TruckTypes", Cnn, adOpenForwardOnly, , adCmdTable
    rst.Find strCriteria, 1, adSearchForward
    If rst.BOF And rst.EOF Then
       CheckIfSupplierTypeIsValid = False
    ElseIf rst!TruckType = Trim(DataComboSupplierType.Text) Then
        CheckIfSupplierTypeIsValid = True
    Else
        CheckIfSupplierTypeIsValid = True
    End If
 End Function