By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Split MDB created problem with FindAsYouType

100+
P: 135
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
  1.  
  2.  
  3. Private Function ParentNumber(ctl As Control) As Integer
  4. On Error Resume Next
  5.     'Purpose:   Return the PageIndex of the tab page that the control is on.
  6.     'Return:    -1 if setting directly on the form, else the page of the tab control.
  7.     'Note:      This works for text boxes and combos, not for labels or controls in an option group.
  8.     Dim iReturn As Integer
  9.  
  10.     iReturn = ctl.Parent.PageIndex
  11.     If Err.Number <> 0& Then
  12.         iReturn = mlngcOnTheForm
  13.     End If
  14.     ParentNumber = iReturn
  15. End Function
  16.  
  17. Private Function ShowHideControl(frm As Form, strControlName As String, bShow As Boolean) As Boolean
  18. On Error Resume Next
  19.     'Purpose:   Show or hide a control on the form, without error message.
  20.     'Return:    True if the contorl's Visible property was set successfully.
  21.     'Arguments: frm = a reference to the form where the control is expected.
  22.     '           strControlName = the name of the control to show or hide.
  23.     '           bShow = True to make visible; False to make invisible.
  24.     'Note:      This is a separate routine, since hiding a non-existant control will error.
  25.     frm.Controls(strControlName).Visible = bShow
  26.     ShowHideControl = (Err.Number = 0&)
  27. End Function
  28.  
  29. Private Function GetFilterField(ctl As Control) As String
  30. On Error GoTo Err_Handler
  31.     'Purpose:   Determine the field name to use when filtering on this control.
  32.     'Return:    The field name the control is bound to, except for combos.
  33.     '               In Access 2002 and later, we return the syntax Access uses for filtering these controls.
  34.     'Argument:  The control we are trying to filter.
  35.     'Note:      We don't use the Recordset of the combo, because:
  36.     '               a) it's not supported earlier than Access 2002, and
  37.     '               b) it's often not loaded at this point.
  38.     '               Instead, we OpenRecordset to get the source field name,
  39.     '               which works even if the field is aliased in the RowSource.
  40.     '               Opening for append only is quicker, as it loads no existing records.
  41.     Dim rs As DAO.Recordset     'To get information about the combo's RowSource.
  42.     Dim iColumn As Integer      'The first visible column of the combo (zero-based.)
  43.     Dim strField As String      'Return value: the field name to use for the filter string.
  44.     Dim bCancel As Boolean      'Flag to not filter on this control.
  45.  
  46.     If ctl.ControlType = acComboBox Then
  47.         iColumn = FirstVisibleColumn(ctl)
  48.         If iColumn = ctl.BoundColumn - 1 Then
  49.             'The bound column is the first visible column: filter on the control source field.
  50.             strField = "[" & ctl.ControlSource & "]"
  51.         Else
  52.             'In Access 2002 and later, we can use the lookup syntax Access uses, if the source is a Table/Query.
  53.             If Int(Val(SysCmd(acSysCmdAccessVer))) >= 10 Then
  54.                 If ctl.RowSourceType = "Table/Query" Then
  55.                     Set rs = DBEngine(0)(0).OpenRecordset(ctl.RowSource, dbOpenDynaset, dbAppendOnly)
  56.                     With rs.Fields(iColumn)
  57.                         strField = "[Lookup_" & ctl.Name & "].[" & .SourceField & "]"
  58.                     End With
  59.                     rs.Close
  60.                 Else
  61.                     bCancel = True  'Hidden bound column not supported if RowSourceType is Value List or call-back function.
  62.                 End If
  63.             Else
  64.                 bCancel = True      'Hidden bound column not supported for versions earlier than Access 2002.
  65.             End If
  66.         End If
  67.     Else
  68.         'Not a combo: filter on the control source field.
  69.         strField = "[" & ctl.ControlSource & "]"
  70.     End If
  71.  
  72.     If strField <> vbNullString Then
  73.         GetFilterField = strField
  74.     ElseIf Not bCancel Then
  75.         GetFilterField = "[" & ctl.ControlSource & "]"
  76.     End If
  77.  
  78. Exit_Handler:
  79.     Set rs = Nothing
  80.     Exit Function
  81.  
  82. Err_Handler:
  83.     Call LogError(Err.Number, Err.Description, conMod & ".GetFilterField")
  84.     Resume Exit_Handler
  85. End Function
  86.  
  87. Private Function FirstVisibleColumn(cbo As ComboBox) As Integer
  88. On Error GoTo Err_Handler
  89.     'Purpose:   Return the column number of the first visible column in a combo.
  90.     'Return:    Column number. ZERO-BASED!
  91.     'Argument:  The combo to examine.
  92.     'Note:      Also returns zero on error.
  93.     Dim i As Integer            'Loop controller.
  94.     Dim varArray As Variant     'Array of the combo's ColumnWidths values.
  95.     Dim iResult As Integer      'Colum number to return.
  96.     Dim bFound As Boolean       'Flag that we found a value to return.
  97.  
  98.     If cbo.ColumnWidths = vbNullString Then
  99.         'If no column widths are specified, the first column is visible.
  100.         iResult = 0
  101.         bFound = True
  102.     Else
  103.         'Parse the ColumnWidths string into an array, and find the first non-zero value.
  104.         varArray = Split(cbo.ColumnWidths, mstrcSep)
  105.         For i = LBound(varArray) To UBound(varArray)
  106.             If varArray(i) <> 0 Then
  107.                 iResult = i
  108.                 bFound = True
  109.                 Exit For
  110.             End If
  111.         Next
  112.         'If the column widths ran out before all columns were checked, the next column is the first visible one.
  113.         If Not bFound Then
  114.             If i < cbo.ColumnCount Then
  115.                 iResult = i
  116.                 bFound = True
  117.             End If
  118.         End If
  119.     End If
  120.  
  121.     FirstVisibleColumn = iResult
  122.  
  123. Exit_Handler:
  124.     Exit Function
  125.  
  126. Err_Handler:
  127.     Call LogError(Err.Number, Err.Description, conMod & ".FirstVisibleColumn")
  128.     Resume Exit_Handler
  129. End Function
  130.  
  131. '------------------------------------------------------------------------------------------------
  132. 'You may prefer to replace this with a true error logger. See http://allenbrowne.com/ser-23a.html
  133. Private Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As String, _
  134.     strCallingProc As String, Optional vParameters, Optional bShowUser As Boolean = True) As Boolean
  135. On Error GoTo Err_LogError
  136.     'Purpose:   Generic error handler.
  137.     'Arguments: lngErrNumber - value of Err.Number
  138.     '           strErrDescription - value of Err.Description
  139.     '           strCallingProc - name of sub|function that generated the error.
  140.     '           vParameters - optional string: List of parameters to record.
  141.     '           bShowUser - optional boolean: If False, suppresses display.
  142.     'Author:    Allen Browne, allen@allenbrowne.com
  143.  
  144.     Dim strMsg As String    'String for display in MsgBox
  145.  
  146.     Select Case lngErrNumber
  147.     Case 0
  148.         Debug.Print strCallingProc & " called error 0."
  149.     Case 2501               'Cancelled
  150.         'Do nothing.
  151.     Case 3314, 2101, 2115   'Can't save.
  152.         If bShowUser Then
  153.             strMsg = "Record cannot be saved at this time." & vbCrLf & _
  154.                 "Complete the entry, or press <Esc> to undo."
  155.             MsgBox strMsg, vbExclamation, strCallingProc
  156.         End If
  157.     Case Else
  158.         If bShowUser Then
  159.             strMsg = "Error " & lngErrNumber & ": " & strErrDescription
  160.             MsgBox strMsg, vbExclamation, strCallingProc
  161.         End If
  162.         LogError = True
  163.     End Select
  164.  
  165. Exit_LogError:
  166.     Exit Function
  167.  
  168. Err_LogError:
  169.     strMsg = "An unexpected situation arose in your program." & vbCrLf & _
  170.         "Please write down the following details:" & vbCrLf & vbCrLf & _
  171.         "Calling Proc: " & strCallingProc & vbCrLf & _
  172.         "Error Number " & lngErrNumber & vbCrLf & strErrDescription & vbCrLf & vbCrLf & _
  173.         "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
  174.     MsgBox strMsg, vbCritical, "LogError()"
  175.     Resume Exit_LogError
  176. End Function
  177.  
Nov 13 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 13 '07 #2

100+
P: 135
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
Nov 13 '07 #3

100+
P: 135
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???
Nov 13 '07 #4

NeoPa
Expert Mod 15k+
P: 31,271
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.
Nov 14 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.