Combo box to to add new record

[JCMcAbee]

Hello. I am sure this very easy and I am probably overlooking the obvious.

I have a form. And on this form I would like a combo box to get a value from a table. Simple, I know, if you want to limit the choices to the table.

But..

If the user needs to add a new value to the list, this error pops up: "The text you entered isn't an item in the list."

For example:

I have a table for colors..red, white & blue, and a user needs to add the color purple to it.

SO my question is...How can the user get the new value into the table with me giving him/her access to the table?

Thanks so much.

 

[TheAceMan1]

How are ya JCMcAbee . . .

Call up the properties window for the combobox and select the [blue]Event[/blue] tab. Se the [blue]On Not in List event[/blue] ... put the cursor on that line and press [blue]F1[/blue] to get the info on it. This event is just what your looking for ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JCMcAbee]

Hey AceMan

Thanks for the prompt reply. I did what you suggested, and F1 takes me to Microsoft help site. It did not offer any suggestions, just what the Build event does. I tried to build the event using all three options (expression, macro, and code) and unless I missing something, did not offer a solution.

Still stumped.

 

[TheAceMan1]

CMcAbee . . .

The main idea is to append the new data to the table, either thru a form or an append query or SQL. Then requery the combobox to update its rowsource. The updating is typically done by setting the [blue]Response[/blue] argument to the constant [purple]acDataErrAdded[/purple]. The constant also avoids the popup message you've been getting.

Have a look here faq702-4283

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JCMcAbee]

Thanks again AceMan. I can't believe I am still struggling with this. After following the steps and making sure the parameters are correct, I still get the "Not In List" error. I do, however, get the "Add To List" pop-up.

I must be missing something, but after spending a lot of time on this, I am beginning to get frustrated.

I do not want to waste too much of your time on this, but if you have more advice, I will gladly accept it.

 

[MajP]

I find the not in list event a little difficult to work with to allow the user to type into the list and then add an item. I usually provide a different and consistent UI. Either a plus sign next to a combo or use the double click event of the combo. I then set the list to not allow additions. Then I pop open a form to allow the user to add. Sometimes it is a sortable continous form to see what is available. The form is diallog with an ok cancel. If ok is selected then the record is added and the combo requeried on close. I fine this easier to control.

 

[TheAceMan1]

JCMcAbee . . .

I was expecting to hear from you sooner as it can get a little picky. Just hang in there. Its not as bad as it seems. You just need to get thru it at least once.

Post the following:
[ol][li]The code you've tried in the NotInList event.[/li]
[li]The [blue]Rowsource[/blue] of the combobox[/li][/ol]

In the meantime the following is a better critique and example of the NotInList event:

[blue]Combo boxes are commonly used to display a list of values in a table or query for a user to select from. By responding to the NotInList event, you can provide a way for the user to add values that aren't in the list.

Often the value displayed in a combo box is looked up from a record in a related table. Because the list is derived from a table or query, you must provide a way for the user to enter a new record in the underlying table. Then you can use the [blue]Requery[/blue] method to requery the list, so it contains the new value.

When a user types a value in a combo box that isn't in the list, the [blue]NotInList[/blue] event of the combo box occurs as long as the combo box's LimitToList property is set to Yes, or a column other than the combo box's bound column is displayed in the box. You can write an event procedure for the NotInList event that provides a way for the user to add a new record to the table that supplies the list's values. The NotInList event procedure includes a string argument named NewData that Microsoft Access uses to pass the text the user enters to the event procedure.

The NotInList event procedure also has a Response argument where you tell Microsoft Access what to do after the procedure runs. Depending on what action you take in the event procedure, you set the Response argument to one of three predefined constant values:

[ul]
[li] [purple]acDataErrAdded[/purple] If your event procedure enters the new value in the record source for the list or provides a way for the user to do so, set the Response argument to acDataErrAdded. Microsoft Access then requeries the combo box for you, adding the new value to the list.[/li]
[li] [purple]acDataErrDisplay[/purple] If you don't add the new value and want Microsoft Access to display the default error message, set the Response argument to acDataErrDisplay. Microsoft Access requires the user to enter a valid value from the list.[/li]
[li] [purple]acDataErrContinue[/purple] If you display your own message in the event procedure, set the Response argument to acDataErrContinue. Microsoft Access doesn't display its default error message, but still requires the user to enter a value in the field. If you don't want the user to select an existing value from the list, you can undo changes to the field by using the Undo method.[/li]
[/ul]

For example, the following event procedure asks the user whether to add a value to a list, adds the value, then uses the Response argument to tell Microsoft Access to requery the list:

[blue]Private Sub ShipperID_NotInList(NewData As String, Response As Integer)
   Dim db As Database, rst As Recordset
   
   If MsgBox("Add " & NewData & " to the list of shippers?", vbQuestion + vbYesNo) = vbYes Then
      [green]' Add shipper stored in NewData argument to the Shippers table.[/green]
      Set db = CurrentDb
      Set rst = db.OpenRecordset("Shippers")
      
      rst.AddNew
      rst!CompanyName = NewData
      rst.Update
      
      Response = acDataErrAdded        [green]' Requery the combo box list.[/green]
      Set rst = noting
      Set db = Nothing
   Else
      Response = acDataErrDisplay      [green]' Require the user to select[/green]
   End If
   
   rst.Close

End Sub[/blue]

[/blue]

I await ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JCMcAbee]

Hello. Sorry for the delay. Guess I should have waited to start this thread as things got very hectic after I posted it. I will try this and let you know where it gets me.

Thanks.

 

[puforee]

Just a thought. Select properties for the combo box. On the Data tab find the Limit To List property. Set it to NO. Next find the Allow Value List Edits property and set it to Yes if it is not already set to yes.

Just a thought.

 

[JCMcAbee]

Hello and thanks again for the help.

I believe (pure speculation on my part) that part of the problem is that I only want the new item added to an existing table. I do not want it to open a form as there is only one field in this table, and a form to hold only one control seems a bit extreme.

As a test, I created a form and added the form name to the "List Items Edit Form" in the data properties, and it seems to work.

Anyway I can get it to add directly into the table itself?

Thanks again.

 

[TheAceMan1]

JCMcAbee . . .

Give me the [blue]table & field names[/blue] and the [blue]Row Source[/blue] of the combobox ...

I have some time now, so ... the sooner you can get me th intel the better!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

JCMcAbee . . . [blue]?[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JCMcAbee]

Hi AceMan1, again my apologies. I really do appreciate the time you spend with those of us less knowledgeable.

After further reading (and trial by error), I was able to determine what I was doing wrong, and was able to get it to work just like you said.

Many thanks again.

 

[TheAceMan1]

JCMcAbee . . .

Just remember ... [blue]at Tek-Tips all ya have to to do is ask![/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]