VBA Programming of P_I Matrix in Access

[Bretz217]

Hello!

I am trying to program a Probability and Impact Matrix (matrix that is used in Project Management to determine the level of a risk) in Access. I have the Matrix on a subform, and need to access my primary spreadsheet (no looping, just for each specific risk) to get the probability and impact. To update the matrix, I am doing a macro in a button. I am somewhat new to Access and very rusty on programming (12+ years!)...I believe I have the logic below, but the code isn't right...please see the code below...thanks!!!

[code]
Private Sub Command75_Click()

Dim dbRiskInformation As DAO.Database
Dim dbPath As String
dbPath = CurrentProject.Path & "Database1.mdb"
Dim ProbValue As DAO.Recordset
Dim ImpValue As DAO.Recordset
Dim RiskIDNum As DAO.Recordset2
Set ProbValue = dbRiskInformation.OpenRecordset("Probability", dbOpenTable)
Set ImpValue = dbRiskInformation.OpenRecordset("Impact", dbOpenTable)
Set RiskIDNum = dbRiskInformation.OpenRecordset("RiskID", dbOpenTable)

Select Case PIValue
Case ProbValue = "1" And ImpValue = "1"
Box60.Caption = RiskIDNum
Case ProbValue = 2 And ImpValue = 1
Box55.Caption = RiskIDNum
Case ProbValue = 3 And ImpValue = 1
Box50.Caption = RiskIDNum
Case ProbValue = 4 And ImpValue = 1
Box45.Caption = RiskIDNum
Case ProbValue = 5 And ImpValue = 1
Box39.Caption = RiskIDNum
Case ProbValue = 1 And ImpValue = 2
Box61.Caption = RiskIDNum
Case ProbValue = 2 And ImpValue = 2
Box56.Caption = RiskIDNum
Case ProbValue = 3 And ImpValue = 2
Box51.Caption = RiskIDNum
Case ProbValue = 4 And ImpValue = 2
Box46.Caption = RiskIDNum
Case ProbValue = 5 And ImpValue = 2
Box40.Caption = RiskIDNum
Case ProbValue = 1 And ImpValue = 3
Box62.Caption = RiskIDNum
Case ProbValue = 2 And ImpValue = 3
Box57.Caption = RiskIDNum
Case ProbValue = 3 And ImpValue = 3
Box52.Caption = RiskIDNum
Case ProbValue = 4 And ImpValue = 3
Box47.Caption = RiskIDNum
Case ProbValue = 5 And ImpValue = 3
Box41.Caption = RiskIDNum
Case ProbValue = 1 And ImpValue = 4


Box63.Caption = RiskIDNum
Case ProbValue = 2 And ImpValue = 4
Box58.Caption = RiskIDNum
Case ProbValue = 3 And ImpValue = 4
Box53.Caption = RiskIDNum
Case ProbValue = 4 And ImpValue = 4
Box48.Caption = RiskIDNum
Case ProbValue = 5 And ImpValue = 4
Box43.Caption = RiskIDNum
Case ProbValue = 1 And ImpValue = 5
Box64.Caption = RiskIDNum
Case ProbValue = 2 And ImpValue = 5
Box59.Caption = RiskIDNum
Case ProbValue = 3 And ImpValue = 5
Box54.Caption = RiskIDNum
Case ProbValue = 4 And ImpValue = 5
Box49.Caption = RiskIDNum
Case ProbValue = 5 And ImpValue = 5
Box44.Caption = RiskIDNum
Case Else: MsgBox ("No Value Found!")

End Select
End Sub
[end code]

[June7]

Have you step debugged? Have you compiled?

Will each recordset return only one record? Are the box controls labels or textboxes?

Your Case structure is wrong. Maybe this is what you want:

Select Case ProbValue!Probability & ImpValue!Impact
Case 11
Me.Box60 = RiskIDNum!RiskID
Case 21
...
End Select

[sygnus21]

Have you step debugged? Have you compiled?

Will each recordset return only one record? Are the box controls labels or textboxes?

Your Case structure is wrong. Maybe this is what you want:

Select Case ProbValue!Probability & ImpValue!Impact
Case 11
Me.Box60 = RiskIDNum!RiskID
Case 21
...
End Select

OK, I know nothing about VBA but would be willing to learn. That said oddly enough someone tasked me with this same problem, guess they think I'm up to the challenge.

Anyway can you post a small example of how your structure would look using the OP's post? And would it be duplicated for each value.

Also how would I.....

