• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • Last Modified:

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

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.
0
Roberto Madro R.
Asked:
Roberto Madro R.
  • 2
  • 2
  • 2
  • +1
4 Solutions
 
peter57rCommented:
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.
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
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.
0
 
peter57rCommented:
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.

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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: http://allenbrowne.com/ser-62.html
 
 
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Maybe this is what you need:

Adding values to lookup tables
0
 
Andrew_WebsterCommented:
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.
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now