Enumerating all controls in a form that are on a tab control

[Guest]

Hey,
i have a tab control that contains numerous test boxes that i would like to
enumerate over them, to build up an sql search string. i.e

Dim ctl As Control
Dim AddAnd As String
AddAnd = ""
strSQL = ""

' Enumerate Controls collection.
For Each ctl In Form.Controls

' Check to see if control is text box or combo box
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If Not IsNull(ctl.Value) Then
strSQL = strSQL & AddAnd & " " & ctl.Name & " = " & ctl.Value
AddAnd = " And"
End If
End If

Next ctl


this code works fine to build up the SQL string, but for one problem. i have
a tab control that shows data from many different tables, each with their own
text boxes for a search, and the code above enumerates over ALL of the text
boxes, not just the ones that are visible in the current page of the tab
control.

how can i enumerate over only the text boxes in the current page of the tab
control?

 

[Allen Browne]

Examine the Parent of the control. For a text box or combo, that will match
the PageIndex of the tab page the control is on.

This example accepts a control, and returns the page index, or -1 if the
control is sitting directly on the form:

Private Function ParentNumber(ctl As Control) As Integer
On Error Resume Next
'Purpose: Return the PageIndex of the tab page that the control is on.
'Return: -1 if setting directly on the form, else the page of the tab
control.
'Note: This works for text boxes and combos, not for labels or
controls in an option group.
Dim iReturn As Integer

iReturn = ctl.Parent.PageIndex
If Err.Number <> 0& Then
iReturn = mlngcOnTheForm
End If
ParentNumber = iReturn
End Function

Note that this is not the case for all controls, e.g. the Parent of a label
could be the control it is attached to; the parent of an option button could
be the option group.

The function above is from this page:
http://allenbrowne.com/AppFindAsUTypeCode.html
It illustrates how to loop through all the controls on the form, determining
information about each one: control type, what it is bound to, type of data,
name of source table, and the name by which the user knows the field.

 

[Guest]

looks like what i want, thanks a lot for that.
i will give it a try, and let u know if i can get it working.

once again, thanks a lot
ben

 

[Guest]

that code works fine, thankyou for that.

I do have another problem though. this is the code that i now have

Dim ctl As Control
Dim AddAnd As String
AddAnd = ""
strSQL = ""

' Enumerate Controls collection.
For Each ctl In Form.Controls

' Check to see if control is text box or combo box

If ParentNumber(ctl) = dataSelector.Value Then

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox
Then
If Not IsNull(ctl.Value) Then
strSQL = strSQL & AddAnd & " " & ctl.Name & " = " &
ctl.Value
AddAnd = " And"
End If
End If
End If
Next ctl

'The following code is written by Allen browne, taken from
http://allenbrowne.com/AppFindAsUTypeCode.html
'This was code given as part of an answer on the microsoft access forum
'http://msdn.microsoft.com/newsgroups/topic.aspx?url=/msdn-files/028/201/016/topic.xml

Private Function ParentNumber(ctl As Control) As Integer
On Error Resume Next
'Purpose: Return the PageIndex of the tab page that the control is on.
'Return: -1 if setting directly on the form, else the page of the tab
control.
'Note: This works for text boxes and combos, not for labels or
controls in an option group.
Dim iReturn As Integer

iReturn = ctl.Parent.PageIndex
If Err.Number <> 0& Then
iReturn = -1
End If
ParentNumber = iReturn
End Function

this code generates an sql string based on the names of the combo boxes and
text boxes on the current page of the tab control. The problem is, each page
of the tab control has a subform for a different table, but all of these
tables have an ID column, for example. To differentiate between these text
boxes on the form, they are called Data_A_IDBox, and Data_B_IDBox etc. The
Sql string needs to have "ID" in it, not "Data_A_IDBox", but i cannot rename
all of the text boxes, because this would create duplicates.

it is also infeasible to put the common boxes such as ID on top of the form,
as they could be in different positions in each tab control.

Does everyone understand my question? Can anyone help?

Thanks a lot for your help,
Ben

 

[Allen Browne]

Examine the SourceTable property of the Field of the RecordsetClone of the
form, using the name in the Control Source of the text box, provided it is
not unbound or an expression (i.e. starting with =.)

There should be an example of that in that web page code too.