end looping continued

[Stefan]

Here is my code

Set db = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "tbl_McareDX", db, adOpenDynamic, adLockOptimistic, adCmdTable
'rs.Find "rs.fields.item("DX")= Diagnosis"

rs.MoveFirst
Do While Not rs.EOF

If Diagnosis = rs.Fields.Item("DX") Then Approval = "YES"
If Diagnosis = rs.Fields.Item("DX") Then Exit Do
If Diagnosis <> rs.Fields.Item("DX") Then Approval = "NO"
rs.MoveNext

Loop

Me!approved = Approval

Basically I am trying to have an unbound text box on a report print YES if
the variable Diagnosis is contained within one of the records of the DX field
of tbl_McareDX

Currently it is always returning NO unless the Diagnosis matches the last
record in DX field

I tried using the find statement commented out above and using the nomatch
property but I keep getting an error.

Any help would be greatly appreciated.

 

[tina]

well, the logic looks correct to me. i might use a single If statement,
instead of three, and just set the "NO" value once, as

Me!approved = "NO"
Do While Not rs.EOF
If Diagnosis = rs.Fields.Item("DX") Then
Me!approved = "YES"
Exit Do
End If
Loop

but the logic is the same. i'm not too familiar with the ADO syntax (i
normally use DAO), but if the code is not erring out then presumably there's
no problem with the syntax. have you tried stepping through the code to see
what values are being compared in each pass through the loop?

hth

 

[GregK]

Write a Parameter Query "MyQuery"
PARAMETERS pDX Text ( 255 );
SELECT Count(*) AS Expr1
FROM tblMCare
WHERE (((tblMCare.DX)=[pDX]));

The code becomes...
dim strDiagnosis as string
dim fApproval as Boolean
dim db as database
dim qdf as querydef
dim rs as recordset

set db = CurrentDB
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameter(0) = strDiagnosis
set rs = qdf.OpenRecordset
fApproval = ( rst.Field(0) > 0)



The above assumes you Diagnosis contains a text value
This resolves the issue by doing away with the loop, and will run
significantly faster

Regards GregK