Solved

Create a recordset and print in Debug.print

Posted on 2008-10-27
7
3,813 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Dilema in Access 2010 3 37
Compress Newid value ms sql Mssql 4 46
Multiple tables for a tabbed form 2 33
Access 2010: Combo Box Query 5 14
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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