Consulting

Results 1 to 5 of 5

Thread: VBA problem..

  1. #1
    VBAX Regular
    Joined
    Nov 2012
    Posts
    16
    Location

    Question VBA problem..

    How Can I go about making the msgbox appear if any of the conditions is wrong?? Please assist


    Private Sub cmd_LO_PBA_Facility_Click()
    On Error GoTo Err_Cmd_LO_PBA_Facility_Click
    Me.Refresh
    Dim stDocName As String

    If cmb_Product_Type = "PBA Facility" Then

    If Not IsNull(RCF_Limit) Then

    If Not IsNull(FX_Option_Limit) Then

    If Not IsNull(Security_Option_Limit) Then

    If Not IsNull(PBA_Account_No) Then

    If Not IsNull(Interest_Rate) Then

    stDocName = "PBA_Facility_LO"
    DoCmd.OpenReport stDocName, acPreview

    Else:
    MsgBox "Please check your input at Revolving Credit Facility tab"
    Me!RCF_Limit.SetFocus

    End If

    End If

    End If

    End If

    End If

    End If

    Exit_Cmd_LO_PBA_Facility_Click:
    Exit Sub
    Err_Cmd_LO_PBA_Facility_Click:
    MsgBox Err.Description
    Resume Exit_Cmd_LO_PBA_Facility_Click

    End Sub

  2. #2
    VBAX Newbie
    Joined
    Apr 2012
    Posts
    3
    Location
    [FONT='Verdana','sans-serif']I would break down the if blocks into sections. This way you can customize what the msgbox displays. For example the first if block you can change the message to "Please check your input at the PBA Facility on the Facility tab." You can also set the focus to the exact area that met the conditions. I hope that helps. [/FONT]
    [FONT='Verdana','sans-serif'][/FONT]
    [FONT='Verdana','sans-serif']Private Sub cmd_LO_PBA_Facility_Click()
    On Error GoTo Err_Cmd_LO_PBA_Facility_Click
    Me.Refresh
    Dim stDocName As String

    If cmb_Product_Type = "PBA Facility" Then[/FONT]

    [FONT='Verdana','sans-serif']MsgBox "Please check your input at Revolving Credit Facility tab"
    Me!RCF_Limit.SetFocus[/FONT]

    [FONT='Verdana','sans-serif']Exit sub[/FONT]
    [FONT='Verdana','sans-serif']End IF[/FONT]
    [FONT='Verdana','sans-serif']If Not IsNull(RCF_Limit) Then
    MsgBox "Please check your input at Revolving Credit Facility tab"[/FONT]

    [FONT='Verdana','sans-serif']Me!RCF_Limit.SetFocus[/FONT]
    [FONT='Verdana','sans-serif']Exit sub[/FONT]
    [FONT='Verdana','sans-serif']End IF[/FONT]
    [FONT='Verdana','sans-serif']If Not IsNull(FX_Option_Limit) Then[/FONT]
    [FONT='Verdana','sans-serif']MsgBox "Please check your input at Revolving Credit Facility tab"
    Me!RCF_Limit.SetFocus[/FONT]

    [FONT='Verdana','sans-serif']Exit sub[/FONT]
    [FONT='Verdana','sans-serif']End IF

    If Not IsNull(Security_Option_Limit) Then[/FONT]

    [FONT='Verdana','sans-serif']MsgBox "Please check your input at Revolving Credit Facility tab"
    Me!RCF_Limit.SetFocus[/FONT]

    [FONT='Verdana','sans-serif']Exit sub[/FONT]
    [FONT='Verdana','sans-serif']End IF[/FONT]
    [FONT='Verdana','sans-serif']
    If Not IsNull(PBA_Account_No) Then
    MsgBox "Please check your input at Revolving Credit Facility tab"[/FONT]

    [FONT='Verdana','sans-serif']Me!RCF_Limit.SetFocus[/FONT]
    [FONT='Verdana','sans-serif']Exit sub[/FONT]
    [FONT='Verdana','sans-serif']End IF[/FONT]
    [FONT='Verdana','sans-serif']If Not IsNull(Interest_Rate) Then[/FONT]
    [FONT='Verdana','sans-serif']MsgBox "Please check your input at Revolving Credit Facility tab"
    Me!RCF_Limit.SetFocus[/FONT]

    [FONT='Verdana','sans-serif']Exit sub[/FONT]
    [FONT='Verdana','sans-serif']End if

    stDocName = "PBA_Facility_LO"
    DoCmd.OpenReport stDocName, acPreview



    Exit_Cmd_LO_PBA_Facility_Click:
    Exit Sub
    Err_Cmd_LO_PBA_Facility_Click:
    MsgBox Err.Description
    Resume Exit_Cmd_LO_PBA_Facility_Click

    End Sub[/FONT]

  3. #3
    VBAX Regular
    Joined
    Nov 2012
    Posts
    16
    Location

    Question Problem Not Solved

    Quote Originally Posted by lenius
    [FONT='Verdana','sans-serif']I would break down the if blocks into sections. This way you can customize what the msgbox displays. For example the first if block you can change the message to "Please check your input at the PBA Facility on the Facility tab." You can also set the focus to the exact area that met the conditions. I hope that helps. [/font]
    [FONT='Verdana','sans-serif'][/font]
    [FONT='Verdana','sans-serif']Private Sub cmd_LO_PBA_Facility_Click()
    On Error GoTo Err_Cmd_LO_PBA_Facility_Click
    Me.Refresh
    Dim stDocName As String

    If cmb_Product_Type = "PBA Facility" Then[/font]

    [FONT='Verdana','sans-serif']MsgBox "Please check your input at Revolving Credit Facility tab"
    Me!RCF_Limit.SetFocus[/font]

    [FONT='Verdana','sans-serif']Exit sub[/font]
    [FONT='Verdana','sans-serif']End IF[/font]
    [FONT='Verdana','sans-serif']If Not IsNull(RCF_Limit) Then
    MsgBox "Please check your input at Revolving Credit Facility tab"[/font]

    [FONT='Verdana','sans-serif']Me!RCF_Limit.SetFocus[/font]
    [FONT='Verdana','sans-serif']Exit sub[/font]
    [FONT='Verdana','sans-serif']End IF[/font]
    [FONT='Verdana','sans-serif']If Not IsNull(FX_Option_Limit) Then[/font]
    [FONT='Verdana','sans-serif']MsgBox "Please check your input at Revolving Credit Facility tab"
    Me!RCF_Limit.SetFocus[/font]

    [FONT='Verdana','sans-serif']Exit sub[/font]
    [FONT='Verdana','sans-serif']End IF

    If Not IsNull(Security_Option_Limit) Then[/font]

    [FONT='Verdana','sans-serif']MsgBox "Please check your input at Revolving Credit Facility tab"
    Me!RCF_Limit.SetFocus[/font]

    [FONT='Verdana','sans-serif']Exit sub[/font]
    [FONT='Verdana','sans-serif']End IF[/font]
    [FONT='Verdana','sans-serif']
    If Not IsNull(PBA_Account_No) Then
    MsgBox "Please check your input at Revolving Credit Facility tab"[/font]

    [FONT='Verdana','sans-serif']Me!RCF_Limit.SetFocus[/font]
    [FONT='Verdana','sans-serif']Exit sub[/font]
    [FONT='Verdana','sans-serif']End IF[/font]
    [FONT='Verdana','sans-serif']If Not IsNull(Interest_Rate) Then[/font]
    [FONT='Verdana','sans-serif']MsgBox "Please check your input at Revolving Credit Facility tab"
    Me!RCF_Limit.SetFocus[/font]

    [FONT='Verdana','sans-serif']Exit sub[/font]
    [FONT='Verdana','sans-serif']End if

    stDocName = "PBA_Facility_LO"
    DoCmd.OpenReport stDocName, acPreview



    Exit_Cmd_LO_PBA_Facility_Click:
    Exit Sub
    Err_Cmd_LO_PBA_Facility_Click:
    MsgBox Err.Description
    Resume Exit_Cmd_LO_PBA_Facility_Click

    End Sub[/font]

    Anw, how do you use the exit sub thing?

    Now I'm trying to tweak a bit, meaning if a field is null then the msgbox will appear and setfocus to the particular null-field.

    I want it to check one by one, for example check:

    If cmb_Product_Type = "PBA Facility" Then

    If RCF_Limit = Null Then
    MsgBox "Please fill in RCF Limit!"
    Me!RCF_Limit.SetFocus

    Else
    If FX_Option_Limit = Null Then
    MsgBox "Please fill in FX Option Limit!"
    Me!FX_Option_Limit.SetFocus
    End If

    End If

    End If

    In addition, I want to preview a document for e.g. stDocName if all the checkings are false which means all fields are filled in thus there is no need for any MsgBox to appear and setfocus to any field.

    Please assist.

  4. #4
    VBAX Contributor
    Joined
    Oct 2011
    Location
    Concord, California
    Posts
    101
    Location
    In your code:
    If RCF_Limit = Null Then
    MsgBox "Please fill in RCF Limit!"
    Me!RCF_Limit.SetFocus

    simply add Exit Sub after the .SetFocus statement. Do this for each If statement.

  5. #5
    VBAX Regular
    Joined
    Nov 2012
    Posts
    16
    Location

    Smile

    Quote Originally Posted by mrojas
    In your code:
    If RCF_Limit = Null Then
    MsgBox "Please fill in RCF Limit!"
    Me!RCF_Limit.SetFocus

    simply add Exit Sub after the .SetFocus statement. Do this for each If statement.
    Thanks mrojas for your assistance.

Posting Permissions

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