Access "decision tree" help

[sarah1994]

I have a roughed out Access Database, containing a number of tables and forms.

I need to use the data stored in the tables to create a "decision tree" that will ultimately guide the user to an answer to his/her decision.

I only have basic knowledge of Microsoft Access and SQL, so I can't wrap my head around how I would accomplish this. Can I do it with a query?

I was hoping to have certain parameters pop up for the user to fill out, which will then lead them to another path in the decision tree. So, if they answer "yes" then follow this path, if they answer "no" then follow this other path...

Any help/advice would be greatly appreciated. I have a rough decision tree sketched out that I am attaching to this post so that you all can have a visual of what I am trying to accomplish.

[Micron]

I have to wonder why a database for this? Isn't it customary to create these as visuals (paper, docs, pdf, images, etc) because it's the best solution for the task? This would probably require 4 or 5 tables with almost nothing in each one of them, unless your image doesn't really represent what you have in mind. However, it would not support just any decision tree layout - primarily just this one. I thought about this as a one table approach, but it I think it would be ugly. Seems to me the end product would be complicated and have very limited flexibility.

[ranman256]

make a form ,with 5 boxes (digits)
put the blue decision above the box.
let user change the digit?

[orange]

I think it would be helpful if you told readers about your issue ("business") with some examples.
As micron said you would normally use some sort of logic diagram or graphics to show a decision tree/workflow.
See this short video from BA_Experts on Data Flow Diagrams

Let me also suggest that you can prototype a "user experience" sort of thing using a minimal database and prudent use of msgboxes. There is nothing(no detail) behind this approach but it can help with the logic of what should be shown to the user, what should be kept from the user, what options should be available now. This approach --we used to call stub processing in the "olden days"- is just a series of simple forms, buttons and msgboxes of what might happen. It's a good strategy to get feedback, comments and experience.

Sample of this is along this line:

Opening interface Form was opened
Select new Registration or Update existing Client (button option)

Selected Option NewRegistration.
New Registration details would go here.


Good luck

[sarah1994]

Thank you everyone for your suggestions. I ended up using numerous if/then/else statements to accomplish this:

Sub MyCode()

Dim strMessage As String
Dim varX As Variant

strMessage = "New identifier required?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbYes Then
    strMessage = "Change first digit."
    MsgBox strMessage, vbInformation, "Action Required"
Else
    strMessage = "Major change to a capability?"
    If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbYes Then
        strMessage = "Change second digit."
        MsgBox strMessage, vbInformation, "Action Required"
Else
        If DLookup("ECN_Class_I_Change", "ENGINEERING_CHANGE_NOTICE_TABLE", "[ECN_Class_I_Change] = -1") Then
            If DLookup("ECN", "ENGINEERING_CHANGE_NOTICE_TABLE", "[ECN] = Forms!DRAWING_STATUS![Subform_DRAWING_STATUS_ECN]!ECN") Then
            strMessage = "Change third digit."
            MsgBox strMessage, vbInformation, "Action Required"
        Else
            strMessage = "Minor change to hardware/drawings?"
            If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbNo Then
                strMessage = "Error. Please try again."
                MsgBox strMessage, vbInformation, "Action Required"
Else
                If DLookup("PCP", "REVIEW_PANEL_TABLE", "[PCP] = -1") Then
                    If DLookup("GWSHW_BL", "REVIEW_PANEL_TABLE", "[GWSHW_BL] = Forms!DRAWING_STATUS![Subform_DRAWING_STATUS_CONFIGURATION_HWCI_BL]!BL_HWCI_HWCI") Then
                        If DLookup("GWSHW_HW", "REVIEW_PANEL_TABLE", "[GWSHW_HW] = Forms!DRAWING_STATUS![Subform_DRAWING_STATUS_CONFIGURATION_HWCI_BL]!BL_HWCI_HWCI") Then
                    strMessage = "Change fourth digit - HWCI/BL has been certified."
                    MsgBox strMessage, vbInformation, "Action Required"
                Else
                    strMessage = "Change fifth digit"
                    MsgBox strMessage, vbInformation, "Action Required"

                End If
            End If
        End If
    End If
End If
End If
End If
End If
End Sub

I bolded the sections that I think are causing the issue. When the button is clicked, it runs through the first two If statements without issue. However, nothing happens after. There is no error or message box. I am attempting to use multiple Dlookup statements to match fields from multiple places as the criteria for the if/then/else statement for the third digit and fifth digit change. I have only ever done this using one Dlookup statement, so I am not sure what I am doing wrong.

I have a roughed out Access Database, containing a number of tables and forms.

I need to use the data stored in the tables to create a "decision tree" that will ultimately guide the user to an answer to his/her decision.

I only have basic knowledge of Microsoft Access and SQL, so I can't wrap my head around how I would accomplish this. Can I do it with a query?

I was hoping to have certain parameters pop up for the user to fill out, which will then lead them to another path in the decision tree. So, if they answer "yes" then follow this path, if they answer "no" then follow this other path...

