Results 1 to 13 of 13
  1. #1
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99

    Combo box Search

    Hey Tech Friends,
    its been a while since my last post trust everyone is ok...I have this MainForm with a drop down combo box with names from my db when a name is click on the record info pulls up.. however the sub form in tab control pulls up the information that will display from a junction table so it is in datasheet view and not continuous form view. when i use the combo box and search for a name it will automatically pull names to search exg typing a...b... c..etc. But when a name is not found and i press enter i get an error in the code and not Msgbox saying "person not found" I cant seem to find where my mistake is any suggestions???? want to be able to search in combo box but not necessarily filter the subforms in TabControl.. jus wanted a message person not found and later learn how to ask do u want to add person
    MyCode:
    Private Sub cbosearch_AfterUpdate()
    Dim rst As Recordset
    Dim strCriteria As String


    strCriteria = "[Inm_ID] =" & Me.cbosearch
    Me.RecordsetClone.FindFirst (strCriteria)
    If Me.RecordsetClone.NoMatch Then
    MsgBox "Person not Found"
    Else
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    End Sub

    Me.RecordsetClone.FindFirst (strCriteria)
    keep getting a debug on this line of code error.."3077"

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Is Inm_ID a number type field?

    Why declare rst variable then not use it?

    Code works for me. Step debug. 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.

  4. #4
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Hi June7 yes its an AutoNumber primarykey field to be honest am struggling with vba think i copied that code somewhere or got it from you guys as I've said, learned a lot since joining this family


    Quote Originally Posted by June7 View Post
    Is Inm_ID a number type field?

    Quote Originally Posted by June7 View Post
    Why declare rst variable then not use it?
    Don't fully understand; bought some books this pass year... am self taught

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    You have to catch the possibility of Null.

    Try:
    Code:
    strCriteria = "[Inm_ID] =" & Nz(Me.cbosearch,0)
    Don't fully understand;...
    Just remove the line
    Code:
    Dim rst As Recordset
    It is uselessness.

  6. #6
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Yes...LoL accesstos almost there my Msgbox "Person Not Found" only shows up after I try clearing the search box with a false name not on pressing enter... there's a ms access ["pretext person not in list select item from list or enter text from list"]even while searching I can detect filtering through names a person is not in db ...do i have to put a [keycode for enter]? would like the user to search combo box for names if none then say not found.....

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Jamesy_007 View Post
    ....i press enter i get an error in the code and not Msgbox saying "person not found" I cant seem to find where my mistake is any suggestions????
    I'm not sure that I understand your requirement properly but I think you may need to use the "Not On List" event rather than the "After Update". Obviously, the code would need to be different.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Absolutely agree with Bob.

    Better to code the "NotInList" event of combobox. It occurs before "AfterUpdate", so you can give the option to the user for adding a new person. Remember to set the "Limit to list" property of combobox to "Yes".

    Please review:
    https://docs.microsoft.com/en-us/off...obox.notinlist

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Use NotInList if a value typed into combobox is not in combobox list.

    Use AfterUpdate if typed value is in combobox list and want to search for record in form's dataset.
    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. #10
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Bob Fitz View Post
    I'm not sure that I understand your requirement properly but I think you may need to use the "Not On List" event rather than the "After Update". Obviously, the code would need to be different.
    Tried the Not On List event can't load any info from db not working... ill explain again....have this MainForm with several sub forms bound in Tab control... reason being is that one person, i have to track several other information so in tab control its easier don't need to filter info in subforms as i type in the [search box/combo box] at the helm in the Main Form because there's other critical info being displayed e.x.g ...Address, Phone etc on the main form. No need to find any records in subforms because all i tied to primary and foreign keys... The sub forms mostly have junction tables info... trying to search in the main form [searchbox]...and if a name is not recognized MsgBox says Person not found.. As is now when a pseudo name is type and enter is press my coded msgbox don't display... only when clearing back the false name typed in search box the message comes up.....

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So combobox LimitToList property is not set to yes?

    Suggest you provide db for analysis.
    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
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    007_db temp.zip
    Quote Originally Posted by June7 View Post
    So combobox LimitToList property is not set to yes?

    Suggest you provide db for analysis.
    YES! think am ah lil proficient lol.... vba is my headache not a programmer but love what Ms Access can do your expertise is appreciated

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want to customize a response when user types in a name that is not in combobox list, use NotInList event. This is a common topic. Review https://blueclawdatabase.com/notinlist-event-code/

    Otherwise, your combobox works.
    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: 09-22-2016, 08:42 AM
  2. Replies: 3
    Last Post: 01-04-2015, 06:09 PM
  3. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  4. Replies: 1
    Last Post: 04-20-2012, 03:16 AM
  5. Replies: 4
    Last Post: 08-16-2011, 05:54 PM

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