[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Access count records returned and loop

Posted on 2012-09-07
7
Medium Priority
?
799 Views
Last Modified: 2012-09-07
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
0
Comment
Question by:Victor Kimura
  • 4
  • 3
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38377630
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
 

Author Comment

by:Victor Kimura
ID: 38377711
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38377749
what exactly do you want to do?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:Victor Kimura
ID: 38377792
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
 

Author Comment

by:Victor Kimura
ID: 38377812
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38377874
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
 

Author Closing Comment

by:Victor Kimura
ID: 38377909
Thank you so much! =)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question