Solved

Access report, SQL parameter passing and VB form Design

Posted on 1998-07-25
8
154 Views
Last Modified: 2010-05-03
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
Comment
Question by:g6yassin
  • 5
  • 3
8 Comments
 

Author Comment

by:g6yassin
ID: 1466649
Edited text of question
0
 

Author Comment

by:g6yassin
ID: 1466650
Edited text of question
0
 

Author Comment

by:g6yassin
ID: 1466651
Edited text of question
0
 

Author Comment

by:g6yassin
ID: 1466652
Edited text of question
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 2

Accepted Solution

by:
percosolator earned 70 total points
ID: 1466653
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
 
LVL 2

Expert Comment

by:percosolator
ID: 1466654
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
 

Author Comment

by:g6yassin
ID: 1466655
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
 
LVL 2

Expert Comment

by:percosolator
ID: 1466656
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now