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:

Expand|Select|Wrap|Line Numbers

  
 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, allen@allenbrowne.com
 
    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

msquared

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

Quote:

Originally Posted by 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.

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

Gilberto

Quote:

Originally Posted by 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.

Split MDB created problem with FindAsYouType

re: Split MDB created problem with FindAsYouType

re: Split MDB created problem with FindAsYouType

re: Split MDB created problem with FindAsYouType

re: Split MDB created problem with FindAsYouType

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about