Solved: remove record from current recordset

[Movian]

Hi,
I am trying to do some advanced searching to create lists based upon multuiple checks from both one to one and one to many table relations. I have given up trying to perform this with a single SQL query and am now creating a base query to generate a records set. I will then use that recordset and VBA to loop through the other searches. However i want to be able to remove an item from my recordset (myrs) not delete it from the table just remove it from the current recordset variable. So that when i have finished all of my additional checks. I can output the contents of the recordset to a csv file... anyone know how to do this ? is this possible ?

so for example say i create a recordset based on all the people aged older than 25.

myrs.open "SELECT * FROM tblapatient where age > 25", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
say this returned 5 records in my recordset (myrs) i then want to check against a one 2 many table to see if they have a record that has both a field that says "Total Cholesterol" and if that same record had a result field greater than 300

and my code found that records 3 and 5 in myrs matched these criteria. I want to remove 1,2 and 3 from myrs and then export the remaining records in myrs to a text file...

hopefuly thats clear enough. Any help is apriciated.

[mohanvijay]

I think if all tables in same database you can do that by single query

[Movian]

I have been trying to do everything i need using a single SQL statement however the requirements are quite strict so a single SQL search will not do.

what i am trying to do now is do as much as i can with the SQL then finish up with the VBA.
Is there no way to soft remove a record from a recordset (just from the currect recordset and not from the table?) or mabye create a subset recordset based on the first recordset ?......

[mohanvijay]

did you try "Filter" property in ADODB.Recordset

[Movian]

that looks like its essentialy an additional WHERE clause.

as mentioned due to the complexity of the scenario i have to go through programticaly and remove records at each step till i am left with a list. It looks like there is no set way to do this so i will try and come up with a work around.

i apriciate the input :)

[Tommy]

Can you select into a temp table and remove from there the records you do not want?

[Movian]

Well the solution i am trying to work on is i added some flag fields to the main patient table. Then i create a recordset based on the basic SQL query. Then go through in code and check the flags that meet the advanced criteria.

However im having some issues with an error message that is not making a whole lot of sense to me.

the error im getting is "Row cannot be located for updating. Some values may have been changes since it was last read."

This is a dev DB no one else is making changes and theo nly changes i am making don't affect the primary key. this is hapening on rs.update.

As usual any help is apriciated.


Private Sub Command27_Click()
Dim rs As New ADODB.Recordset, rs2 As New ADODB.Recordset, counter As Integer
Dim splitarray As Variant, flag As Boolean, splitt As Variant
' remove all previous report mark flags
rs.Open "SELECT * FROM tblPatient WHERE not ReportMark = 0", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

While Not rs.EOF
rs("ReportMark") = 0
rs("ReportText") = Null
rs("OrderOn") = Null
rs("ordervalue") = Null
rs.Update
rs.MoveNext
Wend
rs.Resync
rs.Close

Set rs = Nothing
'create base recordset created from 1 to 1 relationship SQL query
rs.Open CStr(Me.txtStatement), CurrentProject.Connection, adOpenStatic, adLockOptimistic

If Not IsArrayEmpty(VBarray) Then
Do While Not rs.EOF
For counter = 0 To UBound(VBarray)
splitarray = split(VBarray(counter), "|")
rs2.Open "SELECT * FROM [" & splitarray(0) & "] WHERE [" & splitarray(1) & "] = '" & splitarray(2) & "'", CurrentProject.Connection, adOpenStatic, adLockOptimistic
flag = False
Do While Not rs2.EOF
Select Case splitarray(4)
Case "="
If rs2(splitarray(3)) = splitarray(5) Then
flag = True
End If
Case ">"
If CDbl(rs2(splitarray(3))) > CDbl(splitarray(5)) Then
flag = True
End If
Case "<"
If rs2(splitarray(3)) < splitarray(5) Then
flag = True
End If
Case ">="
If rs2(splitarray(3)) >= splitarray(5) Then
flag = True
End If
Case "<="
If rs2(splitarray(3)) <= splitarray(5) Then
flag = True
End If
Case Else
End Select
If flag = True Then
Exit Do
Else
rs2.MoveNext
End If
Loop
rs2.Close
Set rs2 = Nothing
Next

If flag = True Then
rs("ReportMark") = True
splitt = split(Me.txtStatement, "WHERE")
If UBound(splitt) > 0 Then
splitt = split(splitt(1), ".")
splitt(1) = CStr(left(splitt(1), Len(splitt(1)) - 1))
splitt(1) = CStr(right(splitt(1), Len(splitt(1)) - 1))

rs("ReportText") = splitt(1)
End If

If "[" & splitarray(0) & "].[" & splitarray(3) & "]" = Me.Combo28 Then
rs("ORDERON") = splitarray(2)
rs("Ordervalue") = splitarray(5)
Else

If Not IsNothing(rs("ReportText")) Then
rs("ReportText") = rs("ReportText") & "|" & splitarray(2) & " " & splitarray(4) & " " & splitarray(5)
Else
rs("ReportText") = rs("ReportText") & splitarray(2) & " " & splitarray(4) & " " & splitarray(5)
End If
End If
rs.Update
End If
rs.MoveNext
Loop
Else
Do While Not rs.EOF
rs("ReportMark") = 1
If Not IsNothing(Me.Combo28) Then
splitt = split(Me.Combo28, ".")
splitt(1) = CStr(left(splitt(1), Len(splitt(1)) - 1))
splitt(1) = CStr(right(splitt(1), Len(splitt(1)) - 1))
rs("OrderON") = splitt(1)
rs("ordervalue") = rs(splitt(1))
End If
rs.Update
rs.MoveNext
Loop
End If

On Error Resume Next
rs.Close
Set rs = Nothing
rs.Open "SELECT * FROM tblPatient WHERE ReportMark = 1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
MsgBox rs.RecordCount
rs.Close
Set rs = Nothing
DoCmd.OpenReport "REPORTPATIENTLIST", acViewPreview
End Sub

[Tommy]

You seem to be stepping through the rs2 recordset but only stepping through rs after the rs2 is processed. It seems as though you should want to itterate through both of them at the same time.

Why not open rs2 as read only.

[Movian]

i will try changing rs2 to readonly.

rs is a list of patients. rs2 is a list of patient resulsts or items, for each patient i need to go through rs2. Then once i have gone through rs2 either mark or not mark the field in rs. Then move onto the next record in rs.

[Movian]

I have managed to simply my code and am sure i am on the right track.

I removed the select case and now simply open up a second recordset for a cross reference. If records are returned then it meets the criteria if no records are returned then it does not.

However i am still getting the same error message. Another thing to note is that the loop processes twice and then on the 3rd run is when it fails ( I setup a counter and a messagebox). So not sure why it would run fine twice then messup on the third.....

' remove all previous report mark flags
rs.Open "SELECT * FROM tblPatient WHERE not ReportMark = 0", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
While Not rs.EOF
rs("ReportMark") = 0
rs("ReportText") = Null
rs.Update
rs.MoveNext
Wend
rs.Close

'create base recordset created from 1 to 1 relationship SQL query
rs.Open CStr(Me.txtStatement), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do Until rs.EOF
If Not IsArrayEmpty(VBarray) Then
flag = True
For counter = 0 To UBound(VBarray)
splitarray = split(VBarray(counter), "|")

Select Case splitarray(4)
Case "LIKE"
rs2.Open "SELECT tblpatient.*, tbllabresults.* FROM tblpatient, [" & splitarray(0) & "] WHERE tblpatient.[Medicalid#] = '" & rs("MedicalID#") & "' AND [" & splitarray(1) & "] = '" & splitarray(2) & "' and [" & splitarray(3) & "] LIKE '%" & splitarray(5) & "%'", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
Case Else
rs2.Open "SELECT tblpatient.*, tbllabresults.* FROM tblpatient, [" & splitarray(0) & "] WHERE tblpatient.[Medicalid#] = '" & rs("MedicalID#") & "' AND [" & splitarray(1) & "] = '" & splitarray(2) & "' and [" & splitarray(3) & "] " & splitarray(4) & " " & splitarray(5), CurrentProject.Connection, adOpenKeyset, adLockReadOnly
End Select

If rs2.RecordCount < 1 Then
flag = False
End If

rs2.Close
Next

If flag = True Then
rs("ReportMark") = 1
If "[" & splitarray(0) & "].[" & splitarray(3) & "]" = Me.Combo28 Then
rs("ORDERON") = splitarray(2)
rs("Ordervalue") = splitarray(5)
Else
If Not IsNull(rs("ReportText")) Then
rs("ReportText") = rs("ReportText") & "|" & splitarray(2) & " " & splitarray(4) & " " & splitarray(5)
Else
rs("ReportText") = rs("ReportText") & splitarray(2) & " " & splitarray(4) & " " & splitarray(5)
End If
End If
End If
Else
rs("ReportMark") = 1
End If
rs.Update ' This is where i get the error
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

DoCmd.OpenReport "REPORTPATIENTLIST", acViewPreview

[Movian]

After much trial and error i discovered that even though i was opening RS2 as a read only recordset. Because i had the same table opened in both RS and RS2 it was causing problems. So i removed the tables from the Origional SQL statement so that they are only opened as part of the rs2. This stops cross referencing in both recrodset and solved my problem.

Not only that but my new complex report generation system is now working beutifly and can perform complex searches such as

· Demographics: Patient age < 50 years old
· Problem: Coronary artery disease (CAD)
· Medication: Lisinopril
· Lab Results: Total cholesterol > 200 mg/dl
· Lab Results: HDL-cholesterol < 40 mg/dl
· Lab Results: LDL-cholesterol > 100 mg/dl
· Lab Results: Triglycerides > 150 mg/dl


all items anded together with lab results being a one to many with the test name being a field, and the result being seperated.

Thanks to all who helped.