[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

Access report, SQL parameter passing and VB form Design

I am looking few answers and I will raise the points if I get a good answer and if it necessary.
Please keep in mind I am new to VB5 and Access.
I just  started Using Expert-Exchange this month and I would
like to know if I asked the question with High/low or reasonable points.

- I designed my database tables, queries and reports in a Microsoft Access and I am using VB5 for my programming requirement.
    = I want to open the Access report without opening Access. I already received an answer for this question but unfortunately it opens Access. It can't be done is a good
answer.

   =  I have the query “SELECT * FROM table1 WHERE lname = anygivenname”  In Access
I would like to open this query in VB using openRecordset and pass a parameter anygivenname .
How should I pass the parameter "anygivenname" to the query when I am opening it using db.openRecordSet("query1")?
=  When I run my program with a low resolution screen the form covers the entire screen, what should I need to do to make sure that the form opens with a reasonable size taking into account the size and the resolution of the screen.


Thanks
Yassin
0
g6yassin
Asked:
g6yassin
  • 5
  • 3
1 Solution
 
g6yassinAuthor Commented:
Edited text of question
0
 
g6yassinAuthor Commented:
Edited text of question
0
 
g6yassinAuthor Commented:
Edited text of question
0
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.

 
g6yassinAuthor Commented:
Edited text of question
0
 
percosolatorCommented:
You use a querydef.

Example:

Here's the SQL of my query, that I created in Access.  The Query is called "Cust" and it queries a table called "Customer" which has three fields in it:  

Customer [Text DataType], Date [Date/Time datatype], and Status [Text DataType].

Also, in the query, I have created a parameter called "Stat" and specified that the Status field has to equal the value passed into the "Stat" parameter.

------------------------------------
PARAMETERS Stat Text;
SELECT Customer.Customer, Customer.Date, Customer.Status
FROM Customer
WHERE (((Customer.Status)=[Stat]));
------------------------------------


VB Code to access the stored access query:

------------------------------------
Option Explicit

Dim db As Database
Dim qd As QueryDef
Dim r0 As Recordset

Private Sub Form_Load()

    Set db = OpenDatabase("C:\Test\Cust.MDB")
   
   
    Set qd = db.QueryDefs("Cust")
   
    qd.Parameters("Stat") = "GOLD"
   
    Set r0 = qd.OpenRecordset(dbOpenSnapshot)
   
    If (Not (r0.BOF And r0.EOF)) Then
   
        r0.MoveFirst
       
        While (Not r0.EOF)
       
            '(your code here)

            Debug.Print r0!Customer
           
            r0.MoveNext
           
        Wend
   
    End If
   
    r0.Close
   
    qd.Close
   
End Sub
------------------------------------

For this, I used the Microsoft DAO 3.5 library.  (Project, references)

0
 
percosolatorCommented:
Your question:

"When I run my program with a low resolution screen the form covers the entire screen, what should I need to do to make sure that the form opens with a reasonable size taking into account the size and the resolution of the screen"

Use the screen.height and screen.width to determine the size of your screen, and then the me.height and me.width off your form to determine your form size and change the size of your form programmatically at run-time.
0
 
g6yassinAuthor Commented:
The answer is perfect but I would like you to same thing about
this part of the question.

= I want to open the Access report without opening Access. I already received an answer for this question but unfortunately it opens Access. It can't be done is a good
answer.
I raised the points to 75.
Thanks
0
 
percosolatorCommented:
Unfortunately I don't have an immediate answer to your question.

To the best of my knowledge, it is not possible.  I was curious as why Microsoft did not just strip it out and make their own reporting software.  I was told that the report writer relies heavily upon the Microsoft Access VB engine... :(

Never tried to do it with Access, but I know that you can OLE to Excel to embed a graph in your VB form. (Long time ago, I did that one).  That's worth a try.

The alternative, is to use Crystal Reports 4.6, which comes with Visual Basic 5.

We use version 6.0, and I must admit, it's not really intuitive and is awkward to use, but it has low overhead and I've become accustomed to it's quirks.

I might putz around with OLE-ing to the Access report designer for a bit this weekend, but my guess is, is that it's going to load Access and hog-up 3 or 4 meg of memory.

Douglas
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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