• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

View Recordset

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
Scott Palmer
Asked:
Scott Palmer
  • 4
  • 4
2 Solutions
 
Kelvin SparksCommented:
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
 
Scott PalmerData AnalystAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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!

 
Scott PalmerData AnalystAuthor Commented:
Unfortunately there are too many records for a list box. I tried that.
0
 
Rey Obrero (Capricorn1)Commented:
ok,
change this

Set Me.RecordSource = rst

with

Set Me.Recordset = rst
0
 
Scott PalmerData AnalystAuthor Commented:
Well, I don't get an error, but it is not showing anything.  Leaving, won't look at any reply until later tonight.
0
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
0
 
Scott PalmerData AnalystAuthor Commented:
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

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!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now