MS Access count records returned and loop

Hi,

I'm wondering what I'm doing wrong here:
Sub SelectIntoX()

    Dim dbs As Database
    Dim name As String
    Dim N As Integer
    Dim I As Integer

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("F:\BackUp\ClientsWebsite\OMusicStudios\BackUp\BackUp_G_USB\Administration\Quickbooks\MSAccess\qb_export_12_30_11_2101.accdb")

    Set rst = dbs.OpenRecordset("SELECT Count AS N, BillItemLine.*" _
    & "FROM BillItemLine WHERE (((BillItemLine.[VendorRefFullName]) Like '*sunshine*') AND ((BillItemLine.[TxnID])='20A8D-1325181637'));")
           
    For I = 0 To N
        name = rst.Fields(0)
    Next I

    dbs.Close

End Sub

I get a Run-time error '3601'
Too few parameters. Expected 1.

I'm also wondering how I can get the Field values by the Field name rather than by numeric id so something like
rst.Fields('TxnID')

Is this how I do it?

Thanks,
Victor
Victor KimuraSEO, Web DeveloperAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
Set rst = dbs.OpenRecordset("SELECT BillItemLine.*" _
    & "FROM BillItemLine WHERE (((BillItemLine.[VendorRefFullName]) Like '*sunshine*') AND ((BillItemLine.[TxnID])='20A8D-1325181637'));")

if rst.eof then
   msgbox "No records found"
   exit sub
end if

rst.movefirst

do until rst.eof
    msgbox "The value of field VendorRefFullName is " & rst!VendorRefFullName

rst.movenext
loop
0
 
Rey Obrero (Capricorn1)Commented:
try this codes
add the microsoft DAO x.x object library to your references  (x.x is the version number of dao)

  Dim dbs As dao.Database, rst as dao.recordset
    Dim fldName As String
    Dim N As Integer
    Dim I As Integer

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("F:\BackUp\ClientsWebsite\OMusicStudios\BackUp\BackUp_G_USB\Administration\Quickbooks\MSAccess\qb_export_12_30_11_2101.accdb")

    Set rst = dbs.OpenRecordset("SELECT Count AS N, BillItemLine.*" _
    & "FROM BillItemLine WHERE (((BillItemLine.[VendorRefFullName]) Like '*sunshine*') AND ((BillItemLine.[TxnID])='20A8D-1325181637'));")
           
    For I = 0 To rst.fields.count-1
        fldName = rst.Fields(I).name
            msgbox fldName
    Next I

   rst.close
    dbs.Close
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Hi,

The SELECT Count AS N is giving me problems. When I take out "Count AS N" it's okay

I'm trying to loop through the records. I just realized that the for loop was looping through the fields rather than the records.

'    N = 3
    For I = 0 To N
        name = rst.Fields(0)
    Next I
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Rey Obrero (Capricorn1)Commented:
what exactly do you want to do?
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
loop through the records and save the value of field name "VendorRefFullName" to a variable. I'm going to use this variable in an UPDATE within the loop.
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
I tried this but it's not working:
Set rst = dbs.OpenRecordset("SELECT BillItemLine.*" _
    & "FROM BillItemLine WHERE (((BillItemLine.[VendorRefFullName]) Like '*sunshine*') AND ((BillItemLine.[TxnID])='20A8D-1325181637'));")
   
    rsCount = rst.RecordCount
           
    N = 3
    For I = 0 To rsCount
        name = rst.Fields(0)
    Next I

It's only come back with one recordset but when I run the same SELECT in Access it gives me around 10 records.
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Thank you so much! =)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.