Results 1 to 4 of 4
  1. #1
    Niet3sche is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    2

    Access 2010, combo box, and Auto Expand (Auto-Complete) woes.

    Hello.

    I'm having a baffling issue and I would hope that someone where has at least seen it before and can offer a work-around, if not a fix.



    I have a combo box. I have lots of them, but let's go to one specific form.

    On this form, I am adding a person to a task. My combo box has a straightforward row source:
    Code:
    SELECT Personnel.ID, Format([ID]) AS [ID-Text], Personnel.FullName, Personnel.LastName, Personnel.FirstName FROM Personnel ORDER BY Personnel.LastName, Personnel.FirstName;
    I am CASTing the ID (an auto-number PK) to text based upon the suggestion from the Internet, which is odd, but seems to work--sometimes.

    UPDATE: This is fixed. I think. I'm leaving this here for others to find. User "dan some" posted about the importance of DISTINCT in your row source query.

    I have no repetition, but putting DISTINCT in there--to guarantee no repetition...of auto-numbered fields--seems to have fixed this.

    It surely cannot be just me having this issue here, can it?!
    Last edited by Niet3sche; 11-04-2011 at 12:21 PM. Reason: Emphasizing the fact that this appears fixed.

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by Niet3sche View Post

    It surely cannot be just me having this issue here, can it?!
    I wouldn't know because I can't see your row source because you used the VBA code tag with a single line of code which obscures it. Just use the regular code tag or none.

  3. #3
    Niet3sche is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    2
    Quote Originally Posted by boblarson View Post
    I wouldn't know because I can't see your row source because you used the VBA code tag with a single line of code which obscures it. Just use the regular code tag or none.
    The SQL tag hoses SQL?

    Yikes.


    SELECT Personnel.ID, Format([ID]) AS [ID-Text], Personnel.FullName, Personnel.LastName, Personnel.FirstName FROM Personnel ORDER BY Personnel.LastName, Personnel.FirstName;


    The fix for this was
    SELECT DISTINCT ...

    Even though I don't have duplicates, which is why I'm confused at this being a valid fix. And which is why I'm putting it here, with the "solved" tag, so that others may benefit from an immediate answer in a more timely manner than I.

    I'll probably back out the CAST (Format) call as well ... as I said, that came from the Internet and offered some hope at the time.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Not sure why you would need a DISTINCT in there. It isn't logical if your ID field is an autonumber and set to PK so it is Indexed with no duplicates. Interesting, but I wonder if you ran a DECOMPILE and then ran Compact and Repair if it would actually fix it so you didn't need it. If you choose to try that, make sure to make a copy before decompiling in case something gets worse when doing it.

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

Similar Threads

  1. Auto complete field on form
    By oam in forum Access
    Replies: 5
    Last Post: 09-23-2011, 12:45 PM
  2. Dlookup to auto complete in form
    By custhasno in forum Access
    Replies: 2
    Last Post: 09-08-2011, 12:53 PM
  3. Replies: 1
    Last Post: 03-31-2011, 02:51 AM
  4. Auto Complete Data
    By manicamaniac in forum Access
    Replies: 5
    Last Post: 09-14-2010, 03:38 PM
  5. Auto expand memo field vertically to fit text
    By grant.smalley in forum Forms
    Replies: 1
    Last Post: 02-04-2010, 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