Results 1 to 3 of 3
  1. #1
    fuzbuster83 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    5

    Auto-Complete


    I am trying to make a simple form for entering data for our team and I'm trying to make it as quick as possible for quick record tracking.

    I have another table (Table1) that tracks Account #, Vendor Name, Amount, and Description.
    I have a table (Table2) that lists all possible Account #'s.

    I created a form for easy entry. The steps are as follows:
    1. The user opens the form and types the account number (there is a mask for it to show xx-xx-xxxx-xx as a number).
    2. The user types the vendor name (text).
    3. The user enters the amount (currency).
    4. The user types a description (text).
    5. The user hits a Save Record button
    6. *Optional* The user can click a button to save the form or enter a new record
      • There is also a button to move to the next record or previous record.

    What I'm trying to eliminate is the repetitive typing and make the Account # and the Vendor Name fields offer AutoComplete (like Excel would in a same column entry) or provide a drop-down list if auto-complete is not an option. I did try to work on a drop-down list, but for some reason it keeps entering the ID into the field instead of the Account #.

    Any help is appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Use a multi-column combobox for selecting account number. Advise not to use spaces nor punctuation/special characters in naming convention.

    If ID is designated as primary key, then set combobox properties with:

    RowSource: SELECT ID, AccountNum, VendorName FROM Accounts;
    BoundColumn: 1
    ColumnCount: 3
    ColumnWidths: 0";1";1.5

    Textboxes can reference combobox columns by index. Index begins with 0. So textbox for vendor name would have expression in ControlSource:
    =[comboboxname].Column(2)

    If you are not using ID as primary key and saving as foreign key, why have the field at all? If you want to save AccountNum then don't include ID in the RowSource and adjust settings as necessary and AccountNum should be designated as PK.

    If you want the same account number to carry forward to next record, use VBA to set combobox DefaultValue property.
    Sub comboboxname_AfterUpdate()
    Me.comboboxname.DefaultValue = Me.comboboxname
    End Sub
    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.

  3. #3
    fuzbuster83 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    5
    Thanks, I will give this a shot.

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

Similar Threads

  1. Auto Complete A Subform
    By dgutsche in forum Forms
    Replies: 1
    Last Post: 08-14-2014, 04:10 PM
  2. How to auto-complete a data entry.
    By gottnoskill in forum Forms
    Replies: 8
    Last Post: 01-23-2014, 02:04 PM
  3. auto complete fields
    By mona in forum Access
    Replies: 3
    Last Post: 05-09-2012, 07:41 AM
  4. Replies: 3
    Last Post: 11-04-2011, 01:50 PM
  5. Auto Complete Data
    By manicamaniac in forum Access
    Replies: 5
    Last Post: 09-14-2010, 03:38 PM

Tags for this Thread

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