Solved

Creating a form with Combo box in MS Access

Posted on 2014-12-28
5
205 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 34

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 34

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
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…
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…

743 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

10 Experts available now in Live!

Get 1:1 Help Now