[RESOLVED] Need help with datareport / dataenvironment

[Vovo]

Hi everyone,

I have a form that displays customers, in a combobox. You select one and click a command button.

it then generates another combobox with the month usage reports. you select a month to view and click a command button.

This brings up the datareport with the data for that time period.

This all works.

if you then select another month and click the command button it will display the same info again.
I have used debug.print, gotta love it, and it shows that the sql is different. But the report is still the same. this is how i change the command text.

DataEnvironment1.Commands.Item("Command2").CommandText = strsql
    DataEnvironment1.Commands.Item("Command2").Execute strsql

why doesn't it work.

[leinad31]

The datareport data-bound at design time? Try refreshing the data report; datareport1.refresh

[Vovo]

I have tried that, no luck. When I load the form and choose a month it works properly, its just when you try and select another month that is doesn't refresh.

Dim strsql As String
    strsql = "SELECT DISTINCT * FROM rpt_tsheet WHERE t_empnum= '" & frmTSheetSelect.cmbDomestic.Tag & "' AND t_id =" & frmTSheetSelect.cmbDomestic1.Tag & ""
    Debug.Print "CHECK " & strsql
   
    DataEnvironment1.Commands.Item("Command2").CommandText = strsql
    DataEnvironment1.Commands.Item("Command2").Execute strsql
        
    rptDomestic.Refresh

That is all my code on the report. Yes it is data bound.

[leinad31]

Try resetting the datasource property of the report.

[Vovo]

when i try this

rptDomestic.DataSource = DataEnvironment1

it tells me 'Method or data member not found'

[leinad31]

You need to set it to an ADO recordset. Try this

Set rptDomestic.DataSource = DataEnvironment1.Commands.Item("Command2").Execute(strsql)

If that doesn;t work then we'll use non-bound methods

[leinad31]

Or maybe you could zip it up along with DB (is the db large?)

Hard to come up with a solution cause we have no code we can refer to... and the databound properties only you can see their values.

[Vovo]

Thanks for your help so far, unfortunately I can't attach the code or the db - I could get into trouble :-(

[leinad31]

I usually don't use the dataenvironment cause its just a graphical wrapper for ado hence there's additional memory use. You can do something like this at run time.

Public Sub ShowDomesticReport()
'Dim conn As New ADODB.Connection  'assume already open
Dim rs As ADODB.Recordset
Dim rpt As New rptDomestic
Dim strSQL as String
 
   strSQL = "SELECT DISTINCT * FROM rpt_tsheet WHERE t_empnum= '" & _
      frmTSheetSelect.cmbDomestic.Tag & "' AND t_id =" & _
      frmTSheetSelect.cmbDomestic1.Tag & ""
 
On Error Goto Err_Handler
   Set rs = conn.Execute strsql
 
   'Header section labels and report caption
   With rpt.Section("Header")
      .Controls("lblCompany").Caption = Form1.lblCompany.Caption
      .Controls("lblAdd1").Caption = Form1.txtAddress1.Text
      .Controls("lblAdd2").Caption = Form1.txtAddress2.Text
   End With
   rpt.Caption = "Domestic Report"
 
   'Set datasource and bind recordset fields. 
   'Update fields and datareport control names to those you use
   Set rpt.DataSource = rs
   With rpt.Section("Details")
        .Controls("txtQuantity").DataField = "Quantity"    'where quantity is a field in the recordset
        .Controls("txtUnit").DataField = "Unit"
        .Controls("txtParticulars").DataField = "Particulars"
        .Controls("txtUnitPrice").DataField = "Unit Price"
        .Controls("txtAmount").DataField = "Amount"
   End With
   
   rpt.Show vbModal
   rs.Close
   Unload rpt
   Set rs = Nothing
   Set rpt = Nothing
   
   Exit Sub
 
Err_Handler:
   'your error handling code
End Sub

[Vovo]

leinad31, dude, you're the king!

Thanks that did the trick.

I rate you thus.