Results 1 to 4 of 4
  1. #1
    jkk is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    9

    Autofill based on a field other than Bound Colulmn?

    Hi, this question regards Access 2010.



    A little orientation to the table and field names relevant to my question.


    • The first table is called Clients.
      • The Primary Key field for the Clients table is an AutoNumber field called ClientID.
      • The other two relevant fields in this table are ClientFirstName and ClientLastName.

    • The second table is called Contacts.
      • In the Contacts table is a foreign key field, bound to the ClientID field from the Clients table, and also named ClientID.




    On the Contacts table, the ClientID field properties are set on the Lookup tab as follows:

    Display Control: Combo Box
    Row Source Type: Table/Query
    Row Source: SELECT clients.ClientID, clients.ClientFirstName, clients.ClientLastName
    Bound Column: 1

    Further, in the Row Source's Query Builder, ClientFirstName and Client LastName both have Ascending selected as Sort criteria.


    Everything works as intended: When entering data into the the Contacts table (actually, the data is entered via a form built from the Contacts table) the data entry person can click the drop-down on the combo box for the ClientID field, scroll through ClientID records, and see unique ID numbers sorted by first and last names. And the actual data for the field is stored properly as the unique ClientID number.

    Okay, sorry for the long preamble, just trying to provide an overview before asking my question. So here it is: When entering ClientID data into the Contacts table (or form), is it possible to enable autofill based on ClientFirstName and ClientLastName, rather than ClientID? For instance, let's say we have a few clients named Joe Smith. It would be great to just start typing "Joe Smith," and have the field autofill the ClientID number for the first client named Joe Smith. The intention is to make data entry a bit quicker.

    If anyone has other suggestions or solutions, I'm open to those, as well.

    Thanks!

  2. #2
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Access will try to autocomplete based on the first visible Column in a Combo Box. So if you want the user to be able to type in a name, then you can simply make that the first Column. Normally, I do this by just making the ID Column invisible (setting it's width to 0").

    If you want the user to be able to see the ClientID field, you can just move the ClientID to the last Column.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I suggest search by LastName FirstName.

    RowSource: SELECT ClientID, LastName & ", " & FirstName AS LastFirst FROM Contacts ORDER BY LastName, FirstName;

    ColumnCount: 2

    ColumnWidths: 0";2.0"
    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
    jkk is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    9
    Rawb and June7, thank you both for responding.

    Rawb, yes, it is important that the ClientID be visible, and I had tried what you suggested back when I was building the db. The problem with that approach was that the data was stored as text if ClientFirstName was set as the first column in the Row Source query. My understanding is that "behind the scenes" that doesn't really matter, as long as the Bound Column property is set to match whatever column number the ClientID field was in the query's order. (Yes? No?) But it added another step and another column to queries built from the Contacts table, if I wanted to actually see the ClientID number associated with the Contacts record. (Which I did want to see, especially since the queries are often exported to Excel and I needed a unique client identifier for sorting, getting data from pivot tables, etc.) Anyway, comments definitely welcomed!

    June7, thanks again. And not to be picky, just wanting to be clear in case others are "following along:" To match my actual field and table names, I think your suggestion for modifying Row Source would be:

    SELECT ClientID, ClientLastName & ", " & ClientFirstName AS LastFirst FROM Clients ORDER BY ClientLastName, ClientFirstName;

    Which is a great suggestion, except that more than half the time, the client's last name isn't even given! So it's preferable to have ClientFirstName as the first visible/autofill field. But I think you've pointed me toward a solution which addresses both my question, and also the issue I mentioned above of wanting to have the ClientID number visibly stored in the record.

    I think the following will work:

    Row Source: SELECT ClientID, ClientFirstName & " " & ClientLastName & " " & ClientID AS FirstLastID FROM Clients ORDER BY Clients.ClientFirstName, Clients.ClientLastName;

    Bound Column: 1

    Column Count: 2

    Column Widths: 0";2"

    I've tried this out and it works great for what I wanted to accomplish. Thanks!

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

Similar Threads

  1. Autofill based on Autonumber Field?
    By W.Chan in forum Access
    Replies: 2
    Last Post: 12-06-2012, 10:51 PM
  2. Replies: 2
    Last Post: 09-13-2012, 03:42 PM
  3. Autofill field based on combo box
    By topp in forum Access
    Replies: 2
    Last Post: 06-26-2012, 04:36 PM
  4. PLEASE help: Autofill based on one field
    By Suzanne in forum Forms
    Replies: 5
    Last Post: 07-07-2011, 11:09 AM
  5. Autofill of a field based on another
    By MyWebdots in forum Forms
    Replies: 7
    Last Post: 07-12-2010, 05:00 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