Format a Data Report

justus
10-25-2004, 04:54 AM
I am developing a VB Datareport.I get details from a table using a certain criteria.However i need to number the results on the data report. This means am only displaying all the infor on the report. A Sample is as follows.
No# Surname FirstName MiddleName Status
1. Kamau Justus Njoroge Married
2. Maina Simon Kamau Single

The problem is to display the No# Column on the data report and how to set it up so that it increases respective to the data report being populated.Please help urgently.
Regards
Justus

Dennis DVR
10-25-2004, 06:48 AM
I am developing a VB Datareport.I get details from a table using a certain criteria.However i need to number the results on the data report. This means am only displaying all the infor on the report. A Sample is as follows.
No# Surname FirstName MiddleName Status
1. Kamau Justus Njoroge Married
2. Maina Simon Kamau Single

The problem is to display the No# Column on the data report and how to set it up so that it increases respective to the data report being populated.Please help urgently.
Regards
Justus

I think you have a big problem :(

If you are using access database, you need to use 2 recordset, 1 for the query and the 2nd is disconnected recordset with the same structure as the first recordset but with additional field which is the no# field, but this process would take time since you need to pass the value of first recordset to the disconnected recordset row by row and update the no# field incremented by one.

If you are using SQL Server you need to create a temporary table with additional field which is the no# field and use the cursor variable to loop through the result of the query and saved it to the temporary table and update the no# field.

BountyBob
10-25-2004, 06:56 AM
My response is similar to Duane's

You either need to return the counter from the query itself or, as I've done in the attached example, used an existing field (EmpId) and changed its value in the report. This doesn't have to be an actual field. You could add a field to the query (say for example, Null AS DummyField in the select list).

The report changes this value to the value held in the variable "Counter".

Note: this code connects to the Northwind database using an ODBC connection.

Hope this helps,

Rob

justus
10-25-2004, 08:30 AM
Hi Guys,

This only works if the table itself contains the NO# column already. However note that am creating a query that is sorting out the details using a certain criteria.I have managed to use this query and sort out the info but suppose they are 1000 records and i select 100. I need to get the 78th or 98th from the vb report printout.
Please help with a sample code.The value in the column No# should increase as the data report gets populated.

Regards
Justus

Dennis DVR
10-25-2004, 08:52 AM
I already told you, create a disconnected recordset if you are using Access or use the cursor variable if you are using SQL Server.

OK here's a typical example of a disconnected recordset, just pay attention to the "No_ID" field that represents as the Record #. Basically the "No_ID" field is not included in the select statement because there's no No_ID field in the ledger table.

Here's the UN-TESTED Code.

Private Sub Print_Click()
Dim oRSquery As ADODB.Recordset
Dim rsBalance As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim xx As Integer
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Duane\My Documents\db1.mdb"
cnn.Open
Set oRSquery = New ADODB.Recordset
oRSquery.Open "SELECT [Date],Debit,Credit FROM Ledger", cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not oRSquery.EOF And Not oRSquery.BOF Then
Set rsBalance = New ADODB.Recordset
rsBalance.Fields.Append "Date", adDBDate, 8
rsBalance.Fields.Append "Debit", adCurrency, 10
rsBalance.Fields.Append "Credit", adCurrency, 10
rsBalance.Fields.Append "No_ID", adBigInt, 4
rsBalance.Open
Do While Not oRSquery.EOF
rsBalance.AddNew
rsBalance.Fields("No_ID").Value = xx + 1
rsBalance.Fields("Date").Value = oRSquery.Fields("Date").Value
rsBalance.Fields("Debit").Value = oRSquery.Fields("Debit").Value
rsBalance.Fields("Credit").Value = oRSquery.Fields("Credit").Value
rsBalance.Update
xx = xx + 1
oRSquery.MoveNext
Loop
Set DRTutorial.DataSource = rsBalance 'Bind the report to recordset
With DRTutorial.Sections("Details") 'loop through the controls in the Detail section
'There's no count 0 it always start with 1
'by looping through this section we can access all the controls that we dragged in to this section
For xx = 1 To .Controls.Count
If TypeOf .Controls(xx) Is RptTextBox Then 'is it TextBox Control
.Controls(xx).DataField = rsBalance.Fields(xx - 1).Name 'bind it
End If
Next
End With
DRTutorial.ReportWidth = 9000 'set the size of report
DRTutorial.Refresh 'refresh the data report to update the changes
DRTutorial.WindowState = vbMaximized
DRTutorial.Show 'show the report
rsBalance.Close
End If
Set rsBalance = Nothing
oRSquery.Close
Set oRSquery = Nothing
cnn.Close
Set cnn = Nothing
Set DRTutorial = Nothing
End Sub

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum