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
search not returning accurate values
Page 1 of 110 Replies - 1102 Views - Last Post: 21 June 2013 - 08:27 PM
#1
search not returning accurate values
Posted 03 June 2013 - 06:50 AM
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
Replies To: search not returning accurate values
#2
Re: search not returning accurate values
Posted 03 June 2013 - 11:23 AM
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.
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, ..
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

#3
Re: search not returning accurate values
Posted 03 June 2013 - 11:28 AM
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.
http://msdn.microsof...2(v=vs.85).aspx
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
#4
Re: search not returning accurate values
Posted 04 June 2013 - 03:49 AM
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
#5
Re: search not returning accurate values
Posted 04 June 2013 - 04:05 AM
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.
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
#6
Re: search not returning accurate values
Posted 04 June 2013 - 04:14 AM
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
#7
Re: search not returning accurate values
Posted 04 June 2013 - 04:23 AM
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.
So, as already advised, you need to move the Find attempt further down - as outlined in my previous code.
#8
Re: search not returning accurate values
Posted 04 June 2013 - 04:39 AM
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
#9
Re: search not returning accurate values
Posted 10 June 2013 - 05:11 PM
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:
As you can see, there's a lot less drama.

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
#10
Re: search not returning accurate values
Posted 18 June 2013 - 03:41 AM
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:
As you can see, there's a lot less drama.

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
#11
Re: search not returning accurate values
Posted 21 June 2013 - 08:27 PM
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.
Page 1 of 1