Confusing ODBC Error Message

[Paul H]

This the 3rd in a series of posts I have made regarding this data entry form which I'm hoping to distribute soon. The purpose is for medical professionals to enter diagnosis codes for a particular patient. The same patient cannot be given the same diagnosis twice. When this happens I get one of those long, confusing ODBC error messages. Example below.





Since this is not a coding error, I don't know how to trap it and translate it into English for the end user. Surely there's a way to code for this. Rather than groping about and jury-rigging this, I'll await your expert replies.

[ranman256]

it says your are trying to add a key in a table that already has one. 534209
you cant add it twice.

[Bulzie]

In the AfterUpdate of that diagnostic field(which should be a combo box), do a DLookup of that code and Patient in the table. If it is found, give them a message saying so, SetFocus to the Diagnosis box and don't save the value.

cboDiagCode_AfterUpdate:

IF Not Isnull(DLookup("DiagCode", "tblPatientData", "DiagCode = '" & me.cboDiagCode & "'")) Then

MsgBox "The Diagnostic code " & me.cboDiagCode & " you entered has already been selected for this patient."
Me.cboDiagCode.SetFocus
End

End If

[pbaldy]

I assume this is happening after the user fills out an entire form and tries to save it. In general I try to avoid errors rather than just trap for them, so I'd lean towards checking for duplicates during data entry. That way you also avoid the user filling out the whole form and then finding out it's a duplicate.

[Paul H]

Thanks, I will address this tomorrow. Time to go home.

[Paul H]

Since it is a very small form with only two fields to fill out, I went with this.

    P_ID = Me.Parent.P_ID.Caption
    
    strsql = "SELECT * FROM tblDiagnosis_ICD10 WHERE P_RegNo = '" & P_ID & "' and ICD10 = '" & Me.ICD10 & "' and Admit_Date = #" & Me.Parent.Admit_Date & "#"
    
    Set rst = CurrentDb.OpenRecordset(strsql)
        
    If Not rst.EOF Then
        MsgBox "Diagnosis code " & Me.ICD10 & ": " & Me.cboICD10_Description & " has already been used for this patient admission."
        rst.Close
        Set rst = Nothing
        Me.Undo
        Me.cbo_ICD10.SetFocus
        GoTo err_Exit
    End If

Worked great. Thanks for your help.

Paul