?
Solved

Creating a form with Combo box in MS Access

Posted on 2014-12-28
5
Medium Priority
?
235 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
  • 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 40

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 40

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

Industry Leaders: 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!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

830 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