Results 1 to 13 of 13
  1. #1
    bbelly is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Location
    Edmonton, Ab, Can
    Posts
    19

    Open a 'search as you type' form as 'edit-able'

    Greetings
    Win 10 / Access 2016 / 64 bit
    I have created a 'searching' form based on the example from
    http://www.opengatesw.net/ms-access-...pe-Access.html
    This creates a form which is a static copy of the information in my table, which is basically my creation based on their template.
    At the end of the explanation of the 'Search As You Type' it states ...

    "You can also apply this same technique to a form instead of just a listbox with some minor tweaks.
    All you would need to change is the function fLiveSearch to change references from RowSource to RecordSource,
    and ListCount with RecordSetClone.RecordCount.

    I'm not sure, but I get the impression this change could make a form in which I could edit the table contents directly,
    but I'm not understanding the tweaks it wants me to do. (A bonus would be that it lets my 'Plot' (PL) field show more than
    the current ~250 characters - but I have to eat this elephant slowly)

    As I created it, the search creates a list box that holds all the fields, from which I then copy over the selected record to text boxes in a form. But they are 'static'.

    Here's the code I have ...
    ================================================== =========================================
    Option Compare Database
    Option Explicit
    '************* Code Start **************
    ' This code was originally written by OpenGate Software
    ' It is not to be altered or distributed,
    ' except as part of an application.
    ' You are free to use it in any application,
    ' provided the copyright notice is left unchanged.
    ' OpenGate Software http://www.opengatesw.net

    Function fLiveSearch(ctlSearchBox As TextBox, ctlFilter As Control, _
    strFullSQL As String, strFilteredSQL As String, Optional ctlCountLabel As Control)
    '================================================= =================================
    ' THIS FUNCTION ALLOWS YOU TO FILTER A COMBO BOX OR LIST BOX AS THE USER TYPES
    ' ALL YOU NEED TO DO IS PASS IN THE CONTROL REFERENCE TO THE SEARCH BOX ON YOUR
    ' FORM, THE LISTBOX/COMBO BOX YOU WANT TO FILTER, AND WHAT THE FULL AND FILTERED
    ' SQL (ROWSOURCE) SHOULD BE.
    '
    ' ctlSearchBox THE TEXTBOX THE USER TYPES IN TO SEARCH
    '
    ' ctlFilter THE LISTBOX OR COMBOBOX ON THE FORM YOU WANT TO FILTER
    '
    ' strFullSQL THE FULL ROWSOURCE YOU WANT TO DISPLAY AS A DEFAULT IF NO
    ' RESULTS ARE RETURNED
    '
    ' strFilteredSQL THE FILTERED ROWSOURCE FOR THE LISTBOX/COMBOBOX; FOR EXAMPLE
    ' YOU WOULD WANT TO USE '...like ""*" & me.txtsearch.value & "*"""
    ' TO FILTER THE RESULTS BASED ON THE USER'S SEARCH INPUT
    '
    ' ctlCountLabel (OPTIONAL) THE LABEL ON YOUR FORM WHERE YOU WANT TO DISPLAY THE
    ' COUNT OF ROWS DISPLAYED IN THE LISTBOX/COMBOBOX AS THEY SEARCH
    '================================================= ====================================

    'ADVANCED PARAMETERS - Change these constants to change the behaviour of the search
    Const iSensitivity = 1 'Set to the number of characters the user must enter before the search starts
    Const blnEmptyOnNoMatch = True 'Set to true if you want nothing to appear if nothing matches their search


    10 On Error GoTo err_handle

    'restore the cursor to where they left off
    20 ctlSearchBox.SetFocus
    30 ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1

    40 If ctlSearchBox.Value <> "" Then
    'Only fire if they've input more than two characters (otherwise it's wasteful)
    50 If Len(ctlSearchBox.Value) > iSensitivity Then
    60 ctlFilter.RowSource = strFilteredSQL
    70 If ctlFilter.ListCount > 0 Then
    80 ctlSearchBox.SetFocus
    90 ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
    100 Else
    110 If blnEmptyOnNoMatch = True Then
    120 ctlFilter.RowSource = ""
    130 Else
    140 ctlFilter.RowSource = strFullSQL
    150 End If
    160 End If
    170 Else
    180 ctlFilter.RowSource = strFullSQL
    190 End If

    200 Else
    210 ctlFilter.RowSource = strFullSQL
    220 End If

    'if there is a count label, then update it
    230 If IsMissing(ctlCountLabel) = False Then
    240 ctlCountLabel.Caption = "Displaying " & Format(ctlFilter.ListCount - 1, "#,##0") & " records"
    250 End If

    260 Exit Function
    err_handle:
    270 Select Case Err.Number
    Case 91 'no ctlCountLabel
    'exit
    280 Case 94 'null string
    'exit
    290 Case Else
    300 MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description & _
    vbCrLf & "Error " & Err.Number & vbCrLf & "Line: " & Erl
    310 End Select


    End Function
    ' ***** Code End ******
    __________________________________________________ __________________________________________________ ___
    Option Compare Database
    Option Explicit
    ' THIS FORM PROVIDES AN EXAMPLE OF THE SEARCH AS YOU TYPE CAPABILITY.
    ' YOU NEED TO EMULATE THE txtSearch_Change, txtSearch_KeyPress,
    ' txtSearch_GotFocus, txtSearch_LostFocus events
    ' IN ORDER TO MAKE IT WORK; THE LINES WITH ***ADAPT THIS LINE TO YOUR OWN FORM***
    ' INDICATE SOMETHING YOU WILL
    ' NEED TO CHANGE TO MATCH HOW YOUR FORM IS DEPLOYED

    Private blnSpace As Boolean 'INCLUDE THIS LINE ON YOUR FORM

    Private Sub btnClearFilter_Click()
    'CODE FOR THE RED "X" BUTTON TO CLEAR THE FILTER AND SHOW ALL
    On Error Resume Next
    10 Me.txtSearch.Value = ""
    20 txtSearch_Change
    End Sub

    Private Sub txtSearch_Change()
    'CODE THAT HANDLES WHAT HAPPENS WHEN THE USER TYPES IN THE SEARCH BOX
    Dim strFullList As String
    Dim strFilteredList As String


    10 If blnSpace = False Then
    20 Me.Refresh 'refresh to make sure the text box changes are actually available to use

    'specify the default/full rowsource for the control
    30 strFullList = "SELECT ID, TL, YR, WD, HT, SZ, DR, PS, BW, CL, SL, ES, ED, LG, ST, NT, LN, HR, MN, RK, RT, PL, AC, AN, AD, BI, CM, CR, DO, DM, FM, FA, FN, HS, HO, MY, MU, RM, SF, SP, TH, WR, WS, UR, DW, FL, IO, C1, C2, C3, C4, C5, C6, C7, C8 FROM tbNm ORDER BY tbNm.TL;"

    'specify the way you want the rowsource to be filtered based on the user's entry
    40 strFilteredList = "SELECT ID, TL, YR, WD, HT, SZ, DR, PS, BW, CL, SL, ES, ED, LG, ST, NT, LN, HR, MN, RK, RT, PL, AC, AN, AD, BI, CM, CR, DO, DM, FM, FA, FN, HS, HO, MY, MU, RM, SF, SP, TH, WR, WS, UR, DW, FL, IO, C1, C2, C3, C4, C5, C6, C7, C8 FROM tbNm WHERE [TL] LIKE ""*" & Me.txtSearch.Value & _
    "*"" OR [YR] LIKE ""*" & Me.txtSearch.Value & "*"" ORDER BY [TL]"

    'run the search
    50 fLiveSearch Me.txtSearch, Me.lstItems, strFullList, strFilteredList, Me.txtCount
    60 End If

    End Sub

    Private Sub txtSearch_KeyPress(KeyAscii As Integer)
    'NECESSARY TO IDENTIFY IF THE USER IS HITTING THE SPACEBAR
    'IN WHICH CASE WE WANT TO IGNORE THE INPUT

    10 On Error GoTo err_handle

    20 If KeyAscii = 32 Then
    30 blnSpace = True


    40 Else
    50 blnSpace = False
    60 End If


    70 Exit Sub
    err_handle:
    80 Select Case Err.Number
    Case Else
    90 MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description & _
    vbCrLf & "Error " & Err.Number & "(" & Erl & ")"
    100 End Select
    End Sub
    Private Sub txtSearch_GotFocus()
    ' USED TO REMOVE THE PROMPT IF THE CONTROL GETS FOCUS
    10 On Error Resume Next
    20 If Me.txtSearch.Value = "(type to search)" Then
    30 Me.txtSearch.Value = ""
    40 End If
    End Sub
    Private Sub txtSearch_LostFocus()
    ' USED TO ADD THE PROMPT BACK IN IF THE CONTROL LOSES FOCUS
    10 On Error Resume Next
    20 If Me.txtSearch.Value = "" Then
    30 Me.txtSearch.Value = "(type to search)"
    40 End If

    End Sub
    ================================================== ===================================
    As always, thanks for the help

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Please post lengthy code within CODE tags to retain indentation and readability.

    According to the instructions, change:

    ctlFilter.RowSource to ctlFilter.RecordSource

    and

    ctlFilter.ListCount to ctlFlter.RecordSetClone.RecordCount

    Try it and let us know what happens. I anticipate issues because ctlFilter is declared as a control, not a form.


    Here is tutorial for a find-as-you-type form filter http://allenbrowne.com/AppFindAsUType.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bbelly is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Location
    Edmonton, Ab, Can
    Posts
    19
    First Question ... what are "Code Tags" ?

    That aside ... I changed the instances of ctlFilter.RowSource to ctlFilter.RecordSource (5 instances)

    and ListCount with RecordSetClone.RecordCount (2 instances).

    I also created a couple blank text boxes with TL & YR (title & year) links to the table ... but I suspect that has no use at all in the scheme of it

    The only result is that the search functionality that was there is gone, with "An Unexpected Error has occurred: Object doesn't support this property or method"

    The 'FindAsUType' worked to a point, but doesn't provide the "drill down" function that I currently have.

    I have included my database stripped of about 15k records. Perhaps one of you can make sense of it" Also in it is "The Great Dictator" , which has a plot field of many many characters, which the 'Plot' text box chops at ~250.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Click the hashtag icon to insert CODE tags in your post. Paste your VBA between the tags.

    There is no attachment to your post.

    Did you consider the code by Allen Browne?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Code:
    Optionally you can highlight the text of your post and click the # button

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    bbelly is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Location
    Edmonton, Ab, Can
    Posts
    19
    I think I uploaded it to ... somewhere? (Even this doesn't seem intuitive as compared to email attachments)

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You might have uploaded to your "container" which will grow in contents as you upload more stuff. You probably didn't click the Insert Inline button at the bottom right of the uploading dialog window. That will insert the attachment at the current cursor position. If I'm right, the attachment icon should be appearing in your container.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    bbelly is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Location
    Edmonton, Ab, Can
    Posts
    19
    It seems I am limited to an upload of 500 kb - my stripped down database is 18.9 mb. I'll see if I can trim it down to that - but given that a brand new blank data base file is already 324 kb, it seems to be an unrealistic effort. But I'll give it a shot tomorrow.

  10. #10
    bbelly is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Location
    Edmonton, Ab, Can
    Posts
    19
    If anyone is interested, shoot me a message and I'll email the file to you.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A 2MB zip file is allowed to attach. Otherwise, upload to a fileshare site such as Box.com and post link.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    bbelly is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Location
    Edmonton, Ab, Can
    Posts
    19
    Here's a link to that file in "Box" ...

    https://app.box.com/s/ky8sxryrbj1tzhi5jzxptagyoakc28t7

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am amazed such a small db is 19MB, C&R did nothing to reduce, and the zip is still 5MB+. I deleted all but 200 of the 15,000+ records and file size is just over 1mb and could easily be zipped and attached to post. That's all we really needed.

    Your db is not a relational structure. It is just a big flat file like an Excel spreadsheet. Multiple similar name fields is a big indicator of that (CNTRY1, CNTRY2, CNTRY3 , etc) as well as multiple yes/no fields for essentially the same kind of data (in this case - awards). I expect you will experience a lot of frustration filtering and analyzing. I am not even going to try to tackle your search form code and can only wish you luck if you continue on this design path. Why try to adapt the combo/list box code? Allen Browne tutorial is an example of 'find-as-you-type' structure to filer form records.

    Movies and music type (collections) of database has been topic of numerous threads - might do a search here.

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-02-2017, 05:19 PM
  2. Access Design and Form - Search, Add, Edit
    By kwarden13 in forum Database Design
    Replies: 16
    Last Post: 02-29-2016, 08:39 AM
  3. Search form with a Edit Button
    By gebmiller1984 in forum Forms
    Replies: 5
    Last Post: 02-14-2015, 11:54 AM
  4. Search/Edit form creation
    By Hagridore in forum Forms
    Replies: 11
    Last Post: 01-21-2015, 11:17 PM
  5. Replies: 1
    Last Post: 05-31-2013, 08:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums