Check for specific variable results on previous records? 1

[ad2]

Hi,

I have a data entry form for quarterly test results. There are 8 variables.
If variable TH2 was ever positive on a previous test, even if it is negitive on the current test, the patient must have an additional set of tests done.


So what I need to code somehow, when patient X has a new record entered - check if TH2 was ever positive on a previous record and display a message box
warning.

I've created a query that pulls patient ids and TH2 - criteria is if TH2 has a value of 1. But not sure how to have the data entry form check for specific patient id and TH2 value.

Any help would be great.

Thanks

 

[fneily]

You can change your query to use the inputted PatientID as criteria. On the criteria row under PatientID in your query you can put Forms![FormNameofEntry]![PatientID]. (same row as your 1 test to create an AND condition) Then on the AfterUpdate event of PatientID on the form, Run the query which will pick up the PatientID and create a recordset of just that PatientID and TH2 with a value of 1, if any. Then you can open a recordset using DAO to test for any records. Obviously if the recordset is empty, then there are no TH2 with a value of 1 for that patient. And, of course you can send out a msgbox saying if there was a positive response or not.

 

[Golom]

Sounds a bit odd.

If a patient was ever positive on a test then, by your description, any test result thereafter would require another test ... even if he had 100 negative tests after the positive test. Is that how it works?

Just making up names here ...

SQL = "Select * From SomeTable " & _
      "Where PatientID = '" & SomeFormField & "' AND " & _
      "TH2Result = 'Positive'"
Set SomeRecordset = CurrentDB.Openrecordset(SQL)
If SomeRecordset.EOF Then
    ' No Positive Tests
Else
    ' Positive Test Found
End If
SomeRecordset.Close
Set SomeRecordset = Nothing

 

[fneily]

Or use Dlookup.(just thought some more)
Private Sub PatientID_AfterUpdate()
Application.Echo False
DoCmd.SetWarnings False
If DLookup("[PatientID]", "[Patient_Table_query]", "[TH2] = 1") Then
MsgBox ("Patient already tested positive")
Else
MsgBox ("Patient has negative readings")
End If
Application.Echo True
DoCmd.SetWarnings True
End Sub

 

[fneily]

Golom - here's an example: dealing with junkies in the judicial system. That's how it works. Especially if they are expecting a child. I used to work for a narcotics unit, that's how I got my example.

 

[ad2]

THANKS fneily, your Dlookup is just what I needed, works great.

And thanks also to Golom for the input. You both rock!