Error trap not trapping

[morsagmon]

I have this code with an "On Error Resume Next" statement that is not trapping the error.

Public Function FetchSupplementUnits2() As String
    'Fetch supplement units description for Supplements Intake of Treatment
    On Error GoTo FormNotLoaded_Exit

    If IsNull(Forms!frmTreatments.Form!sfrmSupplementsIntakeOfTreatment![Supplement_Code]) Then
        Exit Function
    End If
    On Error GoTo 0
    Dim strUnitCode As String
    [B]On Error Resume Next[/B]
    strUnitCode = DLookup("[Units_Code]", "tblProducts", "[Supplement_Code]=Forms!frmTreatments.Form!sfrmSupplementsIntakeOfTreatment![Supplement_Code]")
    [B]FetchSupplementUnits2 = DLookup("[Units_Description]", "tblUnits_table", "[Units_Code]=" & strUnitCode[/B])
    On Error GoTo 0
FormNotLoaded_Exit:
    Exit Function
    
End Function

The program displays an error message to the user:

Syntax error (missing operator) in query expression '[Unit_Code]='.

The line that causes this error is highlighted.

All I want the program to do is ignore the error end continue, without interrupting the user.

Any ideas?
Thanks!

 

[jdraw]

There appears to be a syntax error in this section

"[Units_Code]=" & strUnitCode)

try

"[Units_Code]='" & strUnitCode & "'")

Also, On Error Resume Next does not do any error trapping. It ignores errors and continues on its merry way.

 

[morsagmon]

jdraw,

Adding the single quotes causes a data type mismatch error.
The way it is written is working great - in this function and in another one.
The function is called for each record. The only time it fails is on the first time, when the subforms are loaded before their parent, in which case I need to ignore the error and continue.
For all records, once all forms are loaded, the function performs exactly what it needs to, in the same syntax I quoted above originally.

On error resume next - that's exactly what I need - the program to ignore and continue on its merry way. My problem - it continues all right, but not before it triggers the error message, which I'm trying to avoid. That's my problem.

Thanks!!

 

[Steve R.]

My apologies for going beyond the question at hand.

I would suggest that the code should be written to "flow" to the end without the use of "exit" and "go to" statements. The sample below may not be correct since I just shuffled the code around to show how to allow the program to "flow" to the end.

Public Function FetchSupplementUnits2() As String
    'Fetch supplement units description for Supplements Intake of Treatment
    On Error GoTo ERR_FormNotLoaded_Exit
    Dim strUnitCode As String
    If Not IsNull(Forms!frmTreatments.Form!sfrmSupplementsIntakeOfTreatment![Supplement_Code]) Then           
            strUnitCode = DLookup("[Units_Code]", "tblProducts", "[Supplement_Code]=Forms!frmTreatments.Form!sfrmSupplementsIntakeOfTreatment![Supplement_Code]")
            FetchSupplementUnits2 = DLookup("[Units_Description]", "tblUnits_table", "[Units_Code]=" & strUnitCode)
        Else
            Rem this is where you would handle the null situation.
        End If
    Exit Function
ERR_FormNotLoaded_Exit:
    Rem should have some sort of error messages.  Resume where????
End Function

 

[morsagmon]

Steve,

I changed the code to follow your logic, and the error message disappeared.
Problem solved!

Many thanks!!

 

[jdraw]

Glad you got it resolved.