How do I add to a drop down list

[Linty Fresh]

I've got a database with activities linked to a department. I want the users to choose activities for each department. Because I don't want users to mistype the activities, I have created a lookup wizard for the activities names.

Is there a way to create a form that can add new activities to the dropdown menu? I know how to go to the Activities table and add new activities manually, but I'd like a way for the user to be able to do so himself through a form. Thanks.

 

[dcx693]

Consult almost any Access manual for examples of how to add items to a combo box. Do a search of this forum. This thread is very recent.

Some programming is required to achieve this. Here are some of the basic steps:
1 - Set the limit to list property of your combo box to Yes. So when the user types an entry that is not already on the list, the LimitToList event of the combo box will fire.
2 - It's good practice to double-check that the user didn't just type something wrong and that he/she reallyl wants to add new data to the list. You can use the MsgBox function to show the user a confirmation prompt.
3 - The NotInList event has two parameters, NewData - which is pretty much self-explanatory, and Reponse. You need to set the value of Response to acDataErrDisplay, acDataErrContinue, or acDataErrAdd depending on what you want to do. You'll find more about this when you search for help.
4 - Most users like to pop-up a form so that users can enter new data into it.

If you need more help, please post again and we'll help you out.

 

[Nouba]

i assume that your working table is joined to the two tables via foreign keys. So you already have comboboxes for your linked Department and Activities tables.

An easy way adding new activities could be reached by using the Activities table in a main form and putting the linked table in a subform.

Another way is using the NotInList event of the combo box. You could put a Msgbox inside the event's code in case of typos. Then open a recordset in the code on the table Activities, add a new record, assign the value NewData to the appropriate field and update the recordset. Then set the Respond Argument of the procedure to acDataErrAdded. This will update the combo box also.

Are there more required fields in table Activities then you have to open a form anyway. After adding a record you have to set the content of the combo box explicitly and requery the combo box in the form's Close event.

 

[thepooh]

How to set the combo box to display the 3rd column after selection by user

I've created a combo box in Access where I've set the Column Count = 3 and the Column Widths to 0";0.5";1.5". The first field is hidden as it is the ID. The 2 other fields are visible to the user. When the user select the record, the combo box will display the value in the 2nd column as the selected record. What I would like to do is display the 3rd column and not the 2nd column. Is there any way that I can do this? I can't seem to find any properties to set it. Thanks.

 

[Rich]

Please don't post the same question under multiple topics