An Alternate Method...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On several of my forms I have constructed a table structure to hold various
peices of information related to the same fields. For example, I may list a
varying number of topics on a form (as many as 12), and next to each one a
label that contains information which changes based on user input.

So often when I have tried to fill information in my pseudo-table, I have
resorted to creating an array that contains each label in the column. In
this way, I am able to cycle through each topic name and enter the data
accordingly. However, as my program has grown more complex, these arrays
have become more and more cumbersome.

Here is an example of one of the more basic uses of the arrays that I
construct:

Sub Enable_Dis(Number As Integer, Action As String)
On Error GoTo Err_Enable_Dis

Dim arrMed As Variant
arrMed = Array(Forms![Breakdown]!MDis1, Forms![Breakdown]!MDis2,
Forms![Breakdown]!MDis3, Forms![Breakdown]!MDis4, Forms![Breakdown]!MDis5,
Forms![Breakdown]!MDis6, Forms![Breakdown]!MDis7, Forms![Breakdown]!MDis8,
Forms![Breakdown]!MDis9, Forms![Breakdown]!MDis10, Forms![Breakdown]!MDis11,
Forms![Breakdown]!MDis12)

Dim arrPath As Variant
arrPath = Array(Forms![Breakdown]!PDis1, Forms![Breakdown]!PDis2,
Forms![Breakdown]!PDis3, Forms![Breakdown]!PDis4, Forms![Breakdown]!PDis5,
Forms![Breakdown]!PDis6, Forms![Breakdown]!PDis7, Forms![Breakdown]!PDis8,
Forms![Breakdown]!PDis9, Forms![Breakdown]!PDis10, Forms![Breakdown]!PDis11,
Forms![Breakdown]!PDis12)

Dim arrPharm As Variant
arrPharm = Array(Forms![Breakdown]!HDis1, Forms![Breakdown]!HDis2,
Forms![Breakdown]!HDis3, Forms![Breakdown]!HDis4, Forms![Breakdown]!HDis5,
Forms![Breakdown]!HDis6, Forms![Breakdown]!HDis7, Forms![Breakdown]!HDis8,
Forms![Breakdown]!HDis9, Forms![Breakdown]!HDis10, Forms![Breakdown]!HDis11,
Forms![Breakdown]!HDis12)

Dim MLabel As Variant
MLabel = Array(Forms!Breakdown!lblM1, Forms!Breakdown!lblM2,
Forms!Breakdown!lblM3, Forms!Breakdown!lblM4, Forms!Breakdown!lblM5,
Forms!Breakdown!lblM6, Forms!Breakdown!lblM7, Forms!Breakdown!lblM8,
Forms!Breakdown!lblM9, Forms!Breakdown!lblM10, Forms!Breakdown!lblM11,
Forms!Breakdown!lblM12)

Dim PLabel As Variant
PLabel = Array(Forms!Breakdown!lblP1, Forms!Breakdown!lblP2,
Forms!Breakdown!lblP3, Forms!Breakdown!lblP4, Forms!Breakdown!lblP5,
Forms!Breakdown!lblP6, Forms!Breakdown!lblP7, Forms!Breakdown!lblP8,
Forms!Breakdown!lblP9, Forms!Breakdown!lblP10, Forms!Breakdown!lblP11,
Forms!Breakdown!lblP12)

Dim HLabel As Variant
HLabel = Array(Forms!Breakdown!lblH1, Forms!Breakdown!lblH2,
Forms!Breakdown!lblH3, Forms!Breakdown!lblH4, Forms!Breakdown!lblH5,
Forms!Breakdown!lblH6, Forms!Breakdown!lblH7, Forms!Breakdown!lblH8,
Forms!Breakdown!lblH9, Forms!Breakdown!lblH10, Forms!Breakdown!lblH11,
Forms!Breakdown!lblH12)

Dim arrPercent As Variant
arrPercent = Array(Forms![Breakdown]!Percent1, Forms![Breakdown]!Percent2,
Forms![Breakdown]!Percent3, Forms![Breakdown]!Percent4,
Forms![Breakdown]!Percent5, Forms![Breakdown]!Percent6,
Forms![Breakdown]!Percent7, Forms![Breakdown]!Percent8,
Forms![Breakdown]!Percent9, Forms![Breakdown]!Percent10,
Forms![Breakdown]!Percent11, Forms![Breakdown]!Percent12)

Dim x As Integer

Forms!Breakdown!MDis1 = ""

