Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

View Recordset

Posted on 2012-04-06
9
Medium Priority
?
509 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

618 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