[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

vbYesNo MsgBox problem

Posted on 2007-08-04
9
Medium Priority
?
1,536 Views
Last Modified: 2013-11-27
Dear Experts...Can you check and edit my code for the following action. On double clicking Main_Pt_ID field in the subform, the MasgBox appears If Contract_End_Date in the parent Form is null. The user is offered to be taken to the appropriate form (through the subroutine) if he presses Yes in the vbYesNo Box else the routine exists. The subroutine that is prompted by the Yes Links the parentform Main_Pt_Id to the destination form Main_Pt_ID.

However, on Pressing yes does not execute the subroutine. Can you please check my VBA and rectify it please?
Private Sub Introduced_Pt_ID_DblClick(Cancel As Integer)
On Error GoTo Err_Introduced_Pt_ID_DblClick

If IsNull(Me.Parent!Contract_End_date) Then

iAnswer = MsgBox("You cannot Calculate Introduction Discount when the Introducing Patient's Contract End Date has not been filled. Do You want to add this information first?", vbYesNo)

If iAnswer = True Then

EditIntroducingPtContractStartDate        This is the subroutine shown below

Else: Exit Sub

End If
End If
Exit_Introduced_Pt_ID_DblClick:
    Exit Sub

Err_Introduced_Pt_ID_DblClick:
    MsgBox Err.Description
    Resume Exit_Introduced_Pt_ID_DblClick
   
   
End Sub

Private Sub EditIntroducingPtContractStartDate()

    Dim stDocName As String
    DimStLinkCriteria As String
   
    stDocName = " Fo_main_Patient_List1"
   
    stLinkCriteria = "[Main_Pt_ID]=" & Me.Parent![Main_Pt_ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
   
Exit_Introduced_Pt_ID_DblClick:
    Exit Sub

Err_Introduced_Pt_ID_DblClick:
    MsgBox Err.Description
    Resume Exit_Introduced_Pt_ID_DblClick

End Sub



0
Comment
Question by:robbhat
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19630423
>If iAnswer = True Then
needs to be
If iAnswer = vbYes Then

0
 
LVL 77

Expert Comment

by:peter57r
ID: 19630478
Yes No buttons do not provide True/False values.

Use vbYes to test for Yes and vbNo to test for No.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 80 total points
ID: 19630480
Oh - missed the earlier response -must have been off the end of my screen.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:robbhat
ID: 19630511
Thanks that works, but I cant figure out why the code is not performing on pressing yes, the first time and the second time. However, if both the criteria are satisfied and the user does not need to use the YesNo button then the primary Object Form opens. Can you help please:

Private Sub Arthur_ID_DblClick(Cancel As Integer)
Dim iAnswer As Integer
Dim iAnswer1 As Integer

On Error GoTo Err_Arthur_ID_DblClick
If IsNull(Me.Parent!Contract_End_date) Then
iAnswer = MsgBox("You cannot Calculate Introduction Discount when the Introducing Patient's Contract End Date has not been filled. Do You want to add this information first?", vbYesNo)
ElseIf iAnswer = vbYes Then
EditIntroducingPtContractStartDate        '''This is the subroutine shown below
ElseIf IsNull(Me.[Introduced pt Contract Start Date]) Then
iAnswer1 = MsgBox("You cannot Calculate Introduction Discount when the Introduced Patient's Contract End Date has not been filled. Do You want to add this information first?", vbYesNo)
ElseIf iAnswer1 = vbYes Then
EditIntroducedPtContractStartDate     '''This is another subroutine shown below
Else: OpenThisForm    '''This is the destination form that is the primary object of the Command
End If
End Sub

Private Sub EditIntroducingPtContractStartDate()
Dim stDocName As String
    Dim StLinkCriteria As String
   
    stDocName = "Fo_main_Patient_List1"
   
    StLinkCriteria = "[Main_Pt_ID]=" & Me.Parent![Main_Pt_ID]
   
   DoCmd.OpenForm stDocName, , , StLinkCriteria
    End Sub


Private Sub EditIntroducedPtContractStartDate()

Dim stDocName As String
    Dim StLinkCriteria As String
   
    stDocName = "Fo_main_Patient_List1"
   
    StLinkCriteria = "[Main_Pt_ID]=" & Me.[introduced Pt ID]
   
    DoCmd.OpenForm stDocName, , , StLinkCriteria
   

End Sub

Private Sub OpenThisForm()


    Dim stDocName As String
    Dim StLinkCriteria As String

    stDocName = "Fo_Introductions_Line_ItemA"
   
    StLinkCriteria = "[Introduced Pt ID]=" & Me![introduced Pt ID]
    DoCmd.OpenForm stDocName, , , StLinkCriteria

End Sub


0
 
LVL 11

Accepted Solution

by:
LambertHeenan earned 420 total points
ID: 19630596
Wow! You went from a piece of code that was basically right to a convoluted If statement with multiple ElseIf's. Quite a jump. :-)

My guess is that your goal is to ensure that a date field on the parent form and another one on the sub form are both populated, and if the user chooses NOT to set either dates the DblClick routine exits.

Ok. Let's try that. First as you have a longish message to display in the MsgBox I suggest using a couple of constants to make things a bit more readable, and second, you don't really need the iAnswer variable, you just directly test the return value of MsgBox() compared to the constant vbYes ...

Private Sub Arthur_ID_DblClick(Cancel As Integer)

Const MSG_Parent = "You cannot Calculate Introduction Discount when the Introducing Patient's Contract End Date has not been filled. Do You want to add this information first?"

Const MSG_Me = "You cannot Calculate Introduction Discount when the Introduced Patient's Contract End Date has not been filled. Do You want to add this information first?"

On Error GoTo Err_Arthur_ID_DblClick ' need to define the error handler!

' Check the date on the parent form
If IsNull(Me.Parent!Contract_End_date) Then
    If MsgBox(MSG_Parent, vbYesNo) = vbYes Then
        EditIntroducingPtContractStartDate      
        If IsNull(Me.Parent!Contract_End_date) Then Exit Sub
    End If
End If

' Check the date on the current form. Note you do not need to use Me
' in order to access the date field as it's part of the namespace of the form

If IsNull([Introduced pt Contract Start Date]) Then
    If MsgBox(MSG_Me, vbYesNo) = vbYes Then
        EditIntroducedPtContractStartDate
        If IsNull([Introduced pt Contract Start Date]) Then Exit Sub
    End If
End If

OpenThisForm    '''This is the destination form that is the primary object of the Command

Exit Sub
Err_Arthur_ID_DblClick:
    ' your error handler code here
End Sub
0
 

Author Comment

by:robbhat
ID: 19631062
This is how one learns thanks to you guys. Big Improvement LamberHeenan, but what the code is doing is that if the user clicks No for both the questions, it opens the final form (the object of the command)form which I dont want. I want it to exit if the details are not rectified. I tried to modify your VBA to do this but its not 100%. My objective is:

1. If date 1 is null  - Question 1. Yes - Gotoform to rectify and exit sub
                                Question 1. no - Exit Sub

2. If date 2 is null - Question 2 Yes - Goto Form to rectify and exit sub
                               Question 2. No - Exit sub

Open Object Form (This implies that neither of the date fields were null)

My modification of your code is this. What it is not doing is:
(a) If question 1 is No, it is going to question 2 without exiting
(b) The MsgBox apears twice with the Yes?No question when first time round one selects No

Can you possibly help me to rectify it:

The Code:

Private Sub Arthur_ID_DblClick(Cancel As Integer)

Const MSG_Parent = "You cannot Calculate Introduction Discount when the Introducing Patient's Contract End Date has not been filled. Do You want to add this information first?"

Const MSG_Me = "You cannot Calculate Introduction Discount when the Introduced Patient's Contract Start Date has not been filled. Do You want to add this information first?"

On Error GoTo Err_Arthur_ID_DblClick

' Check the date on the parent form
If IsNull(Me.Parent!Contract_End_date) Then
    If MsgBox(MSG_Parent, vbYesNo) = vbYes Then
        EditIntroducingPtContractStartDate
        Exit Sub
    ElseIf MsgBox(MSG_Parent, vbYesNo) = vbNo Then
    Exit Sub
        '''Else: Exit Sub
    End If
End If

' Check the date on the current form. Note you do not need to use Me
' in order to access the date field as it's part of the namespace of the form

If IsNull([Introduced pt Contract Start Date]) Then
    If MsgBox(MSG_Me, vbYesNo) = vbYes Then
        EditIntroducedPtContractStartDate
        Exit Sub
    ElseIf MsgBox(MSG_Me, vbYesNo) = vbNo Then
        Exit Sub
    '''If IsNull(Me.Parent!Contract_End_date) = False Then
'''If Me.[Introduced pt Contract Start Date] = False Then

End If
End If

OpenThisForm   '''This is the destination form that is the primary object of the Command

Exit_Arthur_ID_DblClick:
    Exit Sub

Err_Arthur_ID_DblClick:
    MsgBox Err.Description
    Resume Exit_Arthur_ID_DblClick
   
   
End Sub
0
 

Author Comment

by:robbhat
ID: 19631695
Very detailed effort, particularly by LambertHeenan, and I learnt a lot from it, but the solution was imperfect and I did not receive a feedback for this on enquiring about it.
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 19631730
Oops I missed the step that handles their answering 'No' This should handle that...

Private Sub Arthur_ID_DblClick(Cancel As Integer)

Const MSG_Parent = "You cannot Calculate Introduction Discount when the Introducing Patient's Contract End Date has not been filled. Do You want to add this information first?"

Const MSG_Me = "You cannot Calculate Introduction Discount when the Introduced Patient's Contract End Date has not been filled. Do You want to add this information first?"

On Error GoTo Err_Arthur_ID_DblClick ' need to define the error handler!

' Check the date on the parent form
If IsNull(Me.Parent!Contract_End_date) Then
    If MsgBox(MSG_Parent, vbYesNo) = vbYes Then
        EditIntroducingPtContractStartDate      
        If IsNull(Me.Parent!Contract_End_date) Then Exit Sub
    Else
       Exit Sub ' they answered No
    End If
End If

' Check the date on the current form. Note you do not need to use Me
' in order to access the date field as it's part of the namespace of the form

If IsNull([Introduced pt Contract Start Date]) Then
    If MsgBox(MSG_Me, vbYesNo) = vbYes Then
        EditIntroducedPtContractStartDate
        If IsNull([Introduced pt Contract Start Date]) Then Exit Sub
    Else
       Exit Sub ' they answered No
    End If
End If

OpenThisForm    '''This is the destination form that is the primary object of the Command

Exit Sub
Err_Arthur_ID_DblClick:
    ' your error handler code here
End Sub


Notice how the logic works here.

If DATE_IS_NULL Then
  If UserWantsToEdit Then ' MsgBox call
       EditTheDate
       If Date still Null Then Exit Sub
  Else
       Exit Sub ' Abandon the whole process - user said No
   End If
End If

and then repeat the exact same logic for the other date field.

In your latest version posted you did something a bit different...

10  If IsNull(Me.Parent!Contract_End_date) Then
20    If MsgBox(MSG_Parent, vbYesNo) = vbYes Then
30        EditIntroducingPtContractStartDate
40        Exit Sub
50    ElseIf MsgBox(MSG_Parent, vbYesNo) = vbNo Then
60    Exit Sub
70        '''Else: Exit Sub
80    End If
90 End If


Line 20 asks the question and if the use says Yes we go off and edit on line 30

But then line 40 exits the sub routine. This forces the user to double click on the form control again, which is not necessary.

Line 40 has the effect that if they answer No to the first MsgBox prompt they will be asked the exact same question again, and if they still answer No then exit sub. But if the answer Yes... nothing is done. We just step out of the If statement block.

When you code


If MsgBox(MSG_Parent, vbYesNo) = vbYes Then

there are only two possible return values for MsgBox, vbYes or vbNo. If (in line 20) the result is not vbYes it *must* be vbNo, so you can code
If MsgBox(MSG_Parent, vbYesNo) = vbYes Then
   ' do something
Else
    Exit Sub
End If

...and not...

ElseIf  MsgBox(MSG_Parent, vbYesNo) = vbNo Then

... which repeats the question already answered, becuase you called MsgBox again. At the risk of boring you with repetition, the logic should be...

If PARENT FORM DATE_IS_NULL Then
  If MSgbox = vbYes Then
' the next two lines execute if the user responds with Yes
       EditTheDate
       If Date still Null Then Exit Sub
  Else
' otherwise this line executes - they answered No
       Exit Sub ' Abandon the whole process
   End If
End If

' followed directly by ...

If SUB_FORM_DATE_IS_NULL Then
    If MSgbox = vbYes Then
       EditTheDate
       If Date still Null Then Exit Sub
    Else
       Exit Sub ' Abandon the whole process
    End If
End If

' now we have two dates we can call the final process
OpenThisForm  

Lambert
0
 

Author Comment

by:robbhat
ID: 19631799
Fantastic Mr. Expert...Logic issues can be a work of art in good hands...LOL (:
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question