Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    In this particular case where you are looking for a Customer's Name then I would have thought the ideal solution is the standard Combobox?

    Your Combobox is using a Combination of Firstname and Lastname which makes it very difficult to locate a specific Name.

    The standard method is to use the Combination of Lastname and Firstname.



    What are you hoping to display in your Combobox in this particular instance?

    Or am I being thick?

  2. #17
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Mike, there is a huge difference between being thick and just not understanding something that is new to you.

    Search-as-you-type has been a standard feature of just about every stand alone IT program that I've ever used with a number of different employers I've worked for or contracted to before I retired.
    The basic reason behind it is in sales when a past customer rings and says "Hi Jim, it's Bill Flubberbutton. How are you today?"
    I have about 2 seconds (literally) to find his record so I can quickly respond with something like, "Hey Bill. Great to hear from you. How did your daughters 21st birthday go?"
    How do I know this?
    Because in the time its taken him to say hello, I've found his record and quickly scanned the notes I made 6 months earlier when we last spoke.
    The end result is, he's firmly convinced he's a VIP to me because I remember details about his life. He's not just another customer.

    In my case, I had a instance of a lady calling me just a couple of weeks ago. Her name was similar to, (Not her real name) Diane Corevski. I typed in Dia into my combo box and another Diane auto completed. Drop the combo list down and I just couldn't find her. there were too many Dianes.

    Now, If I had had Search-As-You-Type working, after typing in 'Di" about a half a dozen Dianes would have popped up, and one of them would have been Diane and Charlie Korevski.
    Spelt with a K.
    I'd have had her record in front of me within that 2 second window and I could have quickly replied, "Hi Diane. How is Charlie getting on in his new job?"
    That incident pushed Search-As-You-Type to the top of my to-Do list and prompted my joining this forum.

    Now do you understand a bit better?

    Jim

  3. #18
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Jim

    Well this is recommended to be carried out using a Listbox rather than a Combobox.

    See the example in the attached.

    The Form that opens at Startup show a List of your current Customers.

    Type in the search box and the records will filter as required.

    Use the Reset Button to refresh the list
    Attached Files Attached Files

  4. #19
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Should review this tutorial http://allenbrowne.com/ser-32.html

    AutoExpand is located at bottom of Properties Sheet Data tab.
    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. #20
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Well, well well. One CAN learn something new every day, if you belong to the right forum.
    Silly me was looking in ALL thinking that had, you know...ALL the settings.

    Thank you.

    That has fixed that problem, and I now understand another setting much better AND.....I now have access to some code to find Post Codes for Australia.
    All in all, a very good start to my day.

    That one is now bookmarked for down the track.

    Only the error message left to go. Have you had any thoughts on that one?

    Jim

  6. #21
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    It's there, just harder to spot because it's a little ways past middle of a very long list.

    I expect Allen Browne's code should have a resolution. I've never used this so I hoped Allen's tutorial would clarify better than I could even try to explain.
    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.

  7. #22
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Oh dear. It sounds like this issue is causing you some grief in finding a solution. That's certainly not what I wanted.
    As a newbie I assumed it was just my inexperience that was causing the issue and someone who actually knew what they were doing would just point out my mistake and how to rectify it.
    Are you happy enough to continue trying to find a solution or would you prefer to just put it in the "too Hard" basket and move on.
    I'm good with that if you are.
    Jim

  8. #23
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Jim

    Is the Listbox option no good for you Jim?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why have two fields (FullName and Search) with same data?

    Okay, fixing your code, ran into a conundrum. You want to search anywhere in FullName which means AutoExpand must be turned off. If user clicks on list to select item Change event is triggered but combobox no longer has focus causing .Text property to error. And I can't get to work without .Text. Using down arrow to select item from dropdown list does not cause issue.

    Allen's code matches strings starting from first character.

    So finally got this to run without error:
    Code:
    Option Compare Database
    Option Explicit
    
    Function ReloadCombo(sSuburb As String)
    'search as you type
    If Len(sSuburb) = 0 Then
        Me.SearchCombo.RowSource = "SELECT ClientID, FullName FROM CustomerT WHERE False"
    Else
        Me.SearchCombo.RowSource = "SELECT ClientID, FullName FROM CustomerT WHERE FullName LIKE '*" & _
                    Replace(sSuburb, "'", "''") & "*' ORDER BY FullName;"
    End If
    End Function
    
    Private Sub Form_Current()
    Call ReloadCombo(Nz(Me.SearchCombo, ""))
    End Sub
    
    Private Sub SearchCombo_Change()
    If Me.SearchCombo Is Screen.ActiveControl Then
        Call ReloadCombo(Nz(Me.SearchCombo.Text, ""))
        Me.SearchCombo.Dropdown
    End If
    End Sub
    Note the first two lines at top of code. Every module should have them in header. I find it odd the first line is missing. It should be there by default when module is created. The second line is optional by default. Set to automatically include with new module: from the VBE > Tools > Options > check Require Variable Declaration.

    Also note use of Replace() function because some of your names have apostrophes. Apostrophes are considered special characters by SQL and will cause error unless the character is 'escaped' to force SQL to see it as a normal text character.

    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.

  10. #25
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Spot on June7,
    I can't thank you enough.

    Re FullName vs Search
    When I set the very first table up for this project, which was this one, CustomerT, I thought I would prefer first and last name separate as I could foresee at some stage wanting to address something to Dear John, instead of Dear John Doe,
    Later, I realised there was a benefit to having the full name in one field when doing reports etc. (This was before I learnt there was a way of doing this in the report itself.
    It was working however so I just left it.

    Much later when I started working on this ComboBox I wanted the first name, partners name and last name in one field so I could search all three. I often deal with just one of the couple and then get a call from their husband/wife. So, I made a second calculated field to combine all three.
    If I just substitute "Search" wherever it says FullName, is that all I have to do to change it?

    You've been so incredibly helpful on this that I'm loath to ask for one more thing.

    Once the type as you search has narrowed down the list to the one I want, or I've selected a record from the reduced list, its not selecting that record to display in the form as a ComboBox normally does. Is there much involved in making that happen?

    Jim

  11. #26
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Mike, there's nothing inherently wrong in the Text box and list box idea. In fact, I already have one working on another part of the project. It's just takes up too much real estate on this particular form.
    You're 100% right in pushing the idea however. Even with my very limited knowledge of VBA I was able to get one working with a list box. Albeit by copy and pasting code from a Google Search. Much, much simpler for a novice. And, I've identified myself as a novice.

    But, my approach to projects is different to some. Most people design something based on what skills or knowledge they may have.
    I start with what I want, and then find out how I can make that work.
    I had criteria in mind for many parts of my project that I actually had no idea if such was even feasible with Access.
    So far, everything I've planned to incorporate has proven to be very possible with Access. It would be an understatement to say I am thoroughly impressed with what Microsoft has done.
    This Combo box has proven to be the most difficult and frustrating elements so far.

  12. #27
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Ah, I see now the difference between the two fields. I should have looked more closely. Yes, just change field reference in code.

    Isn't there an embedded macro in combobox AfterUpdate event to use selected item to find record? I had removed it with intent of using VBA but instead put macro back. At least I think I used same action. I set it up with SearchForRecord macro action with WHERE CONDITION:
    ="[ClientID]=" & [Forms]![SAYTCustomerF]![SearchCombo]
    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.

  13. #28
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Ah hah!
    Yes, there is an embedded macro still in place, but your post prompted me to have a closer look at it.
    Now that I'm starting to get into and (trying) to understand relationships between tables I have discovered naming every auto record field as just ID can easily cause confusion, so I went through my tables and gave them all a more identifiable name.
    So the macro written by Access when I first set this ComboBox up still had just ID in the code. I just changed it to ClientID and voila! It works!

    I've also successfully edited your code to relate to the Search field instead of FullName AND.......successfully altered it to search from the start of the word instead of anywhere in the name by removing the first asterisk wildcard. Yes, I know. Simple stuff to most but something that still causes a certain amount of pride when done successfully by myself.


    So, In a slightly bastardised version of the words of Richard Harris..... "By George! I think we've got it"

    So Thank you, Thank you, Thank you.

    it would seem by the way, that my question is one repeatedly asked on many forums. Hopefully in future you need just link to this thread.

    Have a great day, and stay safe.

    Jim

  14. #29
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Jim & June

    I have also learned a lot from this thread so many thanks.

    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #30
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why have you removed the first wildcard? Doesn't that somewhat defeat the purpose of including spouse name in string to search or being able to search for last name instead of having to type first name? Maybe you should re-order the names in Search: LastName & ", " & FirstName & " and " + PartnersName

    Note use of + character for concatenating which is left over from ancient BASIC. Arithmetic with Null returns null. This will cause " AND " + PartnersName to return a Null if PartnersName field is null. So the result is a sort of arithmetic with strings. When Null is involved Null is returned. Prevents ending string with " and ".

    Null field is why your calc has an extra space. Consider: [FirstName] & " " + [PartnersName] & " " & [LastName]


    I used Allen Browne's tutorial as guide for code. I modified it because of the two wildcards. If you don't want the first wildcard, might want to use Allen's version and reinstate AutoExpand.
    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-15-2019, 05:54 PM
  2. Search combo box as I type inside it
    By bilalo in forum Forms
    Replies: 8
    Last Post: 07-14-2019, 09:03 PM
  3. Replies: 4
    Last Post: 01-31-2018, 10:37 AM
  4. Search as you type - Combo box
    By lateral in forum Programming
    Replies: 20
    Last Post: 01-29-2015, 12:16 AM
  5. search as you type in combo box
    By pratim09 in forum Forms
    Replies: 3
    Last Post: 08-05-2011, 07:46 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