473,225 Members | 1,220 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

Split MDB created problem with FindAsYouType

135 100+
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
4 2508
MMcCarthy
14,534 Expert Mod 8TB
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
Gilberto
135 100+
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
Gilberto
135 100+
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
32,554 Expert Mod 16PB
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

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

Similar topics

13
by: Larry L | last post by:
Access is noted for bloating a database when you add and delete records frequently. I have always had mine set to compact on close, and that works great. Now after everyone's advice I split my...
1
by: Don Hames | last post by:
I have a windows application that has a split container in the client area. In the left panel, I added controls via the designer in VS 2005. In the right panel, I want to dynamically create and...
3
by: Krish | last post by:
I have requirement, that i get one big chunk of text file. This text file will have has information, that on finding "****End of Information****", i have to split them individual text file with our...
2
by: Digital Fart | last post by:
following code would split a string "a != b" into 2 strings "a" and "b". but is there a way to know what seperator was used? string charSeparators = { "=", ">=", "<=" , "!=" }; string s1 =...
2
by: Jeff | last post by:
Hello all! I created a successful program that reads data from a reliable tab-delimited file - or so I thought. After getting everything to work with small files, I changed the input to a larger...
10
by: pantagruel | last post by:
Hi, I'm looking for an optimal javascript function to split a camelcase string and return an array. I suppose one could loop through the string, check if character is uppercase and start...
12
by: Jay | last post by:
Let's say, for instance, that one was programming a spell checker or some other function where the contents of a string from a text-editor's text box needed to be split so that the resulting array...
6
by: ivan.perak | last post by:
Hello, im a beginner in VB.NET... The thing i would like to do is as it follows.... I have a text file (list of names, every name to the next line) which is about 350000 lines long. I would...
7
by: AMP | last post by:
Hello, I am trying to split a string at the newline and this doesnt work: String Channel = FileName.Split("\r"); What am I doing wrong? Thanks Mike
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.