[RESOLVED] VB/Access SELECT statement problem

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.

VB Code:

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.

It's this line: TotalCharges = rsAccounts!TotalCharges that is your problem... when there are no records, the field will not exist....

You'll need to check to make sure it isn't EOF and BOF before trying to get the data

VB Code:

If Not (rsAccounts.EOF And rsAccounts.BOF) Then
  TotalCharges = rsAccounts!TotalCharges
Else
  TotalCharges = 0
End If

-tg

Hello,
Sorry its took me this long to get back with a reply.

I altered the code as you suggested and it worked fine, thanks.

I also have a problem with the ListView, when i use the code posted on the first question the ListView populates with the correct data but it does not seem to be in any order. The first column "OurRef" needs to be in ascending number order where as at present it is in no specific order.

Any ideas,

Thanks again for the original reply,
SKM.

You need to add an ORDER BY clause to the SQL statement for your recordset. This clause will sort the rs by the specified field.

VB Code:

rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges" _
     & " From tbl_master" _
     & " Where InsCoId = " & InsCoId & " And PaymentReceived = 'NO'"
     & " Order By OurRef", cn

Hello,

Thanks for your reply, I tried it and it works great.

Thanks again, it is much appreciated,

SKM.

Well you can also use the RecordCount Property. The code is like this....

If rsAccounts.RecordCount=0 Then
Msgbox("No such records")
exit sub()
else
continue rest of the code here

..............Hope this will work for you :wave: