[RESOLVED] VB/Access SELECT statement problem

[SKM]

Hello.

Below is the code i use to connect using a recordset to an Access database, the code uses the Select statement to retrieve records which match the Select criteria and then displays the matching records in a Listview.

Option Explicit
Dim objCurrLi As ListItem
Dim InsCoId As String
Dim SumColumb As Double
 
Private Sub cboInsCo_GotFocus()
ListView1.ListItems.Clear      'Clear existing ListView
SumColumb = "0.00"
lblBal.Caption = SumColumb
lblBal.Caption = ""            'Clear Balance Display
End Sub
 
Private Sub cmdLstAcc_Click()
 
Dim TotalCharges As Double
Dim abc As Integer
InsCoId = lblInsCo.Caption
 
ListView1.ListItems.Clear   'Clear existing ListView
 
If cboInsCo.Text = "" Then
   MsgBox "No Insurance Company has been selected.", vbExclamation, "Data Entry Error"
   GoTo abc     'Avoid opening Recordset if no Insurance Company is selected, jump to abc
  Else
End If
  
'Set up and open Access connection and recordset
Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\SKM_ICE Database\SKM_ICE DBase.mdb"
    cn.Open
Set rsAccounts = New ADODB.Recordset
     
rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges" _
     & " From tbl_master" _
     & " Where InsCoId = " & InsCoId & " And PaymentReceived = 'NO'", cn
      
TotalCharges = rsAccounts!TotalCharges
    
'Populate ListView
Do While Not rsAccounts.EOF
   Set objCurrLi = ListView1.ListItems.Add(, , rsAccounts!ourRef & "") 'Our Ref
        objCurrLi.SubItems(1) = rsAccounts!YourRef & ""            'Your Ref
        objCurrLi.SubItems(2) = rsAccounts!Date & ""                'Date
        objCurrLi.SubItems(3) = rsAccounts!Insured2 & ""           'Insured2
        objCurrLi.SubItems(4) = rsAccounts!TotalCharges & ""     'Total charges
   SumColumb = SumColumb + rsAccounts!TotalCharges
   rsAccounts.MoveNext
Loop
 
'Close Access connection and recordset
rsAccounts.Close
cn.Close
Set rsAccounts = Nothing
Set cn = Nothing
    
lblBal.Caption = SumColumb
lblBal.Caption = Format(lblBal, "####0.00")
 
abc:  ' Jumps to here from GoTo statement if cboInsCo.Text = ""
End Sub
 
Private Sub cmdClose_Click()   'Return to Report screen
ListView1.ListItems.Clear         'Clear existing ListView
frmOtstngAcc.Hide
Unload Me
End Sub

The above code works fine as long as there are matching records in the database, if no matching records are found the following error message appears, "Runtime error 3021: Either BOF or EOF is true, or the current record has been deleted".

What needs adding to the code or how does the Select statement need modifying to avoid this error, if no matching record is found i would like a Message box to state that no records were found.

Your help would be much appreciated as i have been working on this problem for a while with no success.

Thanks,
SKM.