Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Creating a form with Combo box in MS Access

Posted on 2014-12-28
5
Medium Priority
?
231 Views
Last Modified: 2015-01-05
First of all i'd like to clarify that im a total beginner im MS Access and in databases in general.

So im trying to create a form which will include

1. A combo box linked to the data in Table A.
2. A text box which linken to the data in Table B
3. A button, so that when I type something in the textbox and select something from combo box that will connect them in a linken Table C (will also save the data in Table B)

Also, I would like to have the option to add a new item in the combo box (therefore in Table A) in case the item is not available.

I just need some general guidelines so that I know how to start/where to read. Thanks a lot.
0
Comment
Question by:Chrysaor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 85
ID: 40520405
In general, building a single form that includes data from multiple sources can be troublesome. This often results in a non-updateable query, which means your users cannot enter or edit data on the form.

If you could give a little more detail about your application, and what sort of data it will handle, we would be better able to help you with this.

Regarding adding a new value to a bound combo, you can use the NotInListEvent. See this question for more details:

http://www.experts-exchange.com/Database/MS_Access/Q_25170097.html
0
 

Author Comment

by:Chrysaor
ID: 40520427
Well , in the first step I am trying to do this:

Table Muscles: ID (AutoNumber), Muscle (Text)
Table Nerves: ID (AutoNumber), Nerve (Text)

Then im trying to create a Form in which I will be able to select a Nerve from the Table Nerves (or add customly a new Nerv which will be saved in Table Nerves), and link it to a Muscle (or better multiple muscles, also here i want the option to customly add a new Muscle Name). But I somehow always come to errors!
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 1000 total points
ID: 40521097
The form should be bound to table C which is the relation table that implements the many-to-many relationship.  You will have a combo with a RowSource bound to the Muscles table and the control itself will be bound to the MuscleID field of tblC.  Than a combo with a RowSource bound to the Nerves table and that control will be bound to the NerveID field of tblC.

Personally, I don't like using the NotInList event to allow users to add rows to combos on the fly.  I think it is too easy for them to add typos using this method so I only do it in rare cases.  In this case, we aren't manufacturing new Muscles and Nerves so I wouldn't allow it.  I would create a maintenance form for each type of data and make them add it there first where I can provide them with search capabilities and try to prevent inadvertent duplications caused by misspellings.

I've attached a sample database that shows how to work with many-to-many relationships.  This sample is venues and events.  It shows how you can work with the data from either side.  So you can look at the events for a venue or you can look at the venues for an event.  In your case, think of Muscles as venues and Nerves as events as you review the example.
ManyToMany.zip
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 40521683
I like Pat's idea of a 'maintenance form' instead of my suggestion to use the NotInList event. As indicted, there are no "unknown" factors here - the nerves and muscles in the human body are well documented, and it's not likely we'll find anything new (unless those stories of alien abductions are true, and then we've go a whole 'nother problem on our hands  ...).

If you must relate multiple Nerves to multiple Muscles, then you'd perhaps have to use listboxes instead and use VBA to save the selections. However, I think you'd be better off with two distinct forms - one to handle the Nerve-to-Muscles relationship, and another to handle the Muscle-to-Nerves relationship. Essentially, the user would select a Nerve (from a combo, perhaps), and then you'd present them a listbox of all available muscles. They would select one or more items in that listbox, and you'd use VBA to save their selections. Same with the Muscles - you'd show them a combo of Muscles, and then populate a Listbox of all available nerves.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40521861
I wouldn't use multi-select list boxes.  They require code to populate and save and require processing recordsets behind the scenes because you would be storing the data in a normalized schema.  I would just use the subform as in my example database which is Customers-Venues, not Events-Venues.  I hadn't looked at it in quite some time.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

609 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