How do I add a specific control to the avarExceptionList?

[BrucePerry]

'=======Where is the exception list?======
'and how do I add a specific control to the list i.e. TextBox1

'Ignore if the control name is in the exception list.
For lngI = LBound(avarExceptionList) To UBound(avarExceptionList)


If avarExceptionList(lngI) = strControl Then
bSkip = True
Exit For
End If
Next

[ranman256]

your strControl must have a value, but I don't see where it came from
Is avarExceptionList an array? Arrays are obsolete, use a Collection.
you don't have to go thru the entire collection to find what you want . Its direct access.

dim colException as new collection
colException.add "TextBox1","TextBox1"
colException.add "combo1","combo1"


then to see if its on the list
if colEXception("TextBox1") = "TextBox1" then

no running from 1st to last inspecting every item to see if its in there.
add by : collection.ADD item, index

to see the entire list
for each itm in colException
debug.print itm
next

[BrucePerry]

I am using a FindAsUType program the avarExceptionList is an array and it goes to the form and gets a list of names
to complete the list for a list box or Value List, only thing is that I have a text box that holds a multitude of values.

I have a Database with a source document number in the same row is a number of related document D1, D2, D3, .. D60.
I list those documents in the text box only I want to exclude that text box because it should not be one of the list slections,
the total value is too many.

[orange]

Bruce,

With respect you are talking jargon. We don't know your set up or your FindAsUType program.

Tell us in plain English what you are trying to do. Do not assume we know your skills, your environment, your software nor your intentions.

Arrays are not obsolete. Collection, dictionaries, stacks etc are all possible. But they are possible answers for HOW to achieve what you want. First we need to know WHAT is involved in business terms.

[BrucePerry]

Simply put: here is the section of code:
My skill level is self taught I can read most of it but get stuck on some of the rest.
Personally I wish there were some classes devoted to data bases that would give you a good start,
but all I find is the integrated one into other courses that are not useful.
Any way the code should look familiar it's a copy from the WEB as is most code.

Worked fine for the normal controls.

It broke when I added the text box from the other form.

My control source for the text box "MS-Other" is:
=[MS SOURCE REFERENCE] & ("-"+[MS-1]) & ("-"+[MS-2]) & ("-"+[MS-3]) & ("-"+[MS-4]) & ("-"+[MS-5]) & ("-"+[MS-6]) & ("-"+[MS-7]) & ("-"+[MS-8]) & ("-"+[MS-9]) & ("-"+[MS-10]) & ("-"+[MS-11]) & ("-"+[MS-12]) & ("-"+[MS-13]) & ("-"+[MS-14]) & ("-"+[MS-15]) & ("-"+[MS-16]) & ("-"+[MS-17]) & ("-"+[MS-18]) & ("-"+[MS-19]) & ("-"+[MS-20]) & ("-"+[MS-21]) & ("-"+[MS-22]) & ("-"+[MS-23]) & ("-"+[MS-24]) & ("-"+[MS-25]) & ("-"+[MS-26]) & ("-"+[MS-27]) & ("-"+[MS-28]) & ("-"+[MS-29]) & ("-"+[MS-30]) & ("-"+[MS-31]) & ("-"+[MS-32]) & ("-"+[MS-33]) & ("-"+[MS-34]) & ("-"+[MS-35]) & ("-"+[MS-36]) & ("-"+[MS-37]) & ("-"+[MS-38]) & ("-"+[MS-39]) & ("-"+[MS-40]) & ("-"+[MS-41]) & ("-"+[MS-42]) & ("-"+[MS-43]) & ("-"+[MS-44]) & ("-"+[MS-45]) & ("-"+[MS-46]) & ("-"+[MS-47]) & ("-"+[MS-48]) & ("-"+[MS-49]) & ("-"+[MS-50]) & ("-"+[MS-51]) & ("-"+[MS-52]) & ("-"+[MS-53]) & ("-"+[MS-54]) & ("-"+[MS-55]) & ("-"+[MS-56]) & ("-"+[MS-57]) & ("-"+[MS-58]) & ("-"+[MS-59]) & ("-"+[MS-60])

So you can see how the contents might be rather large for a value list.

I want to exclude this "MS-Other" text box from the list.

P.S. Thanks for the help
Public Function FindAsUTypeLoad(frm As Form, ParamArray avarExceptionList()) As Boolean
On Error GoTo Err_Handler
'Purpose: Initialize the code for Find.
'Return: True on success.
'Arguments: - frm = a reference to the form where you want this filtering.
' - Optionally, you can specify controls NOT to offer filtering on, by putting the control names in quotes.
'Note: The form must contain the 2 controls, cboFindAsUTypeField and txtFindAsUTypeValue,
' with the combo set up correctly.
'Usage: Set the Load event procedure of the form to:
' Call FindAsUType(Me)
' To suppress filtering on controls FirstName and City, use:
' Call FindAsUType(Me, "FirstName", "City")
Dim rs As DAO.Recordset 'Clone set of the form.
Dim ctl As Control 'Each control on the form.
Dim strForm As String 'Name of form (for error handler.)
Dim strControl As String 'Name of the control.
Dim strField As String 'Name of the filter to use in the filter string.
Dim strControlSource As String 'Name of the field the control is bound to.
Dim strOut As String 'List for the RowSource of cboFindAsUTypeField.
Dim lngI As Long 'Loop counter.
Dim lngJ As Long 'Page counter loop controller.
Dim bSkip As Boolean 'Flag to provide no filtering for this control.
Dim bResult As Boolean 'Return value for this function.
Dim lngParentNumber As Long '-1 if the control is directly on the form, else PageIndex of it parent.
Dim lngMaxParentNumber As Long 'PageIndex of last page of tab control. -1 if no tab control.
Dim astrControls() As String 'Array to handle the controls on the form.
Const lngcControl = 0& 'First element of array astrControls is the control name.
Const lngcField = 1& 'Second element of the array is the field name to filter on.

'The form must have a control source if we are to filter it, and needs our 3 controls.
strForm = frm.Name

If HasUnboundControls(frm, "cboFindAsUTypeField", "txtFindAsUTypeValue") And (frm.RecordSource <> vbNullString) Then
'Set the event handers for the 2 contorls
frm!cboFindAsUTypeField.AfterUpdate = "=FindAsUTypeChange([Form])"
frm.txtFindAsUTypeValue.OnChange = "=FindAsUTypeChange([Form])"
'Calculate the number of pages on the tab control if there is one.
lngMaxParentNumber = MaxParentNumber(frm)

'Declare an array large enough to handle the controls on the form,
' for each page of any tab control (since these have their own tab index),
' and for storing the control name and the filter field name.
ReDim astrControls(0& To frm.Controls.Count - 1&, mlngcOnTheForm To lngMaxParentNumber, lngcControl To lngcField) As String
Set rs = frm.RecordsetClone 'For info about the fields the controls are bound to.

'Loop through the controls on the form.
For Each ctl In frm.Controls
'Ignore hidden controls, and limit ourselves to text boxes and combos.
If ctl.Visible Then
If (ctl.ControlType = acTextBox) Or (ctl.ControlType = acComboBox) Then
bSkip = False
strField = vbNullString
strControl = ctl.Name
'Ignore if the control name is in the exception list.
For lngI = LBound(avarExceptionList) To UBound(avarExceptionList)
If avarExceptionList(lngI) = strControl Then
bSkip = True
Exit For
End If
Next

If Not bSkip Then
'Ignore if unbound, or bound to an expression.
strControlSource = ctl.ControlSource
If (strControlSource = vbNullString) Or (strControlSource Like "=*") Then
bSkip = True
Else
'Ignore yes/no fields, binary (JET uses for unknown), and complex data types (> 100.)
Select Case rs(strControlSource).Type
Case dbBoolean, dbLongBinary, dbBinary, dbGUID, Is > 100
bSkip = True
End Select
End If
End If

