Results 1 to 7 of 7
  1. #1
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60

    Search for the non-bound, text in a bound combo box

    I think someone must have asked this question before, but my searching skills must be sub-par.

    I have a combo box that is bound to a integer field (SQL Server linked table, if that's significant), but displays the text description of that key by hiding the number. The problem is, when users want to filter or search for items in the form using that combo box, they are asked to enter a number, and searches for the code don't work, because Access is trying to compare them to the numbered key.

    Is there a way to have the user to be able to search and filter on this combo-box using the built-in access GUI?

    Thanks.

  2. #2
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Quote Originally Posted by drexasaurus View Post
    I think someone must have asked this question before, but my searching skills must be sub-par.

    I have a combo box that is bound to a integer field (SQL Server linked table, if that's significant), but displays the text description of that key by hiding the number. The problem is, when users want to filter or search for items in the form using that combo box, they are asked to enter a number, and searches for the code don't work, because Access is trying to compare them to the numbered key.

    Is there a way to have the user to be able to search and filter on this combo-box using the built-in access GUI?

    Thanks.
    I'm not quite sure about your question.

    Where are the users being asked for a number? What do you mean Access is trying to compare them to the numbered key?

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I do not understand the question

    Are you saying you have a bound field where, let's say the value is 2013, you are displaying a text value of 'two thousand thirteen'?

    Secondly, if it's a bound field, you do not want to use it as a search criteria, because when you change the value the data on your table will be changed, all 'search' text boxes should be unbound.

  4. #4
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    I mean, there is an item with an automated number, like an autonumber, but users tend to think of them as unique codes (like "CA-123") because they are generally not aware of the automated number. The combo box is bound to the unique number, but only shows the text value they usually see in place of that number. If they want to see all the rows related to "CA-123", they would usually right click and select "filter where 'such-a-field' = CA-123" or "Begins with="CA-". But instead, when they right click, they see "filter where number = 1743" which means nothing to them. This doesn't seem to happen to all forms or combo boxes, so I was wondering if there was a way to change this behavior.

    But the more I think about it, I might just have to find a way to prevent users from filtering on that value. Maybe provide a separate filtering mechanism instead. Since the text code is not part of the forms rowsource, maybe this is impossible, and I just assumed that is was allowing this to happen in other forms when in fact it had not.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you are altering the appearance of your autonumber PK so your users can see it on a form change it so that the bound column is your PK and have a secondary field of your combo box that is the 'converted' display value, but then you will have to modify your filtering code to look at

    me.comboboxname.column(1)

    because combo box column numbers start at 0

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You could try formatting the field at the table level. Format = >"CA-"

    Not sure if that will suit your needs in case the text changes from record to record. Separate columns for display purposes makes the most sense to me. Run some update queries and rebuild some controls on some forms.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You have users who even know about right click shortcut? They should be able to learn that the alpha prefix is not needed and just specify the number part to search on.
    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. Bound Text Box Not Requerying
    By altemir in forum Forms
    Replies: 3
    Last Post: 02-19-2013, 09:06 PM
  2. Replies: 2
    Last Post: 06-11-2012, 09:37 AM
  3. Text box bound to combo box
    By Tari in forum Forms
    Replies: 4
    Last Post: 01-11-2012, 02:19 PM
  4. Bound form with bound combobox
    By Jerry8989 in forum Access
    Replies: 2
    Last Post: 12-05-2011, 01:50 PM
  5. How to bound a text box
    By bundy75 in forum Forms
    Replies: 10
    Last Post: 07-30-2010, 02:50 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