Web Development·12,146 discussions

Access 97/VB Interface/SQL query using S

Locked

I have a Access 97 database for Customer Information, Orders, and Item Inventory. This database is connected to a VB interface using ADO. Using the SQL builder in VB’s Data Report, I’m trying to generate a receipt for each order using that specific order number.

The tables that have been set up are Customer (personal info), Orders (Cust. No., Order No., Subtotal, Tax, Shipping, Total), Items (Item info – size, price, etc.), and OrderList (the line items of each order).

I can get the Report to list the line items for ALL of the orders, but not a particular order. I have tried setting the criteria, but I need it to use either a variable or something like txtOrderNumber.Text, since the Order Number is not known until runtime.

Thisinfo is urgently needed and SOON! 400 pts. to the first person with the magic answer by Dec. 9!!!

Topic

Access 97/VB Interface/SQL query using S

In reply to Access 97/VB Interface/SQL query using S

You can use DataEnvironment object and Command Object (Command Obj is a object under DataEnvironment obj) to solve this problem. Below is a example code to change sql statement at runtime.

Private Sub Command3_Click()
Dim commandObj
SetcommandObj = DataEnvironment1.Commands(“command1”)
commandObj.CommandText = “Select * from usrcustomer_info Where CustomerID = ‘” & text1.Text & “‘”

And if you want to change datafield that will display on your repot you can use this code
Dim sectionObj, controlObj
Set sectionObj = DataReport1.Sections(“command1_detail”)
Set controlObj = sectionObj.Controls(“text1”)
ctl.DataField = “customerid”

customerid is a field in the table, usrcustomer_info, and I want to display it on my report

Access 97/VB Interface/SQL query using S

In reply to Access 97/VB Interface/SQL query using S

The question was auto-closed by TechRepublic

Access 97/VB Interface/SQL query using S

In reply to Access 97/VB Interface/SQL query using S

I assume (dangerous thing 2 do) you have an open Access 97 Form (“Orders”)with an Order Number Field (“OrdNum”) in it??? You said you already had the query that produces every record (“MyReceiptQuery”). Within that query add the following in the “Criteria:” area under the “OrdNum” column: “[Forms].[Orders].[OrdNum]”. That should work dynamically to produce any single record groul you want whether there is 1 item or many? Good Luck, Don Quigg

Access 97/VB Interface/SQL query using S

In reply to Access 97/VB Interface/SQL query using S

The question was auto-closed by TechRepublic

Access 97/VB Interface/SQL query using S

In reply to Access 97/VB Interface/SQL query using S

This question was auto closed due to inactivity

[Author]

Replies