1- step debug?
2- Have you compile?

Thanks.

[June7]

For debugging review the link at bottom of my post.

For programming review: http://office.microsoft.com/en-us/ac...010341717.aspx

I already provided sample based on OP's post. Continue the structure where the ellipses (...) are placed. More about Select Case: http://www.blueclaw-db.com/select_case.htm

[Bretz217]

OK...so I tried the code and it didn't work. I am not getting a type mismatch for the Impact value...both the Impact and Probability are set to general number...my revised code below

Private Sub Command75_Click()
Dim dbRiskInformation As DAO.Database
Dim dbPath As String
Dim rsProbValue As DAO.Recordset
Dim rsRiskIDNumm As DAO.Recordset
Dim rsImpact As DAO.Recordset
dbPath = CurrentProject.Path & "Database1.mdb"
Set dbRiskInformation = db.OpenRecordset("Risk Information", dbOpenDynaset)
Set rsRiskIDNum = dbRiskInformation.OpenRecordset("RiskID")
Set rsProbValue = dbRiskInformation.OpenRecordset("Probability")
Set rsImpact = dbRiskInformation.OpenRecordset("Impact")
Select Case PIValue
Case rsProbValue!Probabilty = 1
Case rsImpact!Impact = 1
Me.Box60 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 2
Me.Box61 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 3
Me.Box62 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 4
Me.Box63 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 5
Me.Box64 = rsRiskIDNum!RiskIDNum

Case rsProbValue!Probability = 2
Case rsImpact!Impact = 1
Me.Box55 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 2
Me.Box56 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 3
Me.Box57 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 4
Me.Box58 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 5
Me.Box59 = rsRiskIDNum!RiskIDNum

Case rsProbValue!Probability = 3
Case rsImpact!Impact = 1
Me.Box50 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 2
Me.Box51 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 3
Me.Box52 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 4
Me.Box53 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 5
Me.Box54 = rsRiskIDNum!RiskIDNum

Case rsProbValue!Probability = 4
Case rsImpact!Impact = 1
Me.Box45 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 2
Me.Box46 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 3
Me.Box47 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 4
Me.Box48 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 5
Me.Box49 = rsRiskIDNum!RiskIDNum

Case rsProbValue!Probability = 5
Case rsImpact!Impact = 1
Me.Box39 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 2
Me.Box40 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 3
Me.Box41 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 4
Me.Box43 = rsRiskIDNum!RiskIDNum
Case rsImpact!Impact = 5
Me.Box44 = rsRiskIDNum!RiskIDNum
Case Else: MsgMe.Box ("No Value Found!")

dbRiskInformation.Close
rsProbValue!Probability.Close
rsRiskIDNum!RiskIDNum.Close
rsImpact!Impact.Close

End Select
End Sub

[Bretz217]

I meant that I AM getting a type mismatch for the rsImpact in the Case statement

[RayMilhon]

Your Case statement is still wrong.

generic Example

Select Case Variable
     Case is = 1
            status = "Approved"           
     Case is = 2
           Status = "Denied"
     Case is = 3, is = 4
           Status = "Deferred"
     Case 5 to 10
           Status = "Pending"           
End Select

You cannot have

Select Case Variable
      Case field2 = 12
          Status = "Approved"
      Case field3 = 13
          Status = .........
End select

[June7]

Right, your case is all wrong.

What is PIValue?

Are you trying to do a nested Select Case?

Select Case won't always work best. Sometimes must use: If Then ElseIf Else End If

I need more info on what you need to do. You are updating table records through recordset? The code doesn't show looping through recordset, so deals only with the first record.

[Bretz217]

So I took out the PIValue (was in there from when I was trying before) and changed everything to If/ElseIF stmts (more familiary with those from ADA anyways). I compiled it and it compiled OK...when I ran it, it keeps saying object not found. I believe that it's not opening my database correctly.

What I am trying to do is update a Probability and Impact Matrix, which is a five by five color grid, with the riskID number. Therefore if a Risk has a Probability of 3 and an Impact of 3, it will end up smack in the middle of the matrix. These values are selected by the user, and repeated in a tabbed form after they input. Once they select update P_I Matrix button (which is where the macro is), the RiskID number should go in the correct box in the matrix. I am also going to assign the macro to a PrintReport Button that will run on generation. I don't want it loop because it is unique for each Risk Number, along with the report.

Is there an issue if the VBA reference is not selected to Microsoft VBA 6.0???

