axertis
07-10-2015, 01:39 PM
Hi, this is my first post.
Given a table in which records represent alarms, another table contains records in which represent immediate actions to be taken in the event of the alarm.
In a continuous form, I would like to provide the user the ability to sequence the steps as desired by using a promote, demote, insert-step-below and delete button.
In short, I want to emulate the way microsoft word does outline numbering.
13900
I'm new at this, or at least it's been a long time since I've last programmed, so I'm very limited in what functions might help me accomplish this.
Originally, I had the following tables:
tblLOOP_DEF
TEMP_NAME (PK)
...
tblIA_LOOP_DEF
ID (PK)
TEMP_NAME(FK)
STEP_NUMBER (1)
STEP_INDENT_1 (1)
STEP_INDENT_2 (2)
STEP_INDENT_3 (1)
STEP_INDENT_4 (2)
STEP_INDENT_5 (3)
STEP_NUM_STR (calculated, from previous 6 fields 1.1.2.1.2.3)
I used the following expression for the calculation:
IIf(Not IsNull([STEP_INDENT_1])," ","") & IIf(Not IsNull([STEP_INDENT_2])," ","") & IIf(Not IsNull([STEP_INDENT_3])," ","") & IIf(Not IsNull([STEP_INDENT_4])," ","") & IIf(Not IsNull([STEP_INDENT_5])," ","") & [STEP_NUMBER] & IIf(Not IsNull([STEP_INDENT_1]),"." & [STEP_INDENT_1],"") & IIf(Not IsNull([STEP_INDENT_2]),"." & [STEP_INDENT_2],"") & IIf(Not IsNull([STEP_INDENT_3]),"." & [STEP_INDENT_3],"") & IIf(Not IsNull([STEP_INDENT_4]),"." & [STEP_INDENT_4],"") & IIf(Not IsNull([STEP_INDENT_5]),"." & [STEP_INDENT_5],"")
which helped get the indented look, to go with the mubering format.
STEP_DESCRITPTION (this is where the procedure steps are written)
I used the following expression for record level validation:
([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Not Null And [STEP_INDENT_3] Is Not Null And [STEP_INDENT_4] Is Not Null And [STEP_INDENT_5] Is Not Null) Or ([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Not Null And [STEP_INDENT_3] Is Not Null And [STEP_INDENT_4] Is Not Null And [STEP_INDENT_5] Is Null) Or ([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Not Null And [STEP_INDENT_3] Is Not Null And [STEP_INDENT_4] Is Null And [STEP_INDENT_5] Is Null) Or ([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Not Null And [STEP_INDENT_3] Is Null And [STEP_INDENT_4] Is Null And [STEP_INDENT_5] Is Null) Or ([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Null And [STEP_INDENT_3] Is Null And [STEP_INDENT_4] Is Null And [STEP_INDENT_5] Is Null) Or ([STEP_INDENT_1] Is Null And [STEP_INDENT_2] Is Null And [STEP_INDENT_3] Is Null And [STEP_INDENT_4] Is Null And [STEP_INDENT_5] Is Null)
I used the following code in a continuous for to successfully populate the next level of the outline number, but there are too many problems on the horizon:
Option Compare Database
Private strStepNum As String
Private Sub Command190_Click()
Dim i As Integer
Dim Position As String
Dim HighestPosition As Integer
If Not IsNull(txtSTEP_INDENT_5) Then
HighestPosition = 5
ElseIf Not IsNull(txtSTEP_INDENT_4) Then
HighestPosition = 4
ElseIf Not IsNull(txtSTEP_INDENT_3) Then
HighestPosition = 3
ElseIf Not IsNull(txtSTEP_INDENT_2) Then
HighestPosition = 2
ElseIf Not IsNull(txtSTEP_INDENT_1) Then
HighestPosition = 1
ElseIf Not IsNull(txtSTEP_NUMBER) Then
HighestPosition = 0
Else
HighestPosition = 99
End If
NewPosition (HighestPosition)
'MsgBox ("wait")
End Sub
Sub NewPosition(Indent As Integer)
Dim strwhere As String
Select Case Indent
Case 5
'do nothing
Case 4
'NewPosition = 5
'Look for other cases where all previous indents match
strwhere = "STEP_NUMBER = " & [STEP_NUMBER] & " AND " & "STEP_INDENT_1 = " & [STEP_INDENT_1] & " AND " & "STEP_INDENT_2 = " & [STEP_INDENT_2] & " AND " & "STEP_INDENT_3 = " & [STEP_INDENT_3] & " AND " & "STEP_INDENT_4 = " & [STEP_INDENT_4] & " AND " & "STEP_INDENT_5 = " & 1 & ""
If (DCount("ID", "tblIA_LOOP_DEF", strwhere)) > 0 Then
'if match then find the greatest number in the next "indent" and add one to it
Dim HighIndent As Long
strwhere = "STEP_NUMBER = " & [STEP_NUMBER] & " AND " & "STEP_INDENT_1 = " & [STEP_INDENT_1] & " AND " & "STEP_INDENT_2 = " & [STEP_INDENT_2] & " AND " & "STEP_INDENT_3 = " & [STEP_INDENT_3] & " AND " & "STEP_INDENT_4 = " & [STEP_INDENT_4] & ""
HighIndent = DMax("STEP_INDENT_5", "tblIA_LOOP_DEF", strwhere)
[STEP_INDENT_5] = HighIndent + 1
Else
'make sure skipping an indent/number is not allowed
strwhere = "STEP_NUMBER = " & [STEP_NUMBER] & " AND " & "STEP_INDENT_1 = " & [STEP_INDENT_1] & " AND " & "STEP_INDENT_2 = " & [STEP_INDENT_2] & " AND " & "STEP_INDENT_3 = " & [STEP_INDENT_3] & " AND " & "STEP_INDENT_4 = " & [STEP_INDENT_4] & ""
'if(
'set the next indent in this record to 1
[STEP_INDENT_5] = 1
End If
(Case 3 through Case Else were not finished)
End Select
End Sub
it was a work in progress, but it was the wrong direction to take, because it would have problems addressing the following:
inserting a new record =>reorder steps
reordering all subsequent steps on cmdDEMOTE or cmdPROMOTE
preventing the possibility of having 1.1 and 1.1.1.1 without 1.1.1
So I think the answer is to make a major change. give all steps two main fields to go with the step description: a sequence number and step "level".
Step values interpreted by code in form and sent back to form as
Result
STEP_SEQUENCE
STEP_LEVEL
Level 0
Level 1
Level 2
Level 3
Level 4
Level 5
STEP_NUM_STR
1
0
1
1
2
0
2
2
3
1
2
1
2.1
4
1
2
2
2.2
5
2
2
2
1
2.2.1
6
2
2
2
2
2.2.2
7
3
2
2
2
1
2.2.2.1
8
4
2
2
2
1
1
2.2.2.1.1
9
3
2
2
2
2
2.2.2.2
10
4
2
2
2
2
1
2.2.2.2.1
11
1
3
3
12
1
4
4
13
2
4
1
4.1
14
3
4
1
1
4.1.1
15
3
4
1
2
4.1.2
I'm thinking cmdPROMOTE will make step level go up only by one higher than the STEP_SEQUENCE - 1 record. Basically going down the list (sorted by sequence) level can only go up by 1 each record.
cmdDEMOTE can drop it all of the way to level zero, but the sequence value has to be reassigned to all subsequent records in the table.
cmdINSERTBELOW would work similarly to cmdDEMOTE assigning STEP_SEQUENCE + 1 to the new record, and reassigning all subsequent.
I see a for loop possibly being what I need to reassign sequence numbers, but I am fuzzy on how that would look like.
Then with each cmdBUTTON operation, the whole form would have to re-evaluate (calculate) the STEP_NUM_STR field starting from the top, based on the sequence number and step level.
any advice or pointer on what that would look like?
Given a table in which records represent alarms, another table contains records in which represent immediate actions to be taken in the event of the alarm.
In a continuous form, I would like to provide the user the ability to sequence the steps as desired by using a promote, demote, insert-step-below and delete button.
In short, I want to emulate the way microsoft word does outline numbering.
13900
I'm new at this, or at least it's been a long time since I've last programmed, so I'm very limited in what functions might help me accomplish this.
Originally, I had the following tables:
tblLOOP_DEF
TEMP_NAME (PK)
...
tblIA_LOOP_DEF
ID (PK)
TEMP_NAME(FK)
STEP_NUMBER (1)
STEP_INDENT_1 (1)
STEP_INDENT_2 (2)
STEP_INDENT_3 (1)
STEP_INDENT_4 (2)
STEP_INDENT_5 (3)
STEP_NUM_STR (calculated, from previous 6 fields 1.1.2.1.2.3)
I used the following expression for the calculation:
IIf(Not IsNull([STEP_INDENT_1])," ","") & IIf(Not IsNull([STEP_INDENT_2])," ","") & IIf(Not IsNull([STEP_INDENT_3])," ","") & IIf(Not IsNull([STEP_INDENT_4])," ","") & IIf(Not IsNull([STEP_INDENT_5])," ","") & [STEP_NUMBER] & IIf(Not IsNull([STEP_INDENT_1]),"." & [STEP_INDENT_1],"") & IIf(Not IsNull([STEP_INDENT_2]),"." & [STEP_INDENT_2],"") & IIf(Not IsNull([STEP_INDENT_3]),"." & [STEP_INDENT_3],"") & IIf(Not IsNull([STEP_INDENT_4]),"." & [STEP_INDENT_4],"") & IIf(Not IsNull([STEP_INDENT_5]),"." & [STEP_INDENT_5],"")
which helped get the indented look, to go with the mubering format.
STEP_DESCRITPTION (this is where the procedure steps are written)
I used the following expression for record level validation:
([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Not Null And [STEP_INDENT_3] Is Not Null And [STEP_INDENT_4] Is Not Null And [STEP_INDENT_5] Is Not Null) Or ([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Not Null And [STEP_INDENT_3] Is Not Null And [STEP_INDENT_4] Is Not Null And [STEP_INDENT_5] Is Null) Or ([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Not Null And [STEP_INDENT_3] Is Not Null And [STEP_INDENT_4] Is Null And [STEP_INDENT_5] Is Null) Or ([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Not Null And [STEP_INDENT_3] Is Null And [STEP_INDENT_4] Is Null And [STEP_INDENT_5] Is Null) Or ([STEP_INDENT_1] Is Not Null And [STEP_INDENT_2] Is Null And [STEP_INDENT_3] Is Null And [STEP_INDENT_4] Is Null And [STEP_INDENT_5] Is Null) Or ([STEP_INDENT_1] Is Null And [STEP_INDENT_2] Is Null And [STEP_INDENT_3] Is Null And [STEP_INDENT_4] Is Null And [STEP_INDENT_5] Is Null)
I used the following code in a continuous for to successfully populate the next level of the outline number, but there are too many problems on the horizon:
Option Compare Database
Private strStepNum As String
Private Sub Command190_Click()
Dim i As Integer
Dim Position As String
Dim HighestPosition As Integer
If Not IsNull(txtSTEP_INDENT_5) Then
HighestPosition = 5
ElseIf Not IsNull(txtSTEP_INDENT_4) Then
HighestPosition = 4
ElseIf Not IsNull(txtSTEP_INDENT_3) Then
HighestPosition = 3
ElseIf Not IsNull(txtSTEP_INDENT_2) Then
HighestPosition = 2
ElseIf Not IsNull(txtSTEP_INDENT_1) Then
HighestPosition = 1
ElseIf Not IsNull(txtSTEP_NUMBER) Then
HighestPosition = 0
Else
HighestPosition = 99
End If
NewPosition (HighestPosition)
'MsgBox ("wait")
End Sub
Sub NewPosition(Indent As Integer)
Dim strwhere As String
Select Case Indent
Case 5
'do nothing
Case 4
'NewPosition = 5
'Look for other cases where all previous indents match
strwhere = "STEP_NUMBER = " & [STEP_NUMBER] & " AND " & "STEP_INDENT_1 = " & [STEP_INDENT_1] & " AND " & "STEP_INDENT_2 = " & [STEP_INDENT_2] & " AND " & "STEP_INDENT_3 = " & [STEP_INDENT_3] & " AND " & "STEP_INDENT_4 = " & [STEP_INDENT_4] & " AND " & "STEP_INDENT_5 = " & 1 & ""
If (DCount("ID", "tblIA_LOOP_DEF", strwhere)) > 0 Then
'if match then find the greatest number in the next "indent" and add one to it
Dim HighIndent As Long
strwhere = "STEP_NUMBER = " & [STEP_NUMBER] & " AND " & "STEP_INDENT_1 = " & [STEP_INDENT_1] & " AND " & "STEP_INDENT_2 = " & [STEP_INDENT_2] & " AND " & "STEP_INDENT_3 = " & [STEP_INDENT_3] & " AND " & "STEP_INDENT_4 = " & [STEP_INDENT_4] & ""
HighIndent = DMax("STEP_INDENT_5", "tblIA_LOOP_DEF", strwhere)
[STEP_INDENT_5] = HighIndent + 1
Else
'make sure skipping an indent/number is not allowed
strwhere = "STEP_NUMBER = " & [STEP_NUMBER] & " AND " & "STEP_INDENT_1 = " & [STEP_INDENT_1] & " AND " & "STEP_INDENT_2 = " & [STEP_INDENT_2] & " AND " & "STEP_INDENT_3 = " & [STEP_INDENT_3] & " AND " & "STEP_INDENT_4 = " & [STEP_INDENT_4] & ""
'if(
'set the next indent in this record to 1
[STEP_INDENT_5] = 1
End If
(Case 3 through Case Else were not finished)
End Select
End Sub
it was a work in progress, but it was the wrong direction to take, because it would have problems addressing the following:
inserting a new record =>reorder steps
reordering all subsequent steps on cmdDEMOTE or cmdPROMOTE
preventing the possibility of having 1.1 and 1.1.1.1 without 1.1.1
So I think the answer is to make a major change. give all steps two main fields to go with the step description: a sequence number and step "level".
Step values interpreted by code in form and sent back to form as
Result
STEP_SEQUENCE
STEP_LEVEL
Level 0
Level 1
Level 2
Level 3
Level 4
Level 5
STEP_NUM_STR
1
0
1
1
2
0
2
2
3
1
2
1
2.1
4
1
2
2
2.2
5
2
2
2
1
2.2.1
6
2
2
2
2
2.2.2
7
3
2
2
2
1
2.2.2.1
8
4
2
2
2
1
1
2.2.2.1.1
9
3
2
2
2
2
2.2.2.2
10
4
2
2
2
2
1
2.2.2.2.1
11
1
3
3
12
1
4
4
13
2
4
1
4.1
14
3
4
1
1
4.1.1
15
3
4
1
2
4.1.2
I'm thinking cmdPROMOTE will make step level go up only by one higher than the STEP_SEQUENCE - 1 record. Basically going down the list (sorted by sequence) level can only go up by 1 each record.
cmdDEMOTE can drop it all of the way to level zero, but the sequence value has to be reassigned to all subsequent records in the table.
cmdINSERTBELOW would work similarly to cmdDEMOTE assigning STEP_SEQUENCE + 1 to the new record, and reassigning all subsequent.
I see a for loop possibly being what I need to reassign sequence numbers, but I am fuzzy on how that would look like.
Then with each cmdBUTTON operation, the whole form would have to re-evaluate (calculate) the STEP_NUM_STR field starting from the top, based on the sequence number and step level.
any advice or pointer on what that would look like?