D-lookup to another query that has a left join (to allow editing) (1 Viewer)

[cmray58]

Background: I'm creating a clinical decision support tool in which a Query will provide doctors with a suggested decision for a patient's prescribed drug based on whether the patient's medical condition falls under the domain of the drug. I.e., if a patient is taking an anti-depressant and any of their diagnoses are Depression, then the output query will say "Approve" in the Suggestion column. This query is finalized and the IIF statement works. However, it utilizes a LEFT JOIN, so the provider cannot edit the final field in the query "Provider Decision" to denote their official decision to approve or deny the med.

Therefore, I'm trying to create another query and use a Dlookup to pull in the Suggestion field from the first query so the provider can edit the record. However, the Dlookup I'm using below is taking forever to run and ends up freezing the program. There's only ~530 entries for the Dlookup to search.

Suggested Decision: =Dlookup("[Suggestion]", "Query1", "[AUTH NBR]='" & [AUTH NBR] & "'")

 

[Ranman256]

Queries ARE the DLOOKUP. You don't need both.
Either use DLOOKUP to return your answer(on join)
Or
Use the joined query to return your answer.

 

[cmray58]

Queries ARE the DLOOKUP. You don't need both.
Either use DLOOKUP to return your answer
Or
Use the joined query to return your answer.

Query1, which contains the "Suggested Decision" IIF statement, required the use of a LEFT JOIN, which disables editing any records. How would you suggest I go about being able to edit these records while still utilizing this "Suggested Decision" value?

Here's the SQL of Query1:

SELECT PatientPrescriptions.[AUTH NBR], PatientPrescriptions.Date_Rx, IIf(IsNull([Cert]),"Review","Approve") AS Suggestion
FROM (PatientPrescriptions LEFT JOIN AllCerts_for_Patients ON (PatientPrescriptions.ID = AllCerts_for_Patients.ID) AND (PatientPrescriptions.Drug_Code = AllCerts_for_Patients.DrugClass)) LEFT JOIN Certs_Concat ON PatientPrescriptions.ID = Certs_Concat.ID
GROUP BY PatientPrescriptions.[AUTH NBR], PatientPrescriptions.Date_Rx, IIf(IsNull([Cert]),"Review","Approve");

 

[arnelgp]

to make it editable make sure the Linking field of the join to the right is Primary Key.

...
sorry that is not editable, because of expression.

 

[arnelgp]

is the Dlookup the controlsource of of your textbox? it wont be editable either.

what is your form continuous or datasheet?

 

[cmray58]

is the Dlookup the controlsource of of your textbox? it wont be editable either.

what is your form continuous or datasheet?

The form is continuous and the dlookup is the control source but the dlookup is not the field that needs to be edited. It's another field

 

[arnelgp]

if the field you are using Dlookup is a bound field, maybe creating a function will somewhat speed the retrieving of data:

on your form, add a public function to retrieve the value:

' code start here
private mdl_rs as dao.recordset

private sub form_open(cancel as integer)
' if our rs is not yet created create it
if mdl_rs is nothing then set mdl_rs = DBEngine(0)(0).QueryDefs("QUERY1").OpenRecordset(dbOpenDynaset)
end sub

public function fnGetSuggestedDecision(authNum as Variant) As Variant
'convert to string
authNum = autNum & ""
with mdl_rs
.findfirst "[AUTH NBR]='" & autNum & "'"
if not .noMatch then fnGetSuggestedDecision=![Suggestion].Value
end with
end function


you can use the function on the current event of your form:

private sub form_current()
me.txtSuggestion = fnGetSuggestedDecision(me![AUTH NBR])
end sub