Any help/advice would be greatly appreciated. I have a rough decision tree sketched out that I am attaching to this post so that you all can have a visual of what I am trying to accomplish.

[sarah1994]

I posted this in a previous thread but I think this forum is the more appropriate place for my question. I have the following code that runs when a button is clicked on a form:

Public Sub MyCode()
Dim strMessage As String
Dim varX As Variant
 
strMessage = "New identifier required?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbYes Then
    strMessage = "Change first digit."
    MsgBox strMessage, vbInformation, "Action Required"
Else
    strMessage = "Major change to a capability?"
    If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbYes Then
        strMessage = "Change second digit."
        MsgBox strMessage, vbInformation, "Action Required"
    Else
        If DLookup("ECN_Class_I_Change", "ENGINEERING_CHANGE_NOTICE_TABLE", "[ECN_Class_I_Change] = -1") Then
            If DLookup("ECN", "ENGINEERING_CHANGE_NOTICE_TABLE", "[ECN] = Forms!DRAWING_STATUS![Subform_DRAWING_STATUS_ECN]!ECN") Then
            strMessage = "Change third digit."
            MsgBox strMessage, vbInformation, "Action Required"
        Else
            strMessage = "Minor change to hardware/drawings?"
            If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbNo Then
                strMessage = "Error. Please try again."
                MsgBox strMessage, vbInformation, "Action Required"
            Else
                If DLookup("PCP", "REVIEW_PANEL_TABLE", "[PCP] = -1") Then
                    If DLookup("GWSHW_BL", "REVIEW_PANEL_TABLE", "[GWSHW_BL] = Forms!DRAWING_STATUS![Subform_DRAWING_STATUS_CONFIGURATION_HWCI_BL]!BL_HWCI_HWCI") Then
                        If DLookup("GWSHW_HW", "REVIEW_PANEL_TABLE", "[GWSHW_HW] = Forms!DRAWING_STATUS![Subform_DRAWING_STATUS_CONFIGURATION_HWCI_BL]!BL_HWCI_HWCI") Then
                    strMessage = "Change fourth digit - HWCI/BL has been certified."
                    MsgBox strMessage, vbInformation, "Action Required"
                Else
                    strMessage = "Change fifth digit"
                    MsgBox strMessage, vbInformation, "Action Required"
                
                End If
            End If
        End If
    End If
End If
End If
End If
End If
End Sub

I bolded the sections that I think are causing the issue. When the button is clicked, it runs through the first two If statements without issue. However, nothing happens after. There is no error or message box. I am attempting to use multiple Dlookup statements to match fields from multiple places as the criteria for the if/then/else statement. I have only ever done this using one Dlookup statement, so I am not sure what I am doing wrong or if Dlookup functions can be combined this way.

[orange]

sarah,
No need to create a new thread for same problem/issue. Better to merge the threads and keep the dialog together for background and context.

[sarah1994]

Thanks, didn't realize that was an option!

I have broken down my code to figure out which area is causing the problem.

I am getting a type mismatch error with the following:
If DLookup("ECN", "ENGINEERING_CHANGE_NOTICE_TABLE", "[ECN] = Forms!DRAWING_INFO_FORM!Subform_DRAWING_STATUS!Sub form_DRAWING_STATUS_ECN!ECN")

I am trying to find matches between the "ECN" field from a table, with the "ECN" field in a subform ,within a subform, within a form.

[orange]

You could try (untested!!) if ECN is text.

If DLookup("ECN", "ENGINEERING_CHANGE_NOTICE_TABLE", "[ECN] ='" & Forms!DRAWING_INFO_FORM!Subform_DRAWING_STATUS!Sub form_DRAWING_STATUS_ECN!ECN & "'")

[Micron]

Some possibilites:
- you are not dealing with the possibility that the DLookup returns a null or empty string. Check that the function returns a valid value. Also consider Nz function.
- mainly, it may be that your lookup is incorrect as you're saying
"Look in myField in myTable where something = -1. Let's say that returns "testing" from myField. Then it becomes
IF "testing" Then...
Does that make sense? I dunno, maybe for what you're doing it does, but it looks fishy.

So, insert a message box such as
Msgbox DLookup("ECN_Class_I_Change", "ENGINEERING_CHANGE_NOTICE_TABLE", "[ECN_Class_I_Change] = -1")
or step through and debug.print and see if the result makes sense in the context of your IF statements.

Also suggest you assign such expressions to properly declared variables. When stepping through your code,
- it is easier to see the returned value by mousing over it, or see its value in the expression window, or debug.print to the immediate window
- it is easier to assign the returned value to any other operation
- it is easier to make comparisons (=, >, etc) and incorporate those in IF or SELECT CASE blocks
- it makes your code easier to read
e.g.
Dim strMyTextValue (you'd use a more meaningful and shorter name)

strMyTextValue = DLookup("ECN_Class_I_Change", "ENGINEERING_CHANGE_NOTICE_TABLE", "[ECN_Class_I_Change] = -1")
IF strMyTextValue = "testing" Then...