showing detail in the data report

[firoz.raj]

sir i want to display Department, Unit, Material Description ,Requested by ,....
etc one time But is is comming two times i don't know why it is comming one time if use group by clause using Having then got error can anybody help me

Private Sub Command4_Click()
   Dim mr As Integer
   mr = InputBox("enter a mr no")
   Set rs = New ADODB.Recordset
   rs.Open "select * from MR  where req_no = " & CStr(mr), con, adOpenDynamic, adLockOptimistic
If Not rs.EOF Then
With DataReport2.Sections("section1").Controls
  .Item("text1").DataField = rs.Fields("productname").Name
  .Item("text2").DataField = rs.Fields("qty").Name
  .Item("text3").DataField = rs.Fields("unit").Name
  .Item("text4").DataField = rs.Fields("dept_name").Name
  .Item("text5").DataField = rs.Fields("job_no").Name
  .Item("text6").DataField = rs.Fields("cost_centre").Name
  .Item("Text7").DataField = rs.Fields("Emp_name").Name
  .Item("text8").DataField = rs.Fields("mr_date").Name
  .Item("text9").DataField = rs.Fields("del_point").Name
  .Item("TEXT10").DataField = rs.Fields("MANAGER").Name
  .Item("TEXT11").DataField = rs.Fields("REQ_NO").Name
   End With
  Set DataReport2.DataSource = rs
  DataReport2.Orientation = rptOrientLandscape
  DataReport2.Refresh
  DataReport2.Show vbModeless
End If
End Sub

[Nightwalker83]

Wrong forum! The "Forum Feedback" section is to ask the staff members about the running of the forum. This question belongs in either the visual basic or database development sections of the forum.

[firoz.raj]

Wrong forum! The "Forum Feedback" section is to ask the staff members about the running of the forum. This question belongs in either the visual basic or database development sections of the forum.

can you tell me the link of url for posting vb6 question ?

[MartinLiss]

Moved.

[wes4dbt]

It looks like your report is setup to do grouping. You can't do grouping grouping using an ADO recordset as the datasource. You have to use a MSDataShape.

Here is a sample from one of my applications,

cFinalCond = "SHAPE {SELECT tags.*,us.*,iif(trim(tags.status)='I','Invoiced','Not Invoiced') as invstatus from tags inner join users us on us.userid=tags.userid where " & cWhere & " order by " & cOrderby & " } AS Tag " & _
                    "APPEND ((SHAPE {SELECT * From tagsdetail order by line} As Command1) " & _
                    "RELATE tagnumber To tagnumber)"
                     
        'Open a DATA Shape Connection For Reports
        With conReport
            .Mode = adModeShareDenyNone
            .Provider = "MSDataShape" 'Microsoft Provider for Data Shaping
            .ConnectionString = "Data Provider = Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source = " & conDb
            .Open
        End With
        
        If (conReport.State And adStateOpen) > 0 Then
            With rsData
                .ActiveConnection = conReport
                .LockType = adLockReadOnly
                .CursorType = adOpenStatic
                .Open cFinalCond
            End With
        End If
        'Set the new Data source of Report
        Set rptTBfull_shape.DataSource = rsData
        rptTBfull_shape.Sections("Section6").Controls("lblConame").Caption = frmMain.Adodc1.Recordset!Name
        rptTBfull_shape.Sections("Section6").Controls("lblphone").Caption = frmMain.Adodc1.Recordset!Phone
        rptTBfull_shape.Show vbModal
        
        rsData.Close
        conReport.Close
        Set rsData = Nothing
        Set conReport = Nothing

Depending on how this app will be used, it may be easier to use a dataenvironment as the reports datasource.

[Nightwalker83]

can you tell me the link of url for posting vb6 question ?

A staff member will move he thread to the correct forum if needed.

[firoz.raj]

can you help me actually i think MSDataShape is new kindly if you don't mind can you edit into MSDataShape code

