Split MDB created problem with FindAsYouType

[Gilberto]

Hello,

I have a couple of forms using the code to FIND AS YOU TYPE from Allen Browne (http://allenbrowne.com/AppFindAsUType.html). It worked PERFECTLY until yesterday when i splitted the db into FE/BE. The tables link ok and everything works ok EXCEPT this function. When i open the form it gives me a underlining line (iReturn = ctl.Parent.PageIndex IN THE PARENTNUMBER function in BOLD) and indicating that iReturn=0. I am new with access and i have no idea how to fix this. If i END the debugging the function DOES work, its just that it DOESNT display automtically the name of the first control.

Is there a way to fix this? why is this related with the splitting of the db?? In case there is no real solution how could i just SKIP or "DISREGARD/ACCEPT" the error so that the form open??

Thanks,
Gilberto

the appears in code:

 
 
 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
 
Private Function ShowHideControl(frm As Form, strControlName As String, bShow As Boolean) As Boolean
On Error Resume Next
    'Purpose:   Show or hide a control on the form, without error message.
    'Return:    True if the contorl's Visible property was set successfully.
    'Arguments: frm = a reference to the form where the control is expected.
    '           strControlName = the name of the control to show or hide.
    '           bShow = True to make visible; False to make invisible.
    'Note:      This is a separate routine, since hiding a non-existant control will error.
    frm.Controls(strControlName).Visible = bShow
    ShowHideControl = (Err.Number = 0&)
End Function
 
Private Function GetFilterField(ctl As Control) As String
On Error GoTo Err_Handler
    'Purpose:   Determine the field name to use when filtering on this control.
    'Return:    The field name the control is bound to, except for combos.
    '               In Access 2002 and later, we return the syntax Access uses for filtering these controls.
    'Argument:  The control we are trying to filter.
    'Note:      We don't use the Recordset of the combo, because:
    '               a) it's not supported earlier than Access 2002, and
    '               b) it's often not loaded at this point.
    '               Instead, we OpenRecordset to get the source field name,
    '               which works even if the field is aliased in the RowSource.
    '               Opening for append only is quicker, as it loads no existing records.
    Dim rs As DAO.Recordset     'To get information about the combo's RowSource.
    Dim iColumn As Integer      'The first visible column of the combo (zero-based.)
    Dim strField As String      'Return value: the field name to use for the filter string.
    Dim bCancel As Boolean      'Flag to not filter on this control.
 
    If ctl.ControlType = acComboBox Then
        iColumn = FirstVisibleColumn(ctl)
        If iColumn = ctl.BoundColumn - 1 Then
            'The bound column is the first visible column: filter on the control source field.
            strField = "[" & ctl.ControlSource & "]"
        Else
            'In Access 2002 and later, we can use the lookup syntax Access uses, if the source is a Table/Query.
            If Int(Val(SysCmd(acSysCmdAccessVer))) >= 10 Then
                If ctl.RowSourceType = "Table/Query" Then
                    Set rs = DBEngine(0)(0).OpenRecordset(ctl.RowSource, dbOpenDynaset, dbAppendOnly)
                    With rs.Fields(iColumn)
                        strField = "[Lookup_" & ctl.Name & "].[" & .SourceField & "]"
                    End With
                    rs.Close
                Else
                    bCancel = True  'Hidden bound column not supported if RowSourceType is Value List or call-back function.
                End If
            Else
                bCancel = True      'Hidden bound column not supported for versions earlier than Access 2002.
            End If
        End If
    Else
        'Not a combo: filter on the control source field.
        strField = "[" & ctl.ControlSource & "]"
    End If
 
    If strField <> vbNullString Then
        GetFilterField = strField
    ElseIf Not bCancel Then
        GetFilterField = "[" & ctl.ControlSource & "]"
    End If
 
Exit_Handler:
    Set rs = Nothing
    Exit Function
 
Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".GetFilterField")
    Resume Exit_Handler
End Function
 
