1) May some pls tell what's wrong with this coding as the system prompt me for a syntax error.
I'm trying to link my recordset to the data report for printing.
VB Code:
Private Sub CmdPrint_Click()
Call connectDB
sql = "SELECT distinct * FROM Invoice WHERE Invoice='"
rs.Open sql, cn, adOpenStatic, adLockOptimistic
Set InvoiceReciept.DataSource = rs
InvoiceReciept.Show
End Sub
2) Can a data report be used to linke differnt fields together? For example, CustID from customer table, Invoiceid from invoice table and CarID from Car Table. Is it possible?
1) The syntax error is in the Where clause. It is incomplete. The Invoice is = to what...
Using Distinct * is kind of pointless, as every row will be (should be) unique.
2) Data Report will display anything in the Recordset that you tell it to display. If you create a recordset from 3 tables, all data in the Selection List is available to the report.
Last edited by brucevde; Mar 13th, 2006 at 01:28 PM.
1) The syntax error is in the Where clause. It is incomplete. The Invoice is = to what...
Can you pls show an example? is it like InvoiceID "'"?
2) Data Report will display anything in the Recordset that you tell it to display. If you create a recordset from 3 tables, all data in the Selection List is available to the report.
I created the recordset in the form. Where shall I write the recordset coding to retrieve the neccessary fields? is under Data Report?
lngInvoiceId = 101010
Can you pls tell what does the line of code mean?
???? It sets the variable lngInvoiceId equal to 101010 of course. No offense, but if you don't know what a simple assignment statement does, how can you expect to understand other coding.
If I want to open 3 tables, I should do something like this?
Do you want to open 3 Recordsets from 3 Tables?
VB Code:
Dim rsCustomers as ADODB.Recordset
Dim rsCarDetails as ADODB.Recordset
sql = "SELECT * FROM Customer WHERE CustID = " & lngCustID
rsCustomers.Open sql, ....
sql = "SELECT * FROM CarDetails WHERE CarRegNo = " & lngCarRegNo
rsCarDetails.Open sql,...
However, the Data Report can only use 1 Recordset at a time, so you would need to Join the tables on a field that is common to both.
VB Code:
Dim rsBoth as ADODB.Recordset
sql = "Select * From Customer Inner Join CarDetails On Customer.CustID = CarDetails.CustId Where Customer.CustId = " & lngCustId & " and CarDetails.CarRegNo = " & lngCarRegNo
I was actually asking if it's possible for user to print as shown on the picture attached. For example, the user is browsing the records and he wants to print invoice 142. Do he have to enter 142 or the program will auto direct him to Datareport.
VB Code:
Dim rsBoth as ADODB.Recordset
sql = "Select * From Customer Inner Join CarDetails On Customer.CustID = CarDetails.CustId Where Customer.CustId = " & lngCustId & " and CarDetails.CarRegNo = " & lngCarRegNo
rsBoth.Open sql,...
For what I understand, JOINSQL is used only when you are joining identical fields. However, in my case, I have to use recordset to link First Name, Address etc also.
For what I understand, JOINSQL is used only when you are joining identical fields. However, in my case, I have to use recordset to link First Name, Address etc also.
Is possible do that?
Without knowing your database schema it would be impossible to answer that question.
For example, the user is browsing the records and he wants to print invoice 142. Do he have to enter 142 or the program will auto direct him to Datareport.
As long as an Invoice number is already loaded into the Textbox, the code behind the Print button can do everything neccessary to run the Data Report.
VB Code:
Private Sub CmdPrint_Click()
Dim rs as ADODB.Recordset
Call connectDB
sql = "SELECT * FROM Invoices WHERE InvoiceID = " & txtInvoiceNo.Text
if you want to oen them simultaneously declare them with different recordset
dim rs1 as adodb.recordset,rs2 as recordset
set rs1= new adodb.recorset
rs1.open,"select * from customer",cn,adopenstatic
set rs2= new adodb.recorset
rs2.open,"select * from invoice",cn,adopenstatic
No. I mean did you insert Group Header and Footer sections? If yes and the recordset is not a hierarchical recordset you will get the error "Report Sections do not match Datasource".
Somehow, I recreate a new datareport and that error went away.
However, when I try to open 2 connectionsetp, there is an runtime error[Type Mismatch] on the lngCustID = Cbo_Cust. Why is this so?
VB Code:
Private Sub CmdPrint_Click()
Dim lngInvoiceId, lngCustID As Long
Dim rsCustomers As New adodb.Recordset
lngInvoiceId = Txt_NoOfInv
lngCustID = Cbo_Cust 'error source
Call connectDB
sql = "SELECT * FROM Invoice WHERE InvoiceID = " & lngInvoiceId
rs.Open sql, cn, adOpenStatic, adLockOptimistic
sql = "SELECT * FROM Customer WHERE CustID = " & lngCustID
The Type Mismatch error probably occurs because Cbo_Cust contains character data (perhaps the Customer Name) and you are trying to convert it to Numeric.
Here is a sample report using Hierarchical Recordset. It uses the Northwind database, just change the path in the db.open statement.
Thanks for the attached files, but it seem alittle complicated to me. I think I need more guidance on that. My porject will be completed after this datareport. Hope you can help.
For example, I need to display the InvoiceID, DateRent, DateRetun from the Invoice table.
CustID, CustFname, CustLname from the customer table
CArRegNo No, Car Model and Cartype from cardetails table.
How may I link them together to display in the Date report format.
You need to include the Relate commands (which are like Inner Joins).
Can you let me know the layout of the report? The layout will dictact how many Child recordsets will exist in the Hierarchy.
The following assumes you want one group (Customer) and one Child (invoice and cardetail).
Reply With Quote