Select Case Action
Case "enable"
For x = 1 To Number
arrMed(x).Enabled = True
arrPath(x).Enabled = True
arrPharm(x).Enabled = True
Next
Case "disable"
For x = 1 To Number
arrMed(x).Enabled = False
arrPath(x).Enabled = False
arrPharm(x).Enabled = False
MLabel(x).Caption = "Max:"
PLabel(x).Caption = "Max:"
HLabel(x).Caption = "Max:"
Next
Case "clear"
For x = 1 To Number
arrPercent(x).Caption = ""
Next
End Select

End Sub

It is so much time and energy, and all I really want to do is find out how
many topics there are in a given subject on the current form, and set each
control's attributes accordingly. I know that I am not doing this the most
efficient way, but I'm stuck on an alternate method. Can anyone suggest an
alternate way of dealing with this problem?

Many thanks in advance.

Joe
 
Hi Joe,

I suspect the answer is to use a subform in continuous view.

But the way you talk about having "constructed a table structure" on "my
forms" to "hold various pieces of information related to the same
fields" makes me feel that you haven't yet got a properly normalised
data structure.

On several of my forms I have constructed a table structure to hold various
peices of information related to the same fields. For example, I may list a
varying number of topics on a form (as many as 12), and next to each one a
label that contains information which changes based on user input.

So often when I have tried to fill information in my pseudo-table, I have
resorted to creating an array that contains each label in the column. In
this way, I am able to cycle through each topic name and enter the data
accordingly. However, as my program has grown more complex, these arrays
have become more and more cumbersome.

Here is an example of one of the more basic uses of the arrays that I
construct:

Sub Enable_Dis(Number As Integer, Action As String)
On Error GoTo Err_Enable_Dis

Dim arrMed As Variant
arrMed = Array(Forms![Breakdown]!MDis1, Forms![Breakdown]!MDis2,
Forms![Breakdown]!MDis3, Forms![Breakdown]!MDis4, Forms![Breakdown]!MDis5,
Forms![Breakdown]!MDis6, Forms![Breakdown]!MDis7, Forms![Breakdown]!MDis8,
Forms![Breakdown]!MDis9, Forms![Breakdown]!MDis10, Forms![Breakdown]!MDis11,
Forms![Breakdown]!MDis12)

Dim arrPath As Variant
arrPath = Array(Forms![Breakdown]!PDis1, Forms![Breakdown]!PDis2,
Forms![Breakdown]!PDis3, Forms![Breakdown]!PDis4, Forms![Breakdown]!PDis5,
Forms![Breakdown]!PDis6, Forms![Breakdown]!PDis7, Forms![Breakdown]!PDis8,
Forms![Breakdown]!PDis9, Forms![Breakdown]!PDis10, Forms![Breakdown]!PDis11,
Forms![Breakdown]!PDis12)

Dim arrPharm As Variant
arrPharm = Array(Forms![Breakdown]!HDis1, Forms![Breakdown]!HDis2,
Forms![Breakdown]!HDis3, Forms![Breakdown]!HDis4, Forms![Breakdown]!HDis5,
Forms![Breakdown]!HDis6, Forms![Breakdown]!HDis7, Forms![Breakdown]!HDis8,
Forms![Breakdown]!HDis9, Forms![Breakdown]!HDis10, Forms![Breakdown]!HDis11,
Forms![Breakdown]!HDis12)

Dim MLabel As Variant
MLabel = Array(Forms!Breakdown!lblM1, Forms!Breakdown!lblM2,
Forms!Breakdown!lblM3, Forms!Breakdown!lblM4, Forms!Breakdown!lblM5,
Forms!Breakdown!lblM6, Forms!Breakdown!lblM7, Forms!Breakdown!lblM8,
Forms!Breakdown!lblM9, Forms!Breakdown!lblM10, Forms!Breakdown!lblM11,
Forms!Breakdown!lblM12)

Dim PLabel As Variant
PLabel = Array(Forms!Breakdown!lblP1, Forms!Breakdown!lblP2,
Forms!Breakdown!lblP3, Forms!Breakdown!lblP4, Forms!Breakdown!lblP5,
Forms!Breakdown!lblP6, Forms!Breakdown!lblP7, Forms!Breakdown!lblP8,
Forms!Breakdown!lblP9, Forms!Breakdown!lblP10, Forms!Breakdown!lblP11,
Forms!Breakdown!lblP12)

