Add records to Access 2007 Table from within an Access 2007 form

Posted on 2011-05-10
Last Modified: 2013-11-28
In Access 2007 I have a form that's bound to a table, I need one field on that form to allow me to add records to the table at times, while other times I'd want to select from existing data, I tried both the Combo Box or List Box options and it seems like I'm missing something here.
Question by:codedigger
    LVL 77

    Assisted Solution

    You cannot use the same bound field to edit and to search (without some very complex programming)

    If you want to search for a record you need to add an unbound combo using the wizard and choosing the option 'find a record in my form'.  Keep the data entry box separate.

    Author Comment

    I don't need to "Search" for an exisitng value, in a comprable setup I'm able to select from an existing value from the drop down list, or simply type in a new value if what I'm looking for doesn't exist, the issue here is, I tried to emulate what I've done in my test environment to that in the production environment but couldn't form some reason and that's why I got in touch with  you guys.
    LVL 77

    Assisted Solution

    OK , I see.

    If the combo is just using a single field (from the current data) as its rowsource then you should be able to do this if you make sure that the Limit to list property of the combo  is set to No.

    LVL 21

    Assisted Solution

    by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
    Access does have a build-in find ( ctril+F) on a bound field.

    If it were mine I would add a search control to the form header. The combo box control wizard will create one for you.

    Also see:
    LVL 21
    Maybe this is what you need:

    Adding values to lookup tables
    LVL 8

    Accepted Solution

    You do do a nice little cheat and base a combo boxes row source on a SELECT statement that lists everything in the field already.

    Set Limit To List to "No"
    Set AutoExpand to "Yes"
    Set Control Source to "FieldName" (change to whatever your fieldname is)
    Set Row Source to "SELECT DISTINCT FieldName FROM TableName ORDER BY FieldName ASC" (change to whatever your field name and table name should be.)

    By using the "DISTINCT" duplicates are eliminated.  Using AutoExpand means that you'll find a value if it's in there already.  Having Limit To List set to No means you can add new values on the fly.

    The only downside is that there is is no way to handle data validation.  If you use a more convoluted Not In List approach, you can fire up another form to handle your data entry to this field, for example.

    Author Closing Comment

    The solution(s) worked and thanks to all 3 experts, other unrelated design issues are at play as well but I got what I need from the Exchange for now.

    Many Thanks to all of you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    This collection of functions covers all the normal rounding methods of just about any numeric value.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now