Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15

    Search as you type in Combo Box

    This is a continuation of an old thread I mistakenly added my question to. I have been advised to start again as the old thread was marked "closed" Rookie mistake.



    https://www.accessforums.net/showthr...318#post458318

    Take 2!
    Here's what I posted a few hours ago.

    Be kind please. First post. (Well, third now I guess.)
    I'm a rank amateur beginner but I'm slowly learning and enjoying the process. I've been at it for about a month.

    I realise this is a very old thread but I've been trying to get this same VBA working and am still getting the same result as aonsu209 was getting. (In the original thread)
    Namely, the list part of the combo box goes blank.

    Judging from the spaces being shown as I enter characters and the list contracts and knowing what is in the relevant table I'd say the search itself is working fine.

    Code:
    Private Sub SearchCombo_Change()Dim strSQL As String
    If Len(Me.SearchCombo.Text) > 0 Then
    strSQL = "SELECT CustomerT.Search FROM CustomerT WHERE (((CustomerT.Search) Like '*" & Me.SearchCombo.Text & "*')) ORDER BY CustomerT.Search;"
    Else
    strSQL = "SELECT CustomerT.Search FROM CustomerT ORDER BY CustomerT.Search;"    'This is the default row source of combo box
     
    End If
    Me.SearchCombo.RowSource = strSQL
    Me.SearchCombo.Dropdown
    End Sub
    the Row Source for my combo box is
    Code:
    SELECT CustomerT.[ClientID], CustomerT.[Search] FROM CustomerT;
    The ComboBox name is "SearchCombo"
    Table name is CustomerT
    I've used a calculated field to combine the columns "FirstName", "PartnersName" & "LastName" into one field called "Search" This is the field that populates the ComboBox

    Can anyone please help?

    Cheers
    Jim

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Every character you type is a search,
    so why do N searches when you can do only 1 when the user enters the word?

  3. #3
    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 is ClientID in the initial RowSource but not included in the SQL statements in VBA?

    What are ColumnCount and ColumnWidths properties set to?
    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.

  4. #4
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Column Count = 2
    Column Width = 0cm;1.905cm;2.54cm

    My knowledge of VBA is insufficient to be able to edit correctly but I'll have a go.

    Code:
    SELECT ClientID, CustomerT.Search FROM CustomerT WHERE (((CustomerT.Search)
    Is this correct? Remember, 4 weeks only on Access. Maybe 24 hours total on VBA. Up to now, I've just copy 'n pasted code from other sources and substituted names of my components only. My attempt here is based solely on patterns I've seen in other peoples code.
    Can you assist with correct syntax? And, thank you for your interest.
    Jim

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

    If your Form is based on your Customer table a simple Combobox in the Header of the Form will do exactly what you neede

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You have ColumnCount of 2 yet you have widths for 3 columns. Then the SQL statements in VBA retrieve only 1 column and since first column is set for 0 width of course nothing shows.

    Fix the SQL and property settings so they make sense and work together.
    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. #7
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    June7, thank you so much for your help.

    I believe I've successfully followed your advice. My attempt at editing the SQL line appears to be correct. It didn't throw up any error messages anyway and the function is now very close to what I anticipated it should be.
    But.....Not quite there yet.

    Probably best to illustrate with screen grabs.
    The Combo box upon opening the form appears blank. Just as it has always been and how Combo Boxes are designed to function..

    If I type in a single character such as "b" it does what Combo Boxes do and gives the the first record with a "b" in it.

    Click image for larger version. 

Name:	Screenshot (2).png 
Views:	24 
Size:	5.9 KB 
ID:	42377

    So far, no Search-As-You-Type

    But, if I simply hit the Delete key and remove the highlighted section that Access gave me, this happens.

    Click image for larger version. 

Name:	Screenshot (3).png 
Views:	24 
Size:	22.3 KB 
ID:	42378

    Now I have every record that contains a "b"

    Type in another letter and it behaves exactly as Search-As-You-Type should and reduces the list down to records with that combination only.

    Click image for larger version. 

Name:	Screenshot (4).png 
Views:	24 
Size:	10.6 KB 
ID:	42379

    We're doing real well at this stage. What's important (to me) to note is that it is selecting records from any of the possibilities. Please note however that its selecting a record with that combination anywhere in the name, not just the start. Note the last record listed.
    I could possibly live with that and I may be able to fix it myself by removing the first asterisk ( * ) or wildcard from the SQL search.

    But, if I then click on one of those names to bring their info into the form, the following happens

    Click image for larger version. 

Name:	Screenshot (5).png 
Views:	24 
Size:	16.5 KB 
ID:	42382

    Hit "Debug" and I get the following.
    Click image for larger version. 

Name:	Screenshot (6).png 
Views:	24 
Size:	18.2 KB 
ID:	42383
    .

    Can you, or anyone else, help me

    1) get rid of that error message and
    2) show me how to stop the Combo Box from getting the first record only so that the user has to delete it before proceeding?

    Jim

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  9. #9
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Fair enough. Instructions seemed to be fairly straight forward.

    You'll need names etc in the table to test the search as you type so I imported some from Northwind with addresses etc.
    The only part that won't work is there is a picture of each job on each record. But they're links, not embedded, so I just deleted all the local links.
    Everything else should work.

    Two copies of the CustomerF form.
    CustomerFOLD is the original I started with.
    SAYTCustomerF is where I'm trying to get search as you type working.

    I've tested both and both seem to be working the same as the originals.

    Please let me know if you need anything else
    And thank you once again for trying to assist.


    Jim
    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I should have thought to ask you if combobox AutoExpand was set to No. Just make that change.

    Advise not to use punctuation/special characters in naming convention - such as E-mail, better would be EMail.
    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.

  11. #11
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    The table in the zip file is originally from Northwind. I then deleted and added columns to make it match my original. I missed the Email name not being correct. No matter. Its not being used in this instance. There's no data in it.
    The naming convention I've decided to adopt is no spaces or underscores and capitol letters for each new word, and then T, R, F or Q for Tables, Reports Forms and Queries.
    So, CustomerT, NewProductF etc

    is AutoExpand the same as CanShrink CanGrow? If so, yes, both are set to no.

    Jim

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, not the same.
    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. #13
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Jim

    I normally just use the method shown in the attached.

    Look at SAYTCustomerF


    Attached Files Attached Files

  14. #14
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    Mike, I appreciate the time you've taken to have a look at my issue, but it would appear it is now just a standard Combo Box, exactly the same as what I was originally using which you can see in CustomerF in the download file.
    I'm looking for a search AS you type function. Something totally different. But, thank you for having a look.
    Cheers
    Jim

  15. #15
    Grandad is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Location
    Australia
    Posts
    15
    June7, I could not find that property in the property sheet for the Combo Box. I had to google the term to find out what I could about it and yes, and once I read the description I totally agree with you. That could explain things for me.
    Now, the question is, where do I find it? Or am I just going blind? (Not an entirely outrageous possibility mind you)
    One possibility perhaps, you are using Access 2010 and I'm on 2016. Is it possible Microsoft started calling it something different now?
    Jim

Page 1 of 2 12 LastLast
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