Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Excel Query VBA-When nothing is found-

This is Excel VBA


I have this piece of code, when I run this- the second query

'////20 year WAVG

Currently there is no record for this result,  so I get an error

‘Either BOF or EOF  is true,  or the current record has been deleted.
Request operation requires a current record. “  

I don’t know what to put to get by this.

Any help would greatly be appreciated.


Public Sub OpenADO()
    Dim dbpath As String
    Dim Src As String
    Dim Connection As ADODB.Connection
    Dim Col As Integer
    Dim Recordset As ADODB.Recordset
    Dim A1 As Range
   
       
    'Without Password
     dbpath = "C:\Documents and Settings\n574824\Databases\SRP\SRPtwo.mdb"
   

    Set conn = New ADODB.Connection
    With conn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open dbpath
    End With
   
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 '////30 year WAVG
'   Create RecordSet
   
    Set Recordset = New ADODB.Recordset
    With Recordset
   
'       Filter
        Src = "SELECT [Expr1] "     '<--- space added to
        Src = Src & "FROM WA "
        Src = Src & "WHERE ([LoanTerm] = '30' ) And [CompanyID] = " & TradeLimit.ComboBox1.Value
       
         .Open source:=Src, ActiveConnection:=conn

'Write the recordset
        Sheet9.Range("L10").Value = .Fields(0)
           
    End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'////20 year WAVG
'   Create RecordSet
   
    Set Recordset = New ADODB.Recordset
    With Recordset
   
'       Filter
        Src = "SELECT [Expr1] "     '<--- space added to
        Src = Src & "FROM WA "
        Src = Src & "WHERE ([LoanTerm] = '20' ) And [CompanyID] = " & TradeLimit.ComboBox1.Value
       
         .Open source:=Src, ActiveConnection:=conn

           
    End With

  Set Recordset = Nothing
    conn.Close
    Set conn = Nothing
End Sub
0
ca1358
Asked:
ca1358
  • 3
  • 3
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I don’t know what to put to get by this.
In general, before you manipulate a recordset, you should test for this and handle it appropriately, using something like this...

'All code leading up to your .Open here

rs.Open source:=Src, ActiveConnection:=conn

If Not (rs.BOF and rs.EOF) then
   'The rest of your code goes here.
else
   'There were no records in your recordset.  Handle it gracefully here.
end if
0
 
Harisha M GCommented:
>> If Not (rs.BOF and rs.EOF) then

That should be

If Not (rs.BOF Or rs.EOF) then
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I always thought it was if there were zero records, .BOF and .EOF would be true.

If there are more than one record, and you are starting to iterate through it, then .BOF would be true, but not .EOF
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Harisha M GCommented:
Jim, Are you talking to me ?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes.  Sorry, I should have prefixed with your name or (Off-Topic).
0
 
Harisha M GCommented:
> I always thought it was if there were zero records, .BOF and .EOF would be true.

Yes, but .BOF And .EOF = True implies .BOF Or .EOF = True, which is a general condition.
0
 
ca1358Author Commented:
Thank you both!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now