Solved

Creating a form with Combo box in MS Access

Posted on 2014-12-28
5
211 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 84
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 35

Accepted Solution

by:
PatHartman earned 250 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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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 35

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

816 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

8 Experts available now in Live!

Get 1:1 Help Now