Generate a report at run-time

[np3il]

I created an SQL comant and I need to generate the report at run-time.

I am using DataReport in VB6 and use the

report.DataSource = rsRep

command and I get an error (Compile Error: Method or Data Member nor found).

If I create a data member, it can not be done at run-time.

For example, the SQL select is a date dependent command.
The SQL command work fine inside the SQL command designer, but I need to get the info at run-time.

How can I generate a report at run-time???

Help B-)

 

[chadt]

use crystal instead of the VB report designer.

 

[VBmim]

np3iul, I supose you are using a DataEnvironment for your sql command, and that your report is connected to this DataEnvironment (The datasource property in your report). What you have to do is:

DataEnvironment1.Commands("nameofyourcommand").CommandText = "select * from table where tabDate = 1-1-2003"
DataEnvironment1.

rs

nameofyourcommand.Open
DataEnvironment1.

rs

nameofyourcommand.Requery

The rs thing is very important. Don't forget them

That should do the trick.

 

[np3il]

Yes I am using DataEnvironment and DataReport.

The report needed required the SQL command to be Date dependent. I have solve that part of it but I need to generate the report th SQL command generates using the DataEnvironment report from VB6.

B-)

 

[VBmim]

Well...

In your report-printing code you can use my code previously posted above like this

Private Sub cmdPrintReport_Click()

DataEnvironment1.Commands("nameofyourcommand").CommandText = "select * from table where tabDate = " & YourDateHere
DataEnvironment1.rsnameofyourcommand.Open
DataEnvironment1.rsnameofyourcommand.Requery

Report1.PrintReport True

end sub

Of course the variable 'YourDateHere' is assigned at runtime, making your report a run-time report.

Just try it and tell us if it worked.

Greetz

Mim

 

[np3il]

Hello All,

I do not have Crystal Reports. As you might imagine, the cheapest is the best "sometimes" but it will have to do for this report.

I am interested in using the report.show command instead of the report.PrintReport True command.

The report is still not passing teh correct data. It is passing ALL the data not the part needed. Hence DATE dependent command.

I have the command working fine but I need to pass the result from the query to the report.

Thanks B-)

 

[VBmim]

Hello np3il

Strange strange strange... It should work...
Can you post your code? It will be easier to help you this way.

Greetz

Mim

 

[np3il]

Hello,

Here is the critical part

Set rsRep = New Recordset
Set rsRep.ActiveConnection = dbConn

'Create SQL Command
sSelect = "SELECT TICKETS.TICKET_NO, TICKETS.PU_TIME, CUSTOMER.FIRSTNAME, CUSTOMER.LASTNAME, TICKETS.LAST_TICK, TICKETS.PU_EMPL, SERVICES.SERV_ABBR, TICKETS.PU_DATE, TICKETS.STRIP_TAG, TICKETS.AMOUNT, DETAIL.QUANITY, COLORS.COLOR_DESC, COLORS.PATT_DESC "
sFrom = "FROM TICKETS, CUSTOMER, SERVICES, DETAIL, COLORS, TRANDETL "
sWhere = "WHERE TICKETS.CUST_NO = CUSTOMER.CUST_NO AND TICKETS.CUST_NO = CUSTOMER.CUST_NO AND TICKETS.SERV_CODE = SERVICES.SERV_CODE AND TICKETS.SERV_CODE = SERVICES.SERV_CODE AND TICKETS.TICKET_NO = DETAIL.TICKET_NO AND DETAIL.COLOR_NO = COLORS.COLOR_NO AND DETAIL.COLOR_NO = COLORS.COLOR_NO AND DETAIL.TICKET_NO = TRANDETL.TICKET_NO AND TICKETS.TICKET_NO = TRANDETL.TICKET_NO AND DETAIL.TICKET_NO = TRANDETL.TICKET_NO AND (TICKETS.PU_DATE IS NOT NULL)AND (TICKETS.PU_DATE = #" + txtDate.Text + "#) "
sOther = "ORDER BY TICKETS.TICKET_NO, TICKETS.PU_TIME;"

'Put it Together
strSQL = sSelect + sFrom + sWhere + sOther

rsRep.Source = strSQL
rsRep.CursorLocation = adUseServer
rsRep.CursorType = adOpenForwardOnly
rsRep.LockType = adLockReadOnly

rsRep.Open 'Open Record set with SQL Command

DEConn.Commands("SQLCommand&quot.CommandText = strSQL
DEConn.rsSQLCommand.Open
DEConn.rsSQLCommand.Requery

rptDailySales.Show 'Display Report

...

What am I doing wrong???

Thanks B-)

 

[VBmim]

Hello

In what kind of database is your data stored. Is it Access or SQL Server?

If it is SQL Server, the '#'-signs for dates don't work...

greetz

Mim

 

[VBmim]

Ow yes, I almost forgot...

In your code you don't need to make the recordset 'rsRep'. The recordset you want to use already exists in the command object of your data environment...

All lines below in bold can be deleted.

Set rsRep = New Recordset
Set rsRep.ActiveConnection = dbConn

