Solved

Create a recordset and print in Debug.print

Posted on 2008-10-27
7
3,601 Views
Last Modified: 2013-11-25
I am trying to reach myself more about recordsets as I have an issue that needs them.  I have data that comes from an Excel spreadhseet.  This data needs to be separated by branches and then automatically e-amiled to somenoe at the branch.  Now, I am developing this in stages and the first thing I am trying to do is create a recordset.  Simple, I have the data in the table but am trying to simply create a recordset and send the data to the Immediatley window.  But I keep getting a datatype mismatch when it hit the debug.print statement.
'First check upload to be sure that it and the report type are of the same
'type.  If not, warn user and exit sub.
Dim rst As Recordset, db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("tblAMLUploadedToday", dbOpenTable)
Do Until rst.EOF
Debug.Print rst
rst.MoveNext
Loop
End Sub

Open in new window

0
Comment
Question by:ssmith94015
  • 4
  • 3
7 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 22817520
Debug.Print rst!fld1, rst!fld2, rst!fld3, etc.  

you must provide each of the field names for that recordset record.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22817552
Another way:
'First check upload to be sure that it and the report type are of the same
'type.  If not, warn user and exit sub.
Dim rst As Recordset, db As DAO.Database, fld as field
Set db = CurrentDb
Set rst = db.OpenRecordset("tblAMLUploadedToday", dbOpenTable)
Do Until rst.EOF
  for each fld in rst.fields: Debug.Print rst(fld):next fld
  rst.MoveNext
Loop
End Sub

Open in new window

0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 22817565
Lets reset that:


'First check upload to be sure that it and the report type are of the same
'type.  If not, warn user and exit sub.
Dim rst As Recordset, db As DAO.Database, fld as field
Set db = CurrentDb
Set rst = db.OpenRecordset("tblAMLUploadedToday", dbOpenTable)
Do Until rst.EOF
  for each fld in rst.fields: Debug.Print rst(fld),:next fld
  rst.MoveNext
Loop
End Sub

Open in new window

0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:ssmith94015
ID: 22817665
This is an ACCESS database and I am getting an ITEM NOT FOUND IN TIHS COLLECTION error when it hits : Debug.Print rst(fld),
0
 

Author Comment

by:ssmith94015
ID: 22817701
Got it to work with a slight modiification.  Also, if I want to have the data across the page, then I woudl have to list out the fileds individually?  Somethign like

rst.field(1), rst.field(2). rst.field(3)........ after the debug.print.
Dim rst As Recordset, db As DAO.Database, fld As Field
Set db = CurrentDb
Set rst = db.OpenRecordset("tblAMLUploadedToday", dbOpenTable)
Do Until rst.EOF
        For Each fld In rst.Fields
          Debug.Print fld
        Next fld
  rst.MoveNext
Loop
 
End Sub

Open in new window

0
 

Author Closing Comment

by:ssmith94015
ID: 31510553
Thank you.  This is just start of trying to figure out a rather complex process but I needed to learn how to do this.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22826352
Did you notice the comma at the end of my debug.print statement?  that is the equivalent of a tab, and should keep all the fields in the same line.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

821 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