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

Creating a form with Combo box in MS Access

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.
  • 2
  • 2
2 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

ChrysaorAuthor Commented:
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!
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespaceā€™s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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