?
Solved

View Recordset

Posted on 2012-04-06
9
Medium Priority
?
507 Views
Last Modified: 2012-04-09
Is there a way to view a recordset without having to dump the data into a table and view the table?

Here is the code for the recordset

    Dim rst As ADODB.Recordset
    Dim strSql As String
    Dim strList As String
       
    Set cnn = CurrentProject.Connection
               
        strSql = "SELECT Number, PrimaryKey, ServCat, QualDesc, SeqID, AndOr, RevFrom,    RevTo, ProcFrom, ProcTo, POSFrom, POSTo, RiskComp, " _
        & "Div, Sequence, Column, CreationDate, ModificationDate, Status, Comment " _
        & "FROM tblServCatData " _
        & "WHERE ServCat = '" & txtServCatView & "'"
                         
    Set rst = New ADODB.Recordset
    rst.Open strSql, cnn, adOpenStatic, adLockOptimistic, adCmdText
           
    If rst.EOF = True Then
        MsgBox "No Records Found"
        Exit Sub
    End If
   
    'view recordset here by some function'

         
    set rst = Nothing
                         
End sub
0
Comment
Question by:Scott Palmer
[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
  • 4
9 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 37817750
What version of Access?

For the later ones, I believe you can set the recordsource of a form or report to a recordset.

By using continuous forms, you may be able to do this.

Have alook at

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_23958304.html

Kelvin
0
 

Author Comment

by:Scott Palmer
ID: 37817795
Access 2003
I went to the link and tried to copy the code, it looks like it should work but I got an error.

Compile Error:

Invalid use of Property and it is shading what is in bold below.  

Set Me.RecordSource = rst

This is the updated code:

    Dim rst As ADODB.Recordset
    Dim strSql As String
    Dim strList As String
       
    Set cnn = CurrentProject.Connection
         
           strSql = "SELECT Number, PrimaryKey, ServCat, QualDesc, SeqID, AndOr, RevFrom, RevTo, ProcFrom, ProcTo, POSFrom, POSTo, RiskComp, " _
        & "Div, Sequence, Column, CreationDate, ModificationDate, Status, Comment " _
        & "FROM tblServCatData " _
        & "WHERE ServCat = '" & txtServCatView & "'"
                         
    Debug.Print strSql
                         
    Set rst = New ADODB.Recordset
    rst.Open strSql, cnn, adOpenKeyset, adLockOptimistic, adCmdText
     
    Set Me.RecordSource = rst
                 
    Set rst = Nothing
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37817814
you can use a listbox for this purpose

Dim rst As ADODB.Recordset
    Dim strSql As String
    Dim strList As String
       
    Set cnn = CurrentProject.Connection
         
           strSql = "SELECT Number, PrimaryKey, ServCat, QualDesc, SeqID, AndOr, RevFrom, RevTo, ProcFrom, ProcTo, POSFrom, POSTo, RiskComp, " _
        & "Div, Sequence, Column, CreationDate, ModificationDate, Status, Comment " _
        & "FROM tblServCatData " _
        & "WHERE ServCat = '" & txtServCatView & "'"
                         
    Debug.Print strSql
                         
    Set rst = New ADODB.Recordset
    rst.Open strSql, cnn, adOpenKeyset, adLockOptimistic, adCmdText

    set me.listboxName.recordset=rst
0
Independent Software Vendors: 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!

 

Author Comment

by:Scott Palmer
ID: 37817825
Unfortunately there are too many records for a list box. I tried that.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37817827
ok,
change this

Set Me.RecordSource = rst

with

Set Me.Recordset = rst
0
 

Author Comment

by:Scott Palmer
ID: 37817851
Well, I don't get an error, but it is not showing anything.  Leaving, won't look at any reply until later tonight.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37817903
create a datasheet form with record source of

SELECT Number, PrimaryKey, ServCat, QualDesc, SeqID, AndOr, RevFrom, RevTo, ProcFrom, ProcTo, POSFrom, POSTo, RiskComp,  Div, Sequence, Column, CreationDate, ModificationDate, Status, Comment  FROM tblServCatData  WHERE 1=0

that will make your datasheet form bound but no records to show.


place your datasheet form as a subform in your form

now after getting the recordset, place this codes

                   set me.subformControlName.form.recordset=rst


if you still having problem, upload a copy of your db
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37817928
0
 

Author Closing Comment

by:Scott Palmer
ID: 37824059
I don't have time to work on this anymore, i am going to just dump it into a table and view the table.  Thank you for spenidng time on this.

Scott
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

741 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