Dim HLabel As Variant
HLabel = Array(Forms!Breakdown!lblH1, Forms!Breakdown!lblH2,
Forms!Breakdown!lblH3, Forms!Breakdown!lblH4, Forms!Breakdown!lblH5,
Forms!Breakdown!lblH6, Forms!Breakdown!lblH7, Forms!Breakdown!lblH8,
Forms!Breakdown!lblH9, Forms!Breakdown!lblH10, Forms!Breakdown!lblH11,
Forms!Breakdown!lblH12)

Dim arrPercent As Variant
arrPercent = Array(Forms![Breakdown]!Percent1, Forms![Breakdown]!Percent2,
Forms![Breakdown]!Percent3, Forms![Breakdown]!Percent4,
Forms![Breakdown]!Percent5, Forms![Breakdown]!Percent6,
Forms![Breakdown]!Percent7, Forms![Breakdown]!Percent8,
Forms![Breakdown]!Percent9, Forms![Breakdown]!Percent10,
Forms![Breakdown]!Percent11, Forms![Breakdown]!Percent12)

Dim x As Integer

Forms!Breakdown!MDis1 = ""

Select Case Action
Case "enable"
For x = 1 To Number
arrMed(x).Enabled = True
arrPath(x).Enabled = True
arrPharm(x).Enabled = True
Next
Case "disable"
For x = 1 To Number
arrMed(x).Enabled = False
arrPath(x).Enabled = False
arrPharm(x).Enabled = False
MLabel(x).Caption = "Max:"
PLabel(x).Caption = "Max:"
HLabel(x).Caption = "Max:"
Next
Case "clear"
For x = 1 To Number
arrPercent(x).Caption = ""
Next
End Select

End Sub

It is so much time and energy, and all I really want to do is find out how
many topics there are in a given subject on the current form, and set each
control's attributes accordingly. I know that I am not doing this the most
efficient way, but I'm stuck on an alternate method. Can anyone suggest an
alternate way of dealing with this problem?

Many thanks in advance.

Joe
Click to expand...
 
John, do you think you could be a little more specific? I don't know how to
"properly normalise" a data structure.

John Nurick said:
Hi Joe,

I suspect the answer is to use a subform in continuous view.

But the way you talk about having "constructed a table structure" on "my
forms" to "hold various pieces of information related to the same
fields" makes me feel that you haven't yet got a properly normalised
data structure.

On several of my forms I have constructed a table structure to hold various
peices of information related to the same fields. For example, I may list a
varying number of topics on a form (as many as 12), and next to each one a
label that contains information which changes based on user input.

So often when I have tried to fill information in my pseudo-table, I have
resorted to creating an array that contains each label in the column. In
this way, I am able to cycle through each topic name and enter the data
accordingly. However, as my program has grown more complex, these arrays
have become more and more cumbersome.

Here is an example of one of the more basic uses of the arrays that I
construct:

Sub Enable_Dis(Number As Integer, Action As String)
On Error GoTo Err_Enable_Dis

Dim arrMed As Variant
arrMed = Array(Forms![Breakdown]!MDis1, Forms![Breakdown]!MDis2,
Forms![Breakdown]!MDis3, Forms![Breakdown]!MDis4, Forms![Breakdown]!MDis5,
Forms![Breakdown]!MDis6, Forms![Breakdown]!MDis7, Forms![Breakdown]!MDis8,
Forms![Breakdown]!MDis9, Forms![Breakdown]!MDis10, Forms![Breakdown]!MDis11,
Forms![Breakdown]!MDis12)

Dim arrPath As Variant
arrPath = Array(Forms![Breakdown]!PDis1, Forms![Breakdown]!PDis2,
Forms![Breakdown]!PDis3, Forms![Breakdown]!PDis4, Forms![Breakdown]!PDis5,
Forms![Breakdown]!PDis6, Forms![Breakdown]!PDis7, Forms![Breakdown]!PDis8,
Forms![Breakdown]!PDis9, Forms![Breakdown]!PDis10, Forms![Breakdown]!PDis11,
Forms![Breakdown]!PDis12)

Dim arrPharm As Variant
arrPharm = Array(Forms![Breakdown]!HDis1, Forms![Breakdown]!HDis2,
Forms![Breakdown]!HDis3, Forms![Breakdown]!HDis4, Forms![Breakdown]!HDis5,
Forms![Breakdown]!HDis6, Forms![Breakdown]!HDis7, Forms![Breakdown]!HDis8,
Forms![Breakdown]!HDis9, Forms![Breakdown]!HDis10, Forms![Breakdown]!HDis11,
Forms![Breakdown]!HDis12)

Dim MLabel As Variant
MLabel = Array(Forms!Breakdown!lblM1, Forms!Breakdown!lblM2,
Forms!Breakdown!lblM3, Forms!Breakdown!lblM4, Forms!Breakdown!lblM5,
Forms!Breakdown!lblM6, Forms!Breakdown!lblM7, Forms!Breakdown!lblM8,
Forms!Breakdown!lblM9, Forms!Breakdown!lblM10, Forms!Breakdown!lblM11,
Forms!Breakdown!lblM12)

Dim PLabel As Variant
PLabel = Array(Forms!Breakdown!lblP1, Forms!Breakdown!lblP2,
Forms!Breakdown!lblP3, Forms!Breakdown!lblP4, Forms!Breakdown!lblP5,
Forms!Breakdown!lblP6, Forms!Breakdown!lblP7, Forms!Breakdown!lblP8,
Forms!Breakdown!lblP9, Forms!Breakdown!lblP10, Forms!Breakdown!lblP11,
Forms!Breakdown!lblP12)

Dim HLabel As Variant
HLabel = Array(Forms!Breakdown!lblH1, Forms!Breakdown!lblH2,
Forms!Breakdown!lblH3, Forms!Breakdown!lblH4, Forms!Breakdown!lblH5,
Forms!Breakdown!lblH6, Forms!Breakdown!lblH7, Forms!Breakdown!lblH8,
Forms!Breakdown!lblH9, Forms!Breakdown!lblH10, Forms!Breakdown!lblH11,
Forms!Breakdown!lblH12)

Dim arrPercent As Variant
arrPercent = Array(Forms![Breakdown]!Percent1, Forms![Breakdown]!Percent2,
Forms![Breakdown]!Percent3, Forms![Breakdown]!Percent4,
Forms![Breakdown]!Percent5, Forms![Breakdown]!Percent6,
Forms![Breakdown]!Percent7, Forms![Breakdown]!Percent8,
Forms![Breakdown]!Percent9, Forms![Breakdown]!Percent10,
Forms![Breakdown]!Percent11, Forms![Breakdown]!Percent12)

Dim x As Integer

Forms!Breakdown!MDis1 = ""

Select Case Action
Case "enable"
For x = 1 To Number
arrMed(x).Enabled = True
arrPath(x).Enabled = True
arrPharm(x).Enabled = True
Next
Case "disable"
For x = 1 To Number
arrMed(x).Enabled = False
arrPath(x).Enabled = False
arrPharm(x).Enabled = False
MLabel(x).Caption = "Max:"
PLabel(x).Caption = "Max:"
HLabel(x).Caption = "Max:"
Next
Case "clear"
For x = 1 To Number
arrPercent(x).Caption = ""
Next
End Select

End Sub

It is so much time and energy, and all I really want to do is find out how
many topics there are in a given subject on the current form, and set each
control's attributes accordingly. I know that I am not doing this the most
efficient way, but I'm stuck on an alternate method. Can anyone suggest an
alternate way of dealing with this problem?

Many thanks in advance.

Joe
Click to expand...
Click to expand...
 
Hi Joe,

I can't be specific without understanding your data. This
http://support.microsoft.com/default.aspx?scid=kb;en-us;100139
is an article on "database normalization basics", and this
http://office.microsoft.com/en-us/assistance/HP051891361033.aspx
takes you to the first of a series of articles about database design.

John, do you think you could be a little more specific? I don't know how to
"properly normalise" a data structure.

John Nurick said:
Hi Joe,

I suspect the answer is to use a subform in continuous view.

But the way you talk about having "constructed a table structure" on "my
forms" to "hold various pieces of information related to the same
fields" makes me feel that you haven't yet got a properly normalised
data structure.

On several of my forms I have constructed a table structure to hold various
peices of information related to the same fields. For example, I may list a
varying number of topics on a form (as many as 12), and next to each one a
label that contains information which changes based on user input.

So often when I have tried to fill information in my pseudo-table, I have
resorted to creating an array that contains each label in the column. In
this way, I am able to cycle through each topic name and enter the data
accordingly. However, as my program has grown more complex, these arrays
have become more and more cumbersome.

Here is an example of one of the more basic uses of the arrays that I
construct:

Sub Enable_Dis(Number As Integer, Action As String)
On Error GoTo Err_Enable_Dis

Dim arrMed As Variant
arrMed = Array(Forms![Breakdown]!MDis1, Forms![Breakdown]!MDis2,
Forms![Breakdown]!MDis3, Forms![Breakdown]!MDis4, Forms![Breakdown]!MDis5,
Forms![Breakdown]!MDis6, Forms![Breakdown]!MDis7, Forms![Breakdown]!MDis8,
Forms![Breakdown]!MDis9, Forms![Breakdown]!MDis10, Forms![Breakdown]!MDis11,
Forms![Breakdown]!MDis12)

Dim arrPath As Variant
arrPath = Array(Forms![Breakdown]!PDis1, Forms![Breakdown]!PDis2,
Forms![Breakdown]!PDis3, Forms![Breakdown]!PDis4, Forms![Breakdown]!PDis5,
Forms![Breakdown]!PDis6, Forms![Breakdown]!PDis7, Forms![Breakdown]!PDis8,
Forms![Breakdown]!PDis9, Forms![Breakdown]!PDis10, Forms![Breakdown]!PDis11,
Forms![Breakdown]!PDis12)

Dim arrPharm As Variant
arrPharm = Array(Forms![Breakdown]!HDis1, Forms![Breakdown]!HDis2,
Forms![Breakdown]!HDis3, Forms![Breakdown]!HDis4, Forms![Breakdown]!HDis5,
Forms![Breakdown]!HDis6, Forms![Breakdown]!HDis7, Forms![Breakdown]!HDis8,
Forms![Breakdown]!HDis9, Forms![Breakdown]!HDis10, Forms![Breakdown]!HDis11,
Forms![Breakdown]!HDis12)

Dim MLabel As Variant
MLabel = Array(Forms!Breakdown!lblM1, Forms!Breakdown!lblM2,
Forms!Breakdown!lblM3, Forms!Breakdown!lblM4, Forms!Breakdown!lblM5,
Forms!Breakdown!lblM6, Forms!Breakdown!lblM7, Forms!Breakdown!lblM8,
Forms!Breakdown!lblM9, Forms!Breakdown!lblM10, Forms!Breakdown!lblM11,
Forms!Breakdown!lblM12)

Dim PLabel As Variant
PLabel = Array(Forms!Breakdown!lblP1, Forms!Breakdown!lblP2,
Forms!Breakdown!lblP3, Forms!Breakdown!lblP4, Forms!Breakdown!lblP5,
Forms!Breakdown!lblP6, Forms!Breakdown!lblP7, Forms!Breakdown!lblP8,
Forms!Breakdown!lblP9, Forms!Breakdown!lblP10, Forms!Breakdown!lblP11,
Forms!Breakdown!lblP12)

Dim HLabel As Variant
HLabel = Array(Forms!Breakdown!lblH1, Forms!Breakdown!lblH2,
Forms!Breakdown!lblH3, Forms!Breakdown!lblH4, Forms!Breakdown!lblH5,
Forms!Breakdown!lblH6, Forms!Breakdown!lblH7, Forms!Breakdown!lblH8,
Forms!Breakdown!lblH9, Forms!Breakdown!lblH10, Forms!Breakdown!lblH11,
Forms!Breakdown!lblH12)

Dim arrPercent As Variant
arrPercent = Array(Forms![Breakdown]!Percent1, Forms![Breakdown]!Percent2,
Forms![Breakdown]!Percent3, Forms![Breakdown]!Percent4,
Forms![Breakdown]!Percent5, Forms![Breakdown]!Percent6,
Forms![Breakdown]!Percent7, Forms![Breakdown]!Percent8,
Forms![Breakdown]!Percent9, Forms![Breakdown]!Percent10,
Forms![Breakdown]!Percent11, Forms![Breakdown]!Percent12)

Dim x As Integer

Forms!Breakdown!MDis1 = ""

Select Case Action
Case "enable"
For x = 1 To Number
arrMed(x).Enabled = True
arrPath(x).Enabled = True
arrPharm(x).Enabled = True
Next
Case "disable"
For x = 1 To Number
arrMed(x).Enabled = False
arrPath(x).Enabled = False
arrPharm(x).Enabled = False
MLabel(x).Caption = "Max:"
PLabel(x).Caption = "Max:"
HLabel(x).Caption = "Max:"
Next
Case "clear"
For x = 1 To Number
arrPercent(x).Caption = ""
Next
End Select

End Sub

It is so much time and energy, and all I really want to do is find out how
many topics there are in a given subject on the current form, and set each
control's attributes accordingly. I know that I am not doing this the most
efficient way, but I'm stuck on an alternate method. Can anyone suggest an
alternate way of dealing with this problem?

Many thanks in advance.

Joe
Click to expand...
Click to expand...
Click to expand...
 
Back
Top