[tendaimare]

I want to search my database table for a certain record but the search is only when it is not the first record. If it is the first record the search code will not return true. Is there any adjustments I need to make to my Code so that even if the record is the first one it will return true

Private Function CheckIfVehicleTypeIsValid() As Boolean
    Dim strCriteria As String
    strCriteria = "TruckType=" & " '" & Trim(DataComboVehicleType.Text) & "' "
    Set rstTrucks = New ADODB.Recordset
    rstTrucks.Open "TruckTypes", Cnn, adOpenForwardOnly, , adCmdTable
    rstTrucks.Find strCriteria, 1, adSearchForward
    If rstTrucks.EOF Then
    CheckIfVehicleTypeIsValid = False
    Else
    CheckIfVehicleTypeIsValid = True
    End If
   End Function

[andrewsw]

From my limited experience, I would first check if any records at all were returned: BOF and EOF together signify this. Then I would examine the TruckType of the current (first) row, before using Find on the rest of the recordset.

Dim sVehicleType As String
sVehicleType = Trim(DataComboVehicleType.Text)

rstTrucks.Open "TruckTypes", Cnn, adOpenForwardOnly, , adCmdTable
If rstTrucks.BOF And rstTrucks.EOF Then
    'there were no records - do something else
ElseIf rstTrucks!TruckType = sVehicleType Then
    'found on first row
    CheckIfVehicleTypeIsValid = False
Else
    'continue with Find ... after the current record (argument 1)

Recordset Object :MSDN

Bear in mind, however, that databases don't care about, or guarantee, the order of records, unless you specifically provide an ORDER BY clause.

My colleague Bob is better placed to advised you on this subject but, in absentia, ..

[andrewsw]

BTW I was considering using Bookmarks to achieve this. That is, to store the Bookmark of the first row and compare it to the Bookmark of the first found row. Unfortunately, ADO bookmarks are not guaranteed to be the same, even if they refer to the same row.

Quote

users should not expect bookmarks to be directly comparable — two bookmarks that refer to the same record may have different values.

http://msdn.microsof...2(v=vs.85).aspx

[tendaimare]

Okay Sir, I tried but now I am getting an error that says "Either EOF and BOF is true, or the current record has been deleted.Requested operation requires a current record."

 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

[andrewsw]

You should also tell us which line the error message refers to, or takes you to. Is it the Find line? If so, it sounds like your query hasn't returned any rows. If you refer back to my previous code, this is why I perform the BOF/EOF check before attempting to Find.

Either there are no rows returned, or the Find doesn't find a record. There will then be no current record and attempting to read !TruckType will cause the error.

I recommend that you take the debugging tutorial linked in my signature.

This post has been edited by andrewsw: 04 June 2013 - 04:08 AM

[tendaimare]

The elseif line

 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

This post has been edited by tendaimare: 04 June 2013 - 04:18 AM

[andrewsw]

Yes, so I guess that Find didn't locate a record, meaning that EOF is true (there is no current record) and the attempt to read !TruckType generates the error.

So, as already advised, you need to move the Find attempt further down - as outlined in my previous code.

[tendaimare]

I am still getting the same error. But I was not sure about where exactly to put the find statement.

 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

[BobRodes]

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.

This post has been edited by BobRodes: 10 June 2013 - 05:12 PM

[tendaimare]

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.

BobRodes you're RIGHT the code worked well.You're the best, but I am still using Find This is the final code. I'll try out the Filter. Thanks!!!

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

[BobRodes]

Glad you got it working. You have one problem, which is that you have an Or in line 6 instead of an And. It probably won't be a problem for you, because if there are any records in the recordset neither will be true when you first open it, but it's still kind of messy so you may want to change it.