Solved

View Recordset

Posted on 2012-04-06
9
503 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 500 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 500 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

729 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