'Ignore if we cannot specify the field to filter on.
If Not bSkip Then
strField = GetFilterField(ctl)
If strField = vbNullString Then
bSkip = True
End If
End If

'Add this control name to our array, in the order of the tab index.
If Not bSkip Then
lngParentNumber = ParentNumber(ctl)
astrControls(ctl.TabIndex, lngParentNumber, lngcControl) = strControl
astrControls(ctl.TabIndex, lngParentNumber, lngcField) = strField
End If
End If
End If
Next

'Loop through the array of controls, to build the string for the RowSource of cboFindAsUTypeField (5 columns.)
For lngJ = LBound(astrControls, 2) To UBound(astrControls, 2)
For lngI = LBound(astrControls) To UBound(astrControls)
If astrControls(lngI, lngJ, lngcControl) <> vbNullString Then
Set ctl = frm.Controls(astrControls(lngI, lngJ, lngcControl))
strOut = strOut & """" & ctl.Name & """" & mstrcSep & _
"""" & Caption4Control(frm, ctl) & """" & mstrcSep & _
ctl.ControlType & mstrcSep & _
"""" & astrControls(lngI, lngJ, lngcField) & """" & mstrcSep & _
"""" & rs(ctl.ControlSource).Type & """" & mstrcSep
End If
Next
Next
rs.Close

'Remove the trailing separator, and assign to the RowSource of cboFindAsUTypeField.
lngI = Len(strOut) - Len(mstrcSep)
If lngI > 0 Then
With frm.cboFindAsUTypeField
.RowSource = Left(strOut, lngI)
.Value = .ItemData(0) 'Initialize to the first item in the list.
End With
bResult = True 'Return True: the list loaded successfully.
End If
End If

'Show the filter controls. (Separate routine, since they could fail if the control does not exist.)
Call ShowHideControl(frm, "cboFindAsUTypeField", bResult)
Call ShowHideControl(frm, "txtFindAsUTypeValue", bResult)

'Return value
FindAsUTypeLoad = bResult
Exit_Handler:
Set ctl = Nothing
Set rs = Nothing
Exit Function
Err_Handler:
Call LogError(Err.Number, Err.DESCRIPTION, conMod & ".FindAsUTypeLoad", "Form " & strForm)
Resume Exit_Handler
End Function

[orange]

I found the code at Allen Browne's site. His stuff is recognized as the best or first class. His things are documented and explained, and most important, they have been tested.

Can you post a jpg ( a screen capture) of the form involved? You may have to create a zip file and post it.
I don't know what the context is for all of this stuff.

=[MS SOURCE REFERENCE] & ("-"+[MS-1]) & ("-"+[MS-2]) & ("-"+[MS-3]) & ("-"+[MS-4]) & ("-"+[MS-5]) & ("-"+[MS-6]) & ("-"+[MS-7]) & ("-"+[MS-8]) & ("-"+[MS-9]) & ("-"+[MS-10]) & ("-"+[MS-11]) & ("-"+[MS-12]) & ("-"+[MS-13]) & ("-"+[MS-14]) & ("-"+[MS-15]) & ("-"+[MS-16]) & ("-"+[MS-17]) & ("-"+[MS-18]) & ("-"+[MS-19]) & ("-"+[MS-20]) & ("-"+[MS-21]) & ("-"+[MS-22]) & ("-"+[MS-23]) & ("-"+[MS-24]) & ("-"+[MS-25]) .....

My skill level is self taught I can read most of it but get stuck on some of the rest.
Personally I wish there were some classes devoted to data bases that would give you a good start,
but all I find is the integrated one into other courses that are not useful.

I just posted some links to another poster re Database, Programming and design.


These may help with the learning.

Good luck.

[BrucePerry]

Sorry the network doesn't allow the image to this post. I used a sample DB and copied the code and form to my own and just changed the text boxes.

Worked fine until I added the text box with the combined fields, (MS-1....MS60).