General discussion

Locked

Access 97/VB Interface/SQL query using S

By jazzzlovr ·
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!!!

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 97/VB Interface/SQL query using S

by neing In reply to Access 97/VB Interface/SQ ...

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

Collapse -

Access 97/VB Interface/SQL query using S

by jazzzlovr In reply to Access 97/VB Interface/SQ ...

The question was auto-closed by TechRepublic

Collapse -

Access 97/VB Interface/SQL query using S

by donq In reply to Access 97/VB Interface/SQ ...

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

Collapse -

Access 97/VB Interface/SQL query using S

by jazzzlovr In reply to Access 97/VB Interface/SQ ...

The question was auto-closed by TechRepublic

Collapse -

Access 97/VB Interface/SQL query using S

by jazzzlovr In reply to Access 97/VB Interface/SQ ...

This question was auto closed due to inactivity

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Software Forums