Private Function FirstVisibleColumn(cbo As ComboBox) As Integer
On Error GoTo Err_Handler
    'Purpose:   Return the column number of the first visible column in a combo.
    'Return:    Column number. ZERO-BASED!
    'Argument:  The combo to examine.
    'Note:      Also returns zero on error.
    Dim i As Integer            'Loop controller.
    Dim varArray As Variant     'Array of the combo's ColumnWidths values.
    Dim iResult As Integer      'Colum number to return.
    Dim bFound As Boolean       'Flag that we found a value to return.
 
    If cbo.ColumnWidths = vbNullString Then
        'If no column widths are specified, the first column is visible.
        iResult = 0
        bFound = True
    Else
        'Parse the ColumnWidths string into an array, and find the first non-zero value.
        varArray = Split(cbo.ColumnWidths, mstrcSep)
        For i = LBound(varArray) To UBound(varArray)
            If varArray(i) <> 0 Then
                iResult = i
                bFound = True
                Exit For
            End If
        Next
        'If the column widths ran out before all columns were checked, the next column is the first visible one.
        If Not bFound Then
            If i < cbo.ColumnCount Then
                iResult = i
                bFound = True
            End If
        End If
    End If
 
    FirstVisibleColumn = iResult
 
Exit_Handler:
    Exit Function
 
Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".FirstVisibleColumn")
    Resume Exit_Handler
End Function
 
'------------------------------------------------------------------------------------------------
'You may prefer to replace this with a true error logger. See http://allenbrowne.com/ser-23a.html
Private Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As String, _
    strCallingProc As String, Optional vParameters, Optional bShowUser As Boolean = True) As Boolean
On Error GoTo Err_LogError
    'Purpose:   Generic error handler.
    'Arguments: lngErrNumber - value of Err.Number
    '           strErrDescription - value of Err.Description
    '           strCallingProc - name of sub|function that generated the error.
    '           vParameters - optional string: List of parameters to record.
    '           bShowUser - optional boolean: If False, suppresses display.
    'Author:    Allen Browne, [e-mail]
 
    Dim strMsg As String    'String for display in MsgBox
 
    Select Case lngErrNumber
    Case 0
        Debug.Print strCallingProc & " called error 0."
    Case 2501               'Cancelled
        'Do nothing.
    Case 3314, 2101, 2115   'Can't save.
        If bShowUser Then
            strMsg = "Record cannot be saved at this time." & vbCrLf & _
                "Complete the entry, or press <Esc> to undo."
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
    Case Else
        If bShowUser Then
            strMsg = "Error " & lngErrNumber & ": " & strErrDescription
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
        LogError = True
    End Select
 
Exit_LogError:
    Exit Function
 
Err_LogError:
    strMsg = "An unexpected situation arose in your program." & vbCrLf & _
        "Please write down the following details:" & vbCrLf & vbCrLf & _
        "Calling Proc: " & strCallingProc & vbCrLf & _
        "Error Number " & lngErrNumber & vbCrLf & strErrDescription & vbCrLf & vbCrLf & _
        "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
    MsgBox strMsg, vbCritical, "LogError()"
    Resume Exit_LogError
End Function
 

[MMcCarthy]

You will need to put some code breaks on the code in the form load or form open event to find out where and when this error is being triggered. Seeing the function code doesn't really help to figure out why the error is being triggered.

[Gilberto]

You will need to put some code breaks on the code in the form load or form open event to find out where and when this error is being triggered. Seeing the function code doesn't really help to figure out why the error is being triggered.

Thanks Mmccarthy. Could you specify a bit more how to do this???? Im kind of new with access

[Gilberto]

Thanks Mmccarthy. Could you specify a bit more how to do this???? Im kind of new with access

I just fixed the problem by DELETING "Call FindAsUTypeLoad(Me)" from the Form Load event, however this makes NO sense as: 1) it USED to work having that line, 2) it SHOULD NEED that line to call the function....am i wrong???

WHATS HAPPENING with my access???????? I opened this SAME db at another computer and it worked WITH the code line and there were NO errors 2465 at all.


Any ideas???

[NeoPa]

As Mary says Gilberto, you need to do some work to determine where your problem occurs. Just posting a great big procedure like that and asking someone to debug it remotely is quite unrealistic (as well as a little on the cheeky side).
As you say yourself, there are situations where it works and some where it doesn't. You don't explain what the differences are between the two scenarios, so how can you expect someone to tell you the answer if you don't supply a meaningful question? If the only information you post is an enormous procedure, don't be surprised if no-one can give you an answer.

This may or may not help, but there is a short article (Debugging in VBA) giving some clues as to how to use the debugging facilities in Access VBA.