PRINT CURRENT RECORD OF DATAGRID ON DATAREPORT 4

[Hiccup]

I have an Adodc with a connected DateGrid on a form for displaying the records of an Access 2K mdb Table. The form also contains a Command Button to print the currently selected record on the DataGrid on to a DataReport called EmployeeReport.

Anyone know the code I should add to the Command Button so that when clicked, only the current record highlighted on the DataGrid will print out on the EmployeeReport?

Thanks in advance!

 

[CCLINT]

This is out of my head, so watch out for syntax errors...

    Dim rsClone As ADODB.Recordset
    Set rsClone = Adodc1.Recordset.Clone
    rsClone.Filter = "ID = " & Adodc1.Recordset.Fields("ID").Value
    Set DataReport1.DataSource = rsClone

The field "ID" is a unique field.
If it isn't, then of course you will get all records with this ID.

 

[Hiccup]

Thanks, CCLINT. Looks like it should work to me; I'll give it a try. Thanks, here's another star!

 

[CCLINT]

Thanks you!

 

[Hiccup]

Thanks CCLINT,

Unfortunately I did get a syntax error; the DataSource was highlighted.

The DataReport is connected via a DE Connection to the "Personnel" Table of the Access 2K mdb. The DE Connection name is LOA. My unique Field of the Table is EmployeeID and the DataReport name is EmployeeDataReport.

I tried working this into your code suggestion, but with no luck.

I'm not sure what the 3rd line of your code is doing. I.e.,