Private Sub Command4_Click()
  X = InputBox("enter a mr no")
  If mr = "" Then'  MsgBox "it cannot be blank", vbInformation, Me.Name
   End If
   Set rs = New ADODB.Recordset
   rs.Open "select * from MR  where req_no = " & CStr(mr), con,   adOpenDynamic, adLockOptimistic
   If Not rs.EOF Then
   With DataReport2.Sections("section1").Controls
  .Item("text1").DataField = rs.Fields("productname").Name
  .Item("text2").DataField = rs.Fields("qty").Name
  .Item("text3").DataField = rs.Fields("unit").Name
  .Item("text4").DataField = rs.Fields("dept_name").Name
  .Item("text5").DataField = rs.Fields("job_no").Name
  .Item("text6").DataField = rs.Fields("cost_centre").Name
  .Item("Text7").DataField = rs.Fields("Emp_name").Name
  .Item("text8").DataField = rs.Fields("mr_date").Name
  .Item("text9").DataField = rs.Fields("del_point").Name
  .Item("TEXT10").DataField = rs.Fields("MANAGER").Name
  .Item("TEXT11").DataField = rs.Fields("REQ_NO").Name
   End With
   Set DataReport2.DataSource = rs
   DataReport2.Orientation = rptOrientLandscape
   DataReport2.Refresh
   DataReport2.Show vbModeless
   End If
  End Sub

[firoz.raj]

can anyone tell me how should i generate srno automatic in a crystal report.
i have written a code in formviewer but i don't know how should i genrerate
sr no. Kindly help me.

Dim crystal As CRAXDDRT.Application
Dim Report As CRAXDDRT.Report
Dim cparam As CRAXDRT.ParameterFieldDefinition
Dim cparams As CRAXDDRT.ParameterFieldDefinitions
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
Call loadreport
'Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
End Sub

Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Zoom 100
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth
End Sub
Private Sub loadreport()
    Set con = New Connection
    con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
   'DataPath = "\\asfserver\itp$\Product_tabletest.mdb"
    Set rs = New ADODB.Recordset
    Set crystal = New CRAXDDRT.Application
    Set Report = New CRAXDDRT.Report
    rs.Open "select * from MR", con, adOpenDynamic, adLockOptimistic
    Set Report = crystal.OpenReport(App.Path & "\" & "list2.rpt", 0)
 End Sub

[firoz.raj]

i have tried but got runtime error -2147217900(80040e14).

 Private Sub Command4_Click()
   Set rs = New adodb.Recordset
'  recordsql = "SELECT * From MR WHERE (((MR.Req_no)=" & Text12.Text & "));"
   recordsql = "shape {select * from mr where mr.req_no=" & Text12 & "} as mr compute mr, sum(mr.qty) by req_no"
   rs.Open recordsql, con, adOpenStatic, adLockOptimistic
   If Not rs.EOF Then
   With DataReport2.Sections("section1").Controls
  .Item("text1").DataField = rs.Fields("productname").Name
  .Item("text2").DataField = rs.Fields("qty").Name
  .Item("text3").DataField = rs.Fields("unit").Name
  .Item("text4").DataField = rs.Fields("dept_name").Name
  .Item("text5").DataField = rs.Fields("job_no").Name
  .Item("text6").DataField = rs.Fields("cost_centre").Name
  .Item("Text7").DataField = rs.Fields("Emp_name").Name
  .Item("text8").DataField = rs.Fields("del_point").Name
  .Item("text9").DataField = rs.Fields("mr_date").Name
  .Item("TEXT10").DataField = rs.Fields("MANAGER").Name
  .Item("Text11").DataField = rs.Fields("sug_vendor").Name
  .Item("text12").DataField = rs.Fields("Req_no").Name
   End With
   Set DataReport2.DataSource = rs
   DataReport2.Orientation = rptOrientLandscape
   DataReport2.Refresh
   DataReport2.Show vbModeless
   Do
   DoEvents
   Loop While DataReport2.AsyncCount > 0
   End If
   End Sub