• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Troubleshooting a VBA code

Dear Experts. I dont understand this. When I have code A for an click then nothing happens but when Code B is used the button functions perfectly. I need the logic behind Code A. Please help me.

Code A:

Private Sub Command84_Click()
On Error GoTo Err_Command84_Click

    Dim stDocName1 As String
    Dim stDocName2 As String
    Dim StLinkCriteria As String

    stDocName1 = "Fo_main_Patient_List1a"
    stDocName2 = "Fo_main_patient_List1FamMembersa"
   
    If Me.Plan_Type.Value = "Main Plan" Then
   
     
    StLinkCriteria = "[Main_Pt_ID]=" & Me![Main_Pt_ID]
    DoCmd.OpenForm stDocName1, , , StLinkCriteria
   
    ElseIf Me.Form.Head_Fam_member_ID.Value = 0 And Me.Plan_Type.Value = "Family Plan" Then
   
    StLinkCriteria = "[Main_Pt_ID]=" & Me![Main_Pt_ID]
    DoCmd.OpenForm stDocName2, , , StLinkCriteria
   
    ElseIf Me.Form.Head_Fam_member_ID.Value > 0 Then
   
    MsgBox ("family Members cannot terminate contracts. Only Family head members can")
   
    End If

Exit_Command84_Click:
    Exit Sub

Err_Command84_Click:
    MsgBox Err.Description
    Resume Exit_Command84_Click
End Sub

Code B:

Private Sub Command84_Click()
On Error GoTo Err_Command84_Click

    Dim stDocName1 As String
    Dim stDocName2 As String
    Dim StLinkCriteria As String

    stDocName1 = "Fo_main_Patient_List1a"
    stDocName2 = "Fo_main_patient_List1FamMembersa"
   
    If Me.Form.Head_Fam_member_ID.Value = 0 Then
   
    StLinkCriteria = "[Main_Pt_ID]=" & Me![Main_Pt_ID]
    DoCmd.OpenForm stDocName1, , , StLinkCriteria
   
    ElseIf Me.Form.Head_Fam_member_ID.Value > 0 Then
   
    StLinkCriteria = "[Main_Pt_ID]=" & Me![Main_Pt_ID]
    DoCmd.OpenForm stDocName2, , , StLinkCriteria
   
    End If

Exit_Command84_Click:
    Exit Sub

Err_Command84_Click:
    MsgBox Err.Description
    Resume Exit_Command84_Click
End Sub
0
robbhat
Asked:
robbhat
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you SURE one of your criteria is being met? In CodeA, one of these must be true:

Me.Plan_Type.Value = "Main Plan"

or

Me.Form.Head_Fam_member_ID.Value = 0 And Me.Plan_Type.Value = "Family Plan"

or

Me.Form.Head_Fam_member_ID.Value > 0

If none of these statements are true, then CodeA won't do anything.

If one of those is true, then you need to determine where your Code A is failing. Set a breakpoint (i.e. put your cursor at the beginning of the code block, and press F9) then run action that fires the code (i.e. click the button, etc). Use the F8 key to "step" through the code and determine the exact line where the code is failing.
0
 
robbhatAuthor Commented:
Head_Fam_Member_ID is always 0 or a number greater than 0 (never negative)
When it is 0 The Plan_Type is either "Main Plan" or "Family Plan"
When Head_Fam_Member_ID >0 Plan_Type is always "Family Plan"

The idea is that

1. when Plan_Type = "Main Plan", I want action (Incidentally, Head_fam_member_ID is also 0, but this is irrelevant because we are depending on the Main Plan marker

2. When Plan_Type = "Family Plan" Head_Fam_Member ID is either 0 or >1. To select  a certain class of Family Plan members, I have to set the criteria of when Head_fam_member_ID =0 and I want action 2

3. When Head_fam_member_ID>0 only one type of "Family Plan" members are present, and there are no "main Plan" holders. This is where I want the MsgBox

I tried the troubleshooting with the F9 and F8. When on Plan B (I did this to compare to see what I might be looking for) it goes from line to line and skips these areas:

1. Dim stDocName1 As String
    Dim stDocName2 As String
    Dim StLinkCriteria As String

2. ElseIf Me.Form.Head_Fam_member_ID.Value > 0 Then
   
    StLinkCriteria = "[Main_Pt_ID]=" & Me![Main_Pt_ID]
    DoCmd.OpenForm stDocName2, , , StLinkCriteria
(This was missed because the previous criteria was satisfied)

For Code A:
It highlights:

If Me.Plan_Type.Value = "Main Plan" Then
ElseIf Me.Form.Head_Fam_member_ID.Value = 0 And Me.Plan_Type.Value = "Family Plan" Then
ElseIf Me.Form.Head_Fam_member_ID.Value > 0 Then
End If
Exit Sub

So, it seems that none of the conditions are satisfied and I cant understand why.

Can you suggest anything/

Rob


0
 
InnovativeLogicCommented:
could this also be the same issue as before where combo44 and plan_type control names are being confused?
0
 
robbhatAuthor Commented:
When it doesnt work and the Experts cant fault the code...The experts are always right. It is the novice who is a div
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now