Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    9

    Unanswered: Code in bold not running when the combo box is null

    Private Sub PostReasonCodes_Click()

    Dim strSQL As String
    Dim str2SQL As String
    Dim rst As Recordset
    Dim MO As String
    Dim WO As Long
    Dim ReasonCodeVar As String

    If Me.Dirty Then Me.Dirty = False

    answer = MsgBox("Are you sure you would like to post the new reason codes dates to the system?", 4, "Verify")
    If answer = vbYes Then

    On Error GoTo Err_PostReasonCodes_Click

    'SQL statement to get WO/MO, Reason Code and group, sorted by the WO then MO
    strSQL = "SELECT STRUDEX_PRP010.[PRCTL#], [STRUDEX_PRP010]![PRCMPY] & [STRUDEX_PRP010]![PRORD#] AS MO " & _
    "FROM (WO_Schedule INNER JOIN STRUDEX_PRP010 ON WO_Schedule.[WO#] = STRUDEX_PRP010.[PRCTL#]) INNER JOIN STRUDEX_OPL010B ON " & _
    "(STRUDEX_PRP010.PROLNE = STRUDEX_OPL010B.O1LINE) AND (STRUDEX_PRP010.[PRORD#] = STRUDEX_OPL010B.[O1ORD#]) AND (STRUDEX_PRP010.PRCMPY = STRUDEX_OPL010B.O1CMPY) " & _
    "GROUP BY STRUDEX_PRP010.[PRCTL#], [STRUDEX_PRP010]![PRCMPY] & [STRUDEX_PRP010]![PRORD#] ORDER BY STRUDEX_PRP010.[PRCTL#], [STRUDEX_PRP010]![PRCMPY] & [STRUDEX_PRP010]![PRORD#]"

    'Opens the data read only as we dont need to change it
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    If rst.RecordCount > 0 Then
    rst.MoveFirst
    WO = rst![PRCTL#]
    MO = rst![MO]
    Forms!WOs_MOs!ReasonCode.SetFocus
    ReasonCodeVar = IIf(IsNull(ReasonCode.Text), "N/A", ReasonCode.Text)
    'Checks to see if the Reason Code has been selected
    If ReasonCodeVar = "N/A" Then
    MsgBox (WO + "" + "does not have a reason code assigned to it, processing aborted")
    On Error GoTo Err_Reason_Code_Not_Assigned
    End If
    End If

    'Terminates the run of the program
    Exit_PostReasonCodes_Click:
    Exit Sub

    'Error occurs if Reason code field is blank when user posts reason code
    Err_Reason_Code_Not_Assigned:
    MsgBox ("Data Error 01")
    Resume Exit_PostReasonCodes_Click

    'General Error
    Err_PostReasonCodes_Click:
    MsgBox Err.Description
    Resume Exit_PostReasonCodes_Click
    End If
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the value could be null then test for null

    Code:
    If (isnull(ReasonCodeVar) OR ReasonCodeVar = "N/A") Then
    MsgBox (WO + "" + "does not have a reason code assigned to it, processing aborted")
    On Error GoTo Err_Reason_Code_Not_Assigned
    End If
    incidentally I'd be cautious using + as the string concatenation operator. AFAIK the correct concatenation symbol is &. using + can cuase odd problems if you are concatenating numeric symbols
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Posts
    9

    One more thing

    Thank you, that works but it does not invoke the GO to Error command, is it because it is not in the IF Statement?

    Regards,
    Dude

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you expect it to invoke the GO to Error statement
    you set an error trap but don't run any code which could cause the error trap to trigger
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Posts
    9

    Where would the error trap go?

    So where would you put it?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry Im not certain what you intend doing
    the way to on error statement works is
    you place it before a block of code where you think . expect an error may occur. if an error does arise, code jumps to teh specified code block.

    with your code
    you set an error trap, but there is no code running after that that could contain an error s the next (effective) statement is exit sub

    so basedon your current code the on error is redundant
    you may want to review your code and work out what you want to do in the light of that
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •