Results 1 to 5 of 5
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    Message Box with multiple conditions

    I could use some help. I have created the code attached. I am trying to make my fields in the form required before saving. I am having trouble with the Else on the third If statement. I will not run. Can someone give some insight?




    Code:
    Private Sub cmdClose_Click()    'Fields are required and cannot save if IsNull
        If Not (IsNull([txtCount])) And Not (IsNull([dtDateActivity])) Then
            DoCmd.GoToRecord , , acNewRec
            Forms!frmNavMain!frmNavMain.Form.lstMonth0.Requery
            Forms!frmNavMain!frmNavMain.Form.lstMonth1.Requery
            Forms!frmNavMain!frmNavMain.Form.lstMonth2.Requery
            Forms!frmNavMain!frmNavMain.Form.lstMissingEntries.Requery
            DoCmd.Close
            Else
                'If IsNull, then msgbox with yes/no
                If (IsNull([txtCount])) Or (IsNull([dtDateActivity])) Then
                MsgBox "Your are attempting to file this entry without a count.  If you continure, this entry will be deleted." _
                & vbCrLf & "Do you want to continue?", vbYesNo + vbCritical, "Missing Required Information"
                    'If no, then return to form with set focus
                    If vbNo Then
                        [dtDateInvoice].SetFocus
                         'if yes, then undo, close form and requery loaded form
                        Else
                            Me.Undo
                            Forms!frmNavMain!frmNavMain.Form.lstMonth0.Requery
                            Forms!frmNavMain!frmNavMain.Form.lstMonth1.Requery
                            Forms!frmNavMain!frmNavMain.Form.lstMonth2.Requery
                            Forms!frmNavMain!frmNavMain.Form.lstMissingEntries.Requery
                            DoCmd.Close
                    End If
                End If
        End If
    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Step through the code and watch your variable values when you have trouble (a line has to execute (process) in order to alter those variables).
    Your problem is likely that the IF relates (is equal to, less than, greater than, not equal to) to no 'thing' or variable. It's like I said to you "IF red then..." You'd reply "If what is red?"
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you want to use the result of the message box, you use a variable:

    response = MsgBox(...)
    If response = vbNo Then

    plus you just have a regular message box there. Here's my template code for a yes/no:

    Code:
    Dim msg As String, button As Variant, title As String, response As Variant
    msg = "Vehicle is Shopped - Do you want to dispatch anyway?"
    button = vbYesNo + vbDefaultButton2
    title = "Vehicle Shopped!"
    
    response = MsgBox(msg, button, title)
    If response = vbYes Then
      'what to do if yes
    Else
      'what to do if not
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Thank you for your responses. I have fixed the code, but know I can do better in defining with dim. Example 1 is the code written and works. Example 2 is more of what I would like to advance my code for improved coding. I am fairly new to vba and have learned on my own. Insight as to why it returns Invalid qualifier would be helpful.

    Example 1: Works

    Code:
    Option Compare
    DatabaseOption Explicit
    
    
    Private Sub cmdClose_Click()
    'Define
    Dim Month0 As String
    Dim Month1 As String
    Dim Month2 As String
    Dim MissingEntries As String
    Dim msg As String
    Dim button As Variant
    Dim Title As String
    Dim Response As Variant
    msg = "Your are attempting to file this entry without a count.  If you continue, this entry will be deleted."
    button = vbYesNo + vbDefaultButton2
    Title = "Missing Required Information"
    Response = MsgBox(msg, button, Title)
    
        'Fields are required and cannot save if IsNull
        If Not (IsNull([txtCount])) And Not (IsNull([dtDateActivity])) Then
            DoCmd.GoToRecord , , acNewRec
            Forms!frmNavMain!frmNavMain.Form.lstMonth0.Requery
            Forms!frmNavMain!frmNavMain.Form.lstMonth1.Requery
            Forms!frmNavMain!frmNavMain.Form.lstMonth2.Requery
            Forms!frmNavMain!frmNavMain.Form.lstMissingEntries.Requery
            DoCmd.Close
            Else
                'If IsNull, then msgbox with yes/no
                If (IsNull([txtCount])) Or (IsNull([dtDateActivity])) Then
                    'If no, then return to form with set focus
                    If Response = vbNo Then
                        [dtDateInvoice].SetFocus
                         'if yes, then undo, close form and requery loaded form
                        Else
                            Me.Undo
                            Forms!frmNavMain!frmNavMain.Form.lstMonth0.Requery
                            Forms!frmNavMain!frmNavMain.Form.lstMonth1.Requery
                            Forms!frmNavMain!frmNavMain.Form.lstMonth2.Requery
                            Forms!frmNavMain!frmNavMain.Form.lstMissingEntries.Requery
                            DoCmd.Close
                    End If
                End If
        End If
    End Sub
    Example 2 returns Invalid qualifier.

    Code:
    Option Compare 
    DatabaseOption Explicit
    
    
    Private Sub cmdClose_Click()
    'Define
    Dim Month0 As String
    Dim Month1 As String
    Dim Month2 As String
    Dim MissingEntries As String
    Dim msg As String
    Dim button As Variant
    Dim Title As String
    Dim Response As Variant
    msg = "Your are attempting to file this entry without a count.  If you continue, this entry will be deleted."
    button = vbYesNo + vbDefaultButton2
    Title = "Missing Required Information"
    Response = MsgBox(msg, button, Title)
    'when using below, returns Invalid qualifier
    Month0 = Forms!frmNavMain!frmNavMain.Form.lstMonth0
    Month1 = Forms!frmNavMain!frmNavMain.Form.lstMonth1
    Month2 = Forms!frmNavMain!frmNavMain.Form.lstMonth2
    MissingEntries = Forms!frmNavMain!frmNavMain.Form.lstMissingEntries
    
        'Fields are required and cannot save if IsNull
        If Not (IsNull([txtCount])) And Not (IsNull([dtDateActivity])) Then
            DoCmd.GoToRecord , , acNewRec
            Month0.Requery
            Month1.Requery
            Month2.Requery
            MissingEntries.Requery
            DoCmd.Close
            Else
                'If IsNull, then msgbox with yes/no
                If (IsNull([txtCount])) Or (IsNull([dtDateActivity])) Then
                    'If no, then return to form with set focus
                    If Response = vbNo Then
                        [dtDateInvoice].SetFocus
                         'if yes, then undo, close form and requery loaded form
                        Else
                            Me.Undo
                            Month0.Requery
                            Month1.Requery
                            Month2.Requery
                            MissingEntries.Requery
                            DoCmd.Close
                    End If
                End If
        End If
    End Sub

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Which line raises the error - the very next one after the comment?

    EDIT - wasn't sure if the error should just be fixed first or mention that some of the 2nd code doesn't make sense.
    Why raise a message box that requires a response without validating the conditions first (e.g. test for null then do or don't raise message box)
    Choices probably should be OK and Cancel, not yes and no as the latter doesn't seem adequate
    Your code does things regardless of what that answer is, so is it appropriate to set variables, etc. if the answer is to cancel closing?
    Also, you can't requery a variable as you are doing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sql case multiple conditions
    By soldat452002 in forum Queries
    Replies: 2
    Last Post: 07-27-2018, 02:40 AM
  2. VBA If with multiple conditions
    By Swatskeb in forum Modules
    Replies: 2
    Last Post: 05-22-2014, 04:22 PM
  3. Replies: 2
    Last Post: 01-23-2014, 12:40 PM
  4. Using iif for Multiple Conditions
    By kwilbur in forum Access
    Replies: 5
    Last Post: 12-30-2011, 01:52 PM
  5. Expression with multiple conditions
    By techexpressinc in forum Queries
    Replies: 3
    Last Post: 06-19-2009, 08:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums