Thanks for that fellas

Actually the problem may be a little more involved.
Heres what happens exactly.

The combo box has a list of stored procedures (from an SQL Server 2000
database). When I select a stored procedure, I would like to show each
parameters name and value in a textbox (1 for name and 1 for value).
Each parameters name and value textbox forms a row (kind of) and for the
next parameter(s), I increment the Top property of the name and value
textbox so that the new controls sit below the previous ones. (do you
get that - they kind of look like a grid in the end)

If the stored procedure has 10 parameters, I would generate 10 rows (x 2
textboxes) giving a total of 20 textboxes.

However, if I've made the wrong selection in the combo and re-chose a
new stored procedure, I need to remove these 20 textboxes that I've
created (at runtime) and recreate a set of new textboxes which will
depend on the number of parameters that the newly selected stored
procedure contains. (possibly 0).

This is why i've created the sub ClearTemporaryControls which can be
called immediately after selecting the stored procedure but before the
new controls are created.

One thing you did say Tom was that you always deleted the zeroth indexed
control. Is this a rule when deleting controls at runtime or just good
practice. Also, if you do delete a control, is the index count
decremented also and do all controls after the deleted control have
their indexes decremented likewise?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Even with this 'extra' complexity. does Tom's solution not work okay? His
point about deleting the zeroeth instance is not a best practice but just a
technique that can work well as when a control is deleted, all the others
will shunt down, so the first instance becomes the zeroeth, the second
becomes first, etc. This allows to loop through the controls with a For Each
construct. If you didn't use this technique, you would need to loop
backwards. The problem arises if there are other controls on the page,
because as soon as one of these becomes the zeroeth instance, it never gets
past it (it is always the zeroeth instance from then on).

Hiding/unhding would work just as well here as long as you know the maximum
number. Here is some code with examples of 3 and 5 boxes

Sub HideTextBoxes()
Dim i As Integer
Dim ctrl As Control
Dim objPage As Page
Const SEARCH_STRING = "txtSprocParam"

Set objPage = Me.MultiPageKPIs.Pages.Item(1)
For i = 0 To objPage.Controls.Count - 1
' change made here
Set ctrl = objPage.Controls.Item(i)
If Left(ctrl.Name, 13) = SEARCH_STRING Then
ctrl.Visible = False
End If
Next i

End Sub

Sub ShowTextBoxes(num As Long)
Dim i As Integer
Dim ctrl As Control
Dim objPage As Page
Const SEARCH_STRING = "txtSprocParam"

Set objPage = Me.MultiPageKPIs.Pages.Item(1)
For i = 0 To objPage.Controls.Count - 1
' change made here
Set ctrl = objPage.Controls.Item(i)
If Left(ctrl.Name, 13) = SEARCH_STRING Then
If CLng(Mid(ctrl.Name, 14, 99)) <= num Then
ctrl.Visible = True
End If
End If
Next i

End Sub


Private Sub CommandButton1_Click()
HideTextBoxes
ShowTextBoxes 3
End Sub

Private Sub CommandButton2_Click()
HideTextBoxes
ShowTextBoxes 5
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grant Williams" wrote in message
...


Thanks for that fellas

Actually the problem may be a little more involved.
Heres what happens exactly.

The combo box has a list of stored procedures (from an SQL Server 2000
database). When I select a stored procedure, I would like to show each
parameters name and value in a textbox (1 for name and 1 for value).
Each parameters name and value textbox forms a row (kind of) and for the
next parameter(s), I increment the Top property of the name and value
textbox so that the new controls sit below the previous ones. (do you
get that - they kind of look like a grid in the end)

If the stored procedure has 10 parameters, I would generate 10 rows (x 2
textboxes) giving a total of 20 textboxes.

However, if I've made the wrong selection in the combo and re-chose a
new stored procedure, I need to remove these 20 textboxes that I've
created (at runtime) and recreate a set of new textboxes which will
depend on the number of parameters that the newly selected stored
procedure contains. (possibly 0).

This is why i've created the sub ClearTemporaryControls which can be
called immediately after selecting the stored procedure but before the
new controls are created.

One thing you did say Tom was that you always deleted the zeroth indexed
control. Is this a rule when deleting controls at runtime or just good
practice. Also, if you do delete a control, is the index count
decremented also and do all controls after the deleted control have
their indexes decremented likewise?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Bob/Tom et al
thanks very much for that. Actually, in the end, I ended up hiding the
previously created controls. What I did was give the current array of
controls the name of the current Stored Procedure like:
Set objLabelParamName = objPage.Controls.Add("Forms.Label.1", "TMP_" &
strSprocName & "_NAME_" & i, Visible)

where strSprocName was defined as the value from combo. This was the
perfect solution as all other controls are removed when the form is
closed anyway as controls created at runtime are only temporary.

I appreciate the time and advice fellas. Hoping I can contribute
somehow in the future to the group.

Grant Williams


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Wise move IMO, it gives you more control and flexibility.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grant Williams" wrote in message
...

Bob/Tom et al
thanks very much for that. Actually, in the end, I ended up hiding the
previously created controls. What I did was give the current array of
controls the name of the current Stored Procedure like:
Set objLabelParamName = objPage.Controls.Add("Forms.Label.1", "TMP_" &
strSprocName & "_NAME_" & i, Visible)

where strSprocName was defined as the value from combo. This was the
perfect solution as all other controls are removed when the form is
closed anyway as controls created at runtime are only temporary.

I appreciate the time and advice fellas. Hoping I can contribute
somehow in the future to the group.

Grant Williams


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!