'Create SQL Command
sSelect = "SELECT TICKETS.TICKET_NO, TICKETS.PU_TIME, CUSTOMER.FIRSTNAME, CUSTOMER.LASTNAME, TICKETS.LAST_TICK, TICKETS.PU_EMPL, SERVICES.SERV_ABBR, TICKETS.PU_DATE, TICKETS.STRIP_TAG, TICKETS.AMOUNT, DETAIL.QUANITY, COLORS.COLOR_DESC, COLORS.PATT_DESC "
sFrom = "FROM TICKETS, CUSTOMER, SERVICES, DETAIL, COLORS, TRANDETL "
sWhere = "WHERE TICKETS.CUST_NO = CUSTOMER.CUST_NO AND TICKETS.CUST_NO = CUSTOMER.CUST_NO AND TICKETS.SERV_CODE = SERVICES.SERV_CODE AND TICKETS.SERV_CODE = SERVICES.SERV_CODE AND TICKETS.TICKET_NO = DETAIL.TICKET_NO AND DETAIL.COLOR_NO = COLORS.COLOR_NO AND DETAIL.COLOR_NO = COLORS.COLOR_NO AND DETAIL.TICKET_NO = TRANDETL.TICKET_NO AND TICKETS.TICKET_NO = TRANDETL.TICKET_NO AND DETAIL.TICKET_NO = TRANDETL.TICKET_NO AND (TICKETS.PU_DATE IS NOT NULL)AND (TICKETS.PU_DATE = #" + txtDate.Text + "#) "
sOther = "ORDER BY TICKETS.TICKET_NO, TICKETS.PU_TIME;"

'Put it Together
strSQL = sSelect + sFrom + sWhere + sOther

rsRep.Source = strSQL
rsRep.CursorLocation = adUseServer
rsRep.CursorType = adOpenForwardOnly
rsRep.LockType = adLockReadOnly

rsRep.Open 'Open Record set with SQL Command


DEConn.Commands("SQLCommand&quot.CommandText = strSQL
DEConn.rsSQLCommand.Open
DEConn.rsSQLCommand.Requery

rptDailySales.Show 'Display Report



At first sight, I don't see anything wrong with your code...

 

[np3il]

The Database in use is Access database.

Thanks B-)

 

[np3il]

I tried the suggestion you mentioned and I still do not obtain the correct data from the Access database.

Does the DEConn.rsSQLCommand.Requery command needs any other settings activated or set??

How can I get the desired data from the database using the showm SQL command ???

Can this be done???

What am I doing wrong ???

Thanks B-)

 

[VBmim]

Hello np3il

Sorry for my late response...

I can't see anything wrong in your code...

I suggest maybe you try to hard code a query with a data criteria and see if this comes out right.

Also be sure that the 'DataSource' property of your report is the name of your data environment and that the 'DataMember' property of your report is set to the name of your command in your data environment.

greetz

Mim

 

[AndyWatt]

I don't know if this is significant but should

DETAIL.QUANITY

read

DETAIL.QUANTITY

I've spent many wasted hours searching for a non-existent error. Of course I _never_ mis-spell!



Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"Why does your program keep showing error messages every time something goes wrong?"

 

[AndyWatt]

I've also noticed that a few of the expressions are duplicated in the WHERE clause:
...
TICKETS.CUST_NO = CUSTOMER.CUST_NO AND
TICKETS.CUST_NO = CUSTOMER.CUST_NO AND
TICKETS.SERV_CODE = SERVICES.SERV_CODE AND TICKETS.SERV_CODE = SERVICES.SERV_CODE AND TICKETS.TICKET_NO = DETAIL.TICKET_NO AND
DETAIL.COLOR_NO = COLORS.COLOR_NO AND
DETAIL.COLOR_NO = COLORS.COLOR_NO AND
DETAIL.TICKET_NO = TRANDETL.TICKET_NO AND
TICKETS.TICKET_NO = TRANDETL.TICKET_NO AND
DETAIL.TICKET_NO = TRANDETL.TICKET_NO AND
...

I may be wrong but I'd imagine that this would slow things down a bit. You might consider using INNER JOIN to move the join expressions away from WHERE to FROM clause:

strSQL = "SELECT t.Ticket_No, t.pu_Time, c.FirstName, " _
& "c.LastName, t.Last_Tick, t.pu_Empl, s.Serv_Abbr, " _
& "t.pu_Date, t.Strip_Tag, t.Amount, d.Quanity, " _
& "o.Color_Desc, o.Patt_Desc " _
& "FROM ((((Tickets t INNER JOIN Customer c ON " _
& "t.Cust_No = c.Cust_No) INNER JOIN Services s ON " _
& "t.Serv_code = s.Serv_Code) INNER JOIN Details d ON " _
& "d.Ticket_No = t.Ticket_No) INNER JOIN Colors o ON " _
& "o.Color_No = d.Color_No) INNER JOIN TranDetl t ON " _
& "r.Ticket_No = t.Ticket_No " _
& "WHERE t.pu_Date IS NOT NULL AND t.pu_Date = #" _
& txtDate.Text & "# ORDER BY t.Ticket_No, T.pu_Time;"

Which, if I've understood correctly, does the same thing as above. Personally I find this easier to read, but some friends of mine don't. Guess it's what you're used to...



Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"Why does your program keep showing error messages every time something goes wrong?"

 

[np3il]

Hello VBmim,

It only works if I change the criteria on the Access side and then run my program, BUT I need to do this from my program NOT Access.

I modified the SQL command by copying from the Access SQL Query option, but still no good.

Help B-)

 

[VBmim]

Sorry np3il, I'm getting out of ideas here...

The last thing I would check is the format of your date. With this I mean: must the date be in a mm-dd-yyyy or dd-mm-yyyy or dd/mm/yyyy or...

 

[np3il]

Hello VBmim,

The date format is not the problem. The problem is how do I get the data generated by DEConn.rsSQLCommand.Requery into the report.

Up until DEConn.rsSQLCommand.Requery is ok ... I verified the data in the Immediate view window in VB6 and the PU_DATE is the correct date. But when the rptDailySales.Show is executed I get all the data in the database instead of the data generated by DEConn.rsSQLCommand.Requery.

I hope I clarified the problem in question.

I have tried

rptDailySales.DataSource = DEConn.rsSQLCommand.Requery

and I get a "Complie error: Expected function or variable"

What is the correct way to assign a new DataSource at run-time (on the fly)???

Help B-)