code:
Private Sub Command75_Click()
Dim dbRiskInformation As DAO.Database
Dim dbPath As String
Dim rsProbValue As DAO.Recordset
Dim rsRiskIDNumm As DAO.Recordset
Dim rsImpact As DAO.Recordset
dbPath = "D:\Documents and Settings\my.name\My Documents\Database1.accdb"
Set dbRiskInformation = OpenDatabase(dbPath)
Set rsRiskIDNum = dbRiskInformation.OpenRecordset("RiskID", dbOpenTable)
Set rsProbValue = dbRiskInformation.OpenRecordset("Probability", dbOpenTable)
Set rsImpact = dbRiskInformation.OpenRecordset("Impact", dbOpenTable)
If rsProbValue!Probabilty = 1 Then
If rsImpact!Impact = 1 Then
Me.Box60 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 2 Then
Me.Box61 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 3 Then
Me.Box62 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 4 Then
Me.Box63 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 5 Then
Me.Box64 = rsRiskIDNum!RiskIDNum
End If

ElseIf rsProbValue!Probability = 2 Then
If rsImpact!Impact = 1 Then
Me.Box55 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 2 Then
Me.Box56 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 3 Then
Me.Box57 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 4 Then
Me.Box58 = rsRiskIDNum!RiskIDNum
ElseIf sImpact!Impact = 5 Then
Me.Box59 = rsRiskIDNum!RiskIDNum
End If

ElseIf rsProbValue!Probability = 3 Then
If rsImpact!Impact = 1 Then
Me.Box50 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 2 Then
Me.Box51 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 3 Then
Me.Box52 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 4 Then
Me.Box53 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 5 Then
Me.Box54 = rsRiskIDNum!RiskIDNum
End If

ElseIf rsProbValue!Probability = 4 Then
If rsImpact!Impact = 1 Then
Me.Box45 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 2 Then
Me.Box46 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 3 Then
Me.Box47 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 4 Then
Me.Box48 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 5 Then
Me.Box49 = rsRiskIDNum!RiskIDNum
End If

ElseIf rsProbValue!Probability = 5 Then
If rsImpact!Impact = 1 Then
Me.Box39 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 2 Then
Me.Box40 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 3 Then
Me.Box41 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 4 Then
Me.Box43 = rsRiskIDNum!RiskIDNum
ElseIf rsImpact!Impact = 5 Then
Me.Box44 = rsRiskIDNum!RiskIDNum
End If
Else: MsgMe.Box ("No Value Found!")
End If
dbRiskInformation.Close
rsProbValue!Probability.Close
rsRiskIDNum!RiskIDNum.Close
rsImpact!Impact.Close

End Sub

[June7]

I don't see any such Reference. There is a Visual Basic for Applications reference that should be selected.

Is this code populating bound textboxes? Possibly expressions in textboxes would accomplish what you want if controls are not bound.

Are you trying to open another db or just connection to the current db?

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[Bretz217]

OK...hopefully last time!!!

After taking out some of the stuff and doing the If stmts, as you suggested, I believe I have only one error. I can tell that my file is being read, and that the program is going to the if statements with the right values...

I need to place the RiskID Number INTO the rectangle assigned under the if statement it is applicable to...here is what I have now...

CODE:
ElseIf rsRiskInformation!Probability = 3 Then
If rsRiskInformation!Impact = 1 Then
' The statement below is where I believe my error is...
Me.Box50 = rsRiskInformation!RiskID

[ssanfu]

Me.Box50 = rsRiskInformation!RiskID

You cannot set a text box (or whatever Me.Box50 is) equal to a recordset!

If rsProbValue!Probabilty = 1 Then
        If rsImpact!Impact = 1 Then

You cannot compare a recordset to a value!

You are opening the recordset on a table with no filtering or sorting. How do you know which record you are comparing or looking for? You don't even have a field name to get the value from.....

[Bretz217]

Steve,

I believe you may be correct with your first response, however I am able to do the second...the value does, in fact, compare, which I can see in my window.

I may have to change the boxes to text boxes vice shapes in order to make it work...

Thanks for your response!

[June7]

ssanfu, I assumed Impact is name of table as well as a field in that table. Though, could be wrong. Boxes are being set to a field of recordset, not the entire recordset and the comparison is also with a field of recordset.

Bretz, we are just going in circles. Provide the db for analysis or can't help further.

[ssanfu]

My bad. I got cross-eyed looking at all those Case and If statements.

I was looking at post #1 but copied from post #9.