Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mocking Combo Box Synchronization (Access XP (using DAO lib))

    <P ID="edit" class=small>(Edited by Patricia W on 04-Sep-03 21:55. )</P>Just for curiosity and interest, and maybe this could help someone -- I had been trying to figure out how to cause the cursor to select a record _without_ using a combo box. The code below will synchronize between character strokes that a user enters, much like the combo box autoexpand (I had problems w/ that):

    Option Compare Database
    Dim Title_if_Any As Variant
    Dim rst As Recordset

    Sub RecordPositioner(strSearchName As String)
    Title_if_Any = DLookup("Title", "cbo_InvTitle_forFastSearch", "TITLE like " & Chr$(34) & strSearchName & "*" & Chr$(34))
    If IsNull(Title_if_Any) Then
    Else
    Set rst = Me.RecordsetClone
    rst.FindFirst "TITLE = " & Chr$(34) & CStr(Title_if_Any) & Chr$(34)
    If rst.NoMatch Then
    Else
    Me.Bookmark = rst.Bookmark
    End If
    rst.Close
    Set rst = Nothing
    End If ' if isnull Title_if_Any
    End Sub

    Private Sub txt_jump_Change()
    If IsNull(Me!txt_jump.Text) Then
    Else
    RecordPositioner (Me!txt_jump.Text)
    DoEvents 'voodoo
    Me!txt_jump.SelStart = Len(Me!txt_jump.Text) ' otherwise the cursor will position strangely
    End If
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mocking Combo Box Synchronization (Access XP (using DAO lib))

    The combobox normally returns the value in the key column of the list that populates it. Then you look for a match to that in the form's recordset. That should be much faster than doing multiple DLookups and examining the entire recordset repeatedly because it limits the scope of the findfirst operation. In general, DLookup is a relatively slow method, and FindFirst can be even slower if the field being searched is not indexed. In both cases, text values in the field will slow down the search dramatically over numeric values.

    You weren't specific about what problems you had with autoexpand, so it might be useful to other loungers if you expanded on the problems you were trying to address with this solution.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mocking Combo Box Synchronization (Access XP (using DAO lib))

    Charlotte, thanks for your post (and thanks for one other response of a couple of months ago that I never got back to you on -- as before! <g>).

    Yes, I do know that numbers are faster searches than text, and that dlookup is also a slow-downer, and I considered all that before I shifted from the combo box -- but, this is just a very small database with only a couple of users (working so intensely on the small databases... hmmm). I also have frequently used the "cbo_whatever.column(0)" format to pick up the underlying key, but -- yes, I seemed to have had great difficulty with the autoexpand feature, ordinarily so nice, this time. However, I have to tell you that I have avoided bookmarks like the plague (bad Pat) so this was new territory and it's _very_ possible that I didn't know what the heck I was doing. I'll re-create the same everything using a combo box when I have some time this afternoon, I hope. Thanks, Charlotte! -- Pat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •