Data report refresh problem

[craigjam]

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

[SatyaPhaniKumar]

Hi

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

drptResults1.cmd1

This will solve your problem.

Satya

[alvin777]

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