Displaying field names in data report

[Shilpa Jain]

Hi All,

I want to display the name of the fields in the datareport but unable to get how to display the same

One more question. I have 3 checkboxes and 3 comboboxes on my main form from where I have to generate the report. How do I apply the combinations for them to generate the report. Right now, I have generated the report by hard coding one of the values present in one of the combo boxes.

Here is the code:

Option Explicit 

Dim oConn As ADODB.Connection 
Dim oRSReport As ADODB.Recordset 
Dim catDB As ADOX.Catalog 
Dim tbl As ADOX.Table 

Private Sub DataReport_Initialize() 
  Dim sSQL As String 
  Dim sCMD As String 
  Dim oRSSource As ADODB.Recordset 
  Dim oRSDestination As ADODB.Recordset 
  Dim c As Integer 
  Dim l As Integer 
  Dim f As Long 
     
  Set oConn = New Connection 
  oConn.CursorLocation = adUseClient 
  oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
                           "Data Source=D:\Tool\P_and_E\P_and_E.mdb;" & _ 
                           "Persist Security Info=False" 

  oConn.Mode = adModeReadWrite 
  oConn.Open 
     
  Set catDB = New ADOX.Catalog 

  catDB.ActiveConnection = oConn 
     
  Set tbl = New ADOX.Table 
  With tbl 
     
    .Name = "TempTable3" 

    Set .ParentCatalog = catDB 
    With .Columns 
      'creating the fields in the temptable, using the c and l values 
      For c = 1 To 2 
        For l = 1 To 4 
           
          .Append "L" & CStr(l) & "C" & CStr(c), adVarWChar 
        
          .Item("L" & CStr(l) & "C" & CStr(c)).Attributes = adColNullable 
        Next 
      Next 
    End With 
  End With 

  catDB.Tables.Append tbl 

  'this sql statement will be are query for getting the data we want into a recordset 
  sSQL = "SELECT Emp_No AS L1, Name AS L2, RBAC_Status as L3,RBAC_Date as L4 from Access_Info where RBAC_Status = 'Completed'" 
    
  Set oRSSource = New ADODB.Recordset 
  oRSSource.Open sSQL, oConn, adOpenForwardOnly, adLockOptimistic 
     

  sSQL = "SELECT * FROM TempTable3" 
  Set oRSDestination = New ADODB.Recordset 
  oRSDestination.Open sSQL, oConn, adOpenStatic, adLockOptimistic 
     
'Putting data into fields 
  With oRSSource 
    Do Until .EOF 
      oRSDestination.AddNew 
      f = 0 
      For c = 1 To 2 
        For l = 1 To 4 
          oRSDestination.Fields(f).Value = .Fields("L" & l).Value 
          f = f + 1 
        Next 
        .MoveNext 
        If .EOF Then Exit For 
      Next 
      oRSDestination.Update 
      If .EOF Then Exit Do 
     Loop 
    .Close 
  End With 
  oRSDestination.Close 
     
  Set oRSDestination = Nothing 
  Set oRSSource = Nothing 
     
  'creating the recordset to bind to the datareport 
  sSQL = "SELECT * FROM TempTable3" 
  Set oRSReport = New ADODB.Recordset 
  oRSReport.Open sSQL, oConn, adOpenForwardOnly 
  Set DataReport1.DataSource = oRSReport 
     
End Sub 

Private Sub DataReport_Terminate() 
  'this will delete temporary table 
  catDB.Tables.Delete tbl.Name 

  Set tbl = Nothing 
  Set catDB = Nothing 
End Sub

Instead of

RBAC_Status = 'Completed'

present in sql query I have to use 'RBACCmb.text' but I am getting an error while using this value as it is not interlinked with data report. How can I do this?

Please guide me.

regards,
Shilpa