Data report refresh problem

Hi.

I have been fighting with this for a week or so and tried all sorts of things - including info in old threads on this forum - but can't get it working.

I have a form that contains customer information and numerical fields including calculated results. These are connected to an mdb database through an ado control. Then I have a Data Report to allow the results of the calculation to be printed off for the customer. I have tried linking this report through both a DataEnvironment and ADO code.

The problem is that the report displays the correct data first time. Then if I close the report, change a field in the form and re-calculate the results, then hit the print button again it still shows the old data. If I then close the report, then hit the print button again, it shows the right info. I don't actually have to close the form, just the report.

Below is the code I have in the print button. It is currently set to run from the coded ADO object rather than the DataEnvironment.

Any help will be greatly appreciated.

Thanks


Private Sub cmdPrint_Click()

Dim dblCust As Double
Dim strPadd As String
Dim dteDate As Date

adoResultsold.Recordset.Update

dblCust = txtcustnum(0).Text
strPadd = txtPaddockID(7).Text
dteDate = txtDate(10).Text

With cnnReport
If cnnReport.State = 0 Then

.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=D:\Sap Nitrate Program\sap.mdb"
.Open
End If

End With

With cmdReport
.ActiveConnection = cnnReport
.CommandType = adCmdText
.CommandText = "SELECT * FROM customer, results, " & _
"comment WHERE results.custnum = customer.custnum and customer.custnum = " & _
"" & dblCust & " AND results.paddockid = '" & strPadd & "' " & _
"AND comment.id = results.commentid"

End With

Set rstReport = cmdReport.Execute

Set drptResults1.DataSource = Nothing
drptResults1.DataMember = ""

cmdReport.Execute
rstReport.Requery

Set drptResults1.DataSource = rstReport

If rstReport.RecordCount <> 0 Then

drptResults1.Refresh
drptResults1.Show

Else
MsgBox "No Data"
End If
rstReport.Close
cnnReport.Close

End Sub

Hi

Suppose the command name you used in the report is Cmd1.
Before saying drptResults1.Refresh, insert the following line.

Code:

---

drptResults1.cmd1

---

This will solve your problem.

Satya

I have the same problem.

here is the code:

Private Sub mnuFilePrint_Click()
'Check dates first
If IsDate(txtFrom.Text) = False Then
intMsg = MsgBox("Check date if it's correct", vbOKOnly, Empty)
txtFrom.SetFocus
Exit Sub
End If

If IsDate(txtTo.Text) = False Then
intMsg = MsgBox("Check date if it's correct", vbOKOnly, Empty)
txtTo.SetFocus
Exit Sub
End If
Me.MousePointer = 11

'-------------------------------------------------------------------------------------------------------------------------
'Check first if there is a record to print
With datDummy
'strTo = txtTo.Text
sSQL = ("SELECT * FROM [Receiving Table] WHERE Received BETWEEN #" & txtFrom.Text & "# AND #" & txtTo.Text & "# ORDER BY Received")
.RecordSource = sSQL
.Refresh

'If no record is found
If .Recordset.RecordCount = 0 Then
intMsg = MsgBox("Check date if it's correct", vbOKOnly + vbExclamation, Empty)
Me.MousePointer = 0
Exit Sub
End If
End With
'-------------------------------------------------------------------------------------------------------------------------
'Refresh datDummy to reflect txtFrom and txtTo

With datDummy
'strTo = txtTo.Text
sSQL = ("SELECT * FROM [Receiving Table] WHERE Received BETWEEN #" & txtFrom.Text & "# AND #" & txtTo.Text & "#")
.RecordSource = sSQL
.Refresh
End With


'-------------------------------------------------------------------------------------------------------------------------
'Tranfer to Temporary Items 3 for printing

'datDummy.Recordset.MoveFirst

Do While datDummy.Recordset.EOF = False
With datTemp3.Recordset
.AddNew
![RR No] = lblRRNo
!Received = lblRecv
!Supplier = lblSupp
![Invoice No] = lblInvNo
!Description = lblDesc
!Quantity = lblQty
![Price/Item] = lblPriceItem
![Quantity x Price] = lblQtyPrice
.Update
datDummy.Recordset.MoveNext
End With
Loop




'-------------------------------------------------------------------------------------------------------------------------
'Print using data report
If Receiving.rsRecvSub.State = adStateOpen Then Receiving.rsRecvSub.Close

Receiving.Commands("RecvRep").CommandType = adCmdText
Receiving.Commands("RecvRep").CommandText = "SELECT * FROM [Temporary Items 5] ORDER BY [RR No]"

'reopen the commands
'Receiving.Commands ("RecvRep")

'label the date
With RecvRep.Sections("Section4")
.Controls("lblDateF").Caption = frmRecvRep.txtFrom
.Controls("lblDateT").Caption = frmRecvRep.txtTo
End With
RecvRep.Show
RecvRep.Refresh
Call ClearItems 'for next batch
'-------------------------------------------------------------------------------------------------------------------------
'Code Backup for printing

'Dim sLine As String
'Dim iIndex As Integer
' With datRecvRep.Recordset
' .MoveFirst
' While Not .EOF
' sLine = ""
' For iIndex = 0 To .Fields.Count - 1
' sLine = sLine & Chr(9) & .Fields(iIndex)
' Next
' Printer.Print Mid$(sLine, 2)
' .MoveNext
' Wend
'End With
'Printer.EndDoc
'-------------------------------------------------------------------------------------------------------------------------
Me.MousePointer = 0
End Sub