DATAREPEATER ... DOES ANYONE KNOW?????

[fordman]

We are having some issues with retrieving our information into our repeater. The problem is that we populate our repeater from a temporay table in SQL Server 7 that is created when a search command is issued. The information comes out of the main table, goes into the temp table and the repeater populates from that table. Whe we go to do another search the repeater seems to hold the previous information in temporary memory some where and keeps poulating with the first record search after the form is loaded. The only way to see the record is to exit the program and re-load it. The repeater will then populate with the record searched just before exiting. This happens even though the SQL table populates properly.

WHAT ARE WE DOING WRONG? HOW CAN WE DELETE THIS TEMPORARY MEMORY FOR THE NEXT SEARCH??????????

 

[shepherd]

Can you supply a sample of the code that creates the temporary table, and the code that calls the procedure?

 

[fordman]

Private Sub cmdNewVisit_Click()

If inputNurse > " " Then GoTo ContNewVisit
inputNurse = InputBox$("Treating Nurse Initials", "EMS", , 1000, 1000)


ContNewVisit:
Dim strBookmark As String
Dim sin2468 As String
Dim sum2468 As Integer
Dim sin1357 As Integer
Dim sinWrk As Integer
Dim sinsum As Integer
Dim intUserResponse As Integer
Dim strNewVisit As String
Dim buttonpressed As Variant
Dim i As Variant
Dim sinWork As String * 9
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim prm As New ADODB.Parameter
Dim drView As Variant
Dim Test As Long
Const conBtns As Integer = vbOKOnly + vbExclamation

dgType.Visible = False
dgArea.Visible = False
dgDiagnosis.Visible = False
dgJob.Visible = False
dgCause.Visible = False
dgTreatment.Visible = False
dgSide.Visible = False
dgNew.Visible = False
dgWSIB.Visible = False
dgSafe.Visible = False
dgDept.Visible = False
dgZone.Visible = False
dgAction.Visible = False
dgMedRestCode.Visible = False
dgStatus.Visible = False
dgMedD8.Visible = False

' deMV.cnn.Open
drpMedVisit.Enabled = False

cmdView.Visible = False
Start_over:

famEmpMasterInfo.Visible = False
drpMedVisit.Visible = False
framMedicalHeader.Visible = False
famRecMaint.Visible = False
famMessage.Visible = False

inputSINNo = InputBox$("SIN Number", "EMS", , 1000, 1000)
If Val(inputSINNo) < 1 Then Exit Sub
If Val(inputSINNo) < 100000000 Or Val(inputSINNo) > 999999999 Then
MsgBox &quot;S.I.N. out of range&quot;, vbOKOnly + vbInformation
GoTo Start_over
End If
sin2468$ = Mid(inputSINNo, 2, 1) + Mid(inputSINNo, 4, 1) + Mid(inputSINNo, 6, 1) + Mid(inputSINNo, 8, 1)
sum2468% = Val(sin2468$) + Val(sin2468$)
sin2468$ = Str(sum2468)
sinWrk = 0
For i = 1 To Len(sin2468)
sinWrk = sinWrk + Val(Mid(sin2468$, i, 1))
Next i
sin1357% = sinWrk% + Val(Mid(inputSINNo, 1, 1)) + Val(Mid(inputSINNo, 3, 1)) + Val(Mid(inputSINNo, 5, 1)) + Val(Mid(inputSINNo, 7, 1))
sinsum = (Fix((sin1357% + 9) / 10)) * 10:
If Val(Right$(inputSINNo, 1)) <> sinsum% - sin1357% Then
buttonpressed = MsgBox(inputSINNo, 2, &quot;this is NOT valid number&quot
GoTo Start_over
End If

With adoIRMaster.Recordset
.MoveFirst
strBookmark = .Bookmark
.Find &quot;SINNo='&quot; & inputSINNo & &quot;'&quot;
If .EOF Then
MsgBox &quot;Employee Not Found&quot;, vbOKOnly + vbInformation
GoTo Start_over
End If
End With

With adoMedAlert.Recordset
.MoveFirst
strBookmark = .Bookmark
.Find &quot;SINNo='&quot; & inputSINNo & &quot;'&quot;
If .EOF Then
Exit Sub
End If
End With

famEmpMasterInfo.Visible = True

deMV.cnn.Open
With cmd
.ActiveConnection = deMV.cnn
.CommandText = &quot;DROP TABLE MedTemp&quot;
On Error Resume Next
.Execute
End With
' deMV.cnn.Close

MsgBox &quot;it's dropped&quot;

txtIRMSINNo = inputSINNo
txtFields(0) = txtIRMSINNo

framMedicalHeader.Visible = True

With famMessage
.Top = 5880
.Visible = True
End With

deMV.cnn.Open
With cmd
.ActiveConnection = deMV.cnn
.CommandText = &quot;SELECT * INTO MedTemp FROM MedVisits WHERE SINNo = ? ORDER BY TDate DESC&quot;
.CommandType = adCmdText
End With


With drpMedVisit
.Enabled = True
.Visible = True
.Refresh
End With

If adoMedVisits.EOFAction Then
MsgBox &quot;No Medcial Record On File For This Employee&quot;
End If

With prm
.Direction = adParamInput
.Type = adInteger
.Value = inputSINNo
End With

cmd.Parameters.Append prm

Set rs = cmd.Execute

drpMedVisit.Refresh

deMV.cnn.Close
Set cnn = Nothing
Set Adodc1 = Nothing
Set drpMedVisit.RepeatedControl = Nothing

End Sub



Here's the code ... please remember that we have been iterating for a while so it may not be as clean as we want it.

Thanks for any help you can give!!!

 

[sidanshu]

hey fordman- unfortunately not the solution- just want to say that i have exactly same problem with data report in vb - that gets data through data environment from a emporary table and table gets populated through code- table always shows the updated data but report does not unless you exit and start again.so please let me know too if you found the solution and vice versa.i have my thread posted in vb and databases with subject 'data report does not refresh.' Good luck