rsClone.Filter = "ID = " & Adodc1.Recordset.Fields ("ID&quot.Value

Thanks for any other suggestions!

 

[CCLINT]

>The DataReport is connected via a DE Connection

This, you didn't mention in your leading question.
The code I posted relys only on the Adodc that the grid is connected to.
I works fine then.
It is only a logic example anyways.

>I'm not sure what the 3rd line of your code is doing. I.e.,

Here you would change "ID" with "EmployeeID".
This filters the rs to the single, currently selected record.

I would recommend not using the DE for the report, which is based off of the data from a datagrid.
I wouldn't use the DE for anything, especially not in a multi-user environment.

 

[luisfelipe007]

cclint

a filter its slow in big tables? Can you do kind of ramTable on the fly with only that record(s) and pass it to the DataReport???

Regards,

--
Luis MX

 

[CCLINT]

>a filter its slow in big tables?

What do you mean? Using the rs.Filter method, or using criteria in the sql statement?

If you use a client side cursor, and the filter method, this will be faster than requery the db with criteria.
If there are no indexes available, then this will slow things down, but you can dynamically add an index to the structure in the client cursor by using the
rs.Fields(0).Properties("Optimize&quot = True
method.

Using this Optimize method has no affect on the db table/field and makes no changes on the actual indexes in the table, and is discarded when the recordset is closed. It is an internal ADO method which dynamically creates an index on a certain field in the client cursor.

>ramTable
This would be like using a client side cursor.
IMO, you should always use a client side cursor for a data report. The data report, or data grid, will pull in all the data returned by the recordset anyways.

 

[luisfelipe007]

I mean..

1 Create a ramtable (a table onthe fly) but im sure this cant be done, or can I ?

2 You pass all the fields in the row selected.

ramtable.addnew
ramtable!id = Adodc1.Recordset.Fields("id&quot.Value
ramtable!name = Adodc1.Recordset.Fields("name&quot.Value
ramtable!phone = Adodc1.Recordset.Fields("phone&quot.Value

then just

Set DataReport1.DataSource = ramtable

without any filter.


--
Luis MX

 

[CCLINT]

Ah..I know what you mean now. I call this a fabricated recordset. See my post in thread222-626165

I think Hiccup had been using this and said it was slow for the data report.
I would think it would work pretty fast for it, and have ran tests on this a while back and didn't see a problem with speed when used with the data report.

 

[Hiccup]

Thanks CCLINT. Sorry that I failed to mention the DE connection for the DataReport. I'll take your advice and not use the DE.

I've tried to create the DataReport using an Adodc Connection, but in the DataSource drop-down property of the DataReport, only the DE Connection displays, NOT my Adodc Connection. I manualy enter the Adodc into the DataSource field of the DataReport properties, but I always get a DataSource syntax error.

Can't figure out how to create a DataReport connected to my Access mdb via an Adodc in the DataReport's Property Window.

I used your code below, but it also bombs on the DataSource:

Private Sub Command1_Click(Index As Integer)
Dim rsClone As ADODB.Recordset
Set rsClone = Adodc1.Recordset.Clone
rsClone.Filter = "EmployeeID = " & Adodc1.Recordset.Fields("EmployeeID&quot.Value
Set DataReport1.DataSource = rsClone
DataReport1.Show
End Sub

 

[CCLINT]

>Can't figure out how to create a DataReport connected to my Access mdb via an Adodc in the DataReport's Property Window.

You have to set it dynamically.

Start anew just for testing purposes, so you can see how this works and nothing else is getting in the way.

Assumming you have a form (Form1) with an Adodc, or a recordset object variable, and this recordset is opened on a db table using a client side cursor, (or a fabricated recordset), and it contains data.

Add a new data report. Name it "DataReport1"

Add a text box to the details section and set it's DataField property to the name of a field (EmployeeID) in the recordset on form1.

Go to the code window for the data report and add the following:

Option Explicit
Private rsDataReport As ADODB.Recordset

Private Sub DataReport_Terminate()
    If Not rsDataReport Is Nothing Then
        If rsDataReport.State = adStateOpen Then rsDataReport.Close
    End If
    Set rsDataReport = Nothing
End Sub

Public Sub Display(rs As ADODB.Recordset, Optional vbModalType As VBRUN.FormShowConstants = vbModal)
    Set rsDataReport = Nothing
    Set rsDataReport = rs.Clone
    rsDataReport.Filter = "EmployeeID =" & rs.Fields("EmployeeID").Value

    Set Me.DataSource = rsDataReport
    
    Me.Show vbModalType
End Sub

Now, in the form with your datagrid add a command button named "Command1" and add the following proceedure:

Private Sub Command1_click()
    OpenReport
End Sub
Private Sub OpenReport()
   Dim rpt As DataReport1
   Set rpt = New DataReport1
   rpt.Display Me.Adodc1.Recordset, vbModal
   'or, using an ADO recordset object variable, here called m_rsADO
   'rpt.Display m_rsADO, vbModal
   Set rpt = Nothing
End Sub

 

[Hiccup]

Thanks a TON CCLINT!! It seems to be working GREAT!!

Rack up another star!

 

[luisfelipe007]

CCLINT

>See my post in Thread222-626165

Good, but is this recordset only at RAM?? or its created phisically in the DB ????

--
Luis MX

 

[CCLINT]

> or its created phisically in the DB

What db? Look at the code again. There IS no "db" except the recordset itself...Of course, it will automatically use ram, and temporary disk space as needed, but I don't know what else you are getting at.
Once the recordset object is closed and set to nothing, all the metadata and data is lost and gone, unless you first save it to a file using the rs.Save method.

 

[luisfelipe007]

oohhhh thank you, sorry I didnt see that in the code

Next time I'll check before asking.

heres your star

--
Luis MX

 

[Hiccup]

Thanks CCLINT for code suggestion above. It works fine on several of my forms, but a couple give me the following run-time error when the Commend Button is clicked: "3001 - Arguments are of the wrong type, are out of exceptable range or are in conflict with one another."

This is the line that is highlighted when I debug the error:

rsDataReport.Filter = "ClientID=" & rs.Fields("ClientID&quot.Value



The complete code I'm using for the DataReport is as follows:

Option Explicit
Private rsDataReport As ADODB.Recordset

Private Sub DataReport_Terminate()
If Not rsDataReport Is Nothing Then
If rsDataReport.State = adStateOpen Then rsDataReport.Close
End If
Set rsDataReport = Nothing
End Sub

Public Sub Display(rs As ADODB.Recordset, Optional vbModalType As VBRUN.FormShowConstants = vbModal)
Set rsDataReport = Nothing
Set rsDataReport = rs.Clone
rsDataReport.Filter = "ClientID=" & rs.Fields("ClientID&quot.Value
Set Me.DataSource = rsDataReport
Me.Show vbModalType
End Sub

CCLINT any ideas why this code works fine with some DataReport/Command Button setups, but not with others? I've reviewed the code many times on those setups that aren't working and enven though I've used the same code, I'm getting the 3001 run-time error on some of them.

Thanks for any suggestions!!