How to display the contents of a combo box on a form

[sawilliams]

My question is about how to display the contents of a combo box on a form. I have a combo box that looks up the values in a table. The table (tblKeyWords) has only two fields ("KeyWord_ID" and "KeyWord"). I really only want the field "KeyWord" to display on the form. But I also want the user to be able to add new entries to the KeyWord field. If I change the property "Limit to List" to "no" I get the error "Microsoft Office Access can't set the LimitToList property to No right now. The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column. Adjust the ColumnWidths property first, and then set the LimitToList property." My properties are:

Column Count = 2
Column Heads = No
Column Widths = 0",1"
Bound Column = 1

So, if I change Bound Column to = 2, I can change "Limit To List" to "No", but when I open the form, the combo box displays only the KeyWord_ID field and when I click on it, I see the KeyWords but when I select one, I get the error "The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits."

I'm not sure how to proceed.

 

[Remou]

Set Limit to List back to yes and search for Not in List Event. You will find a number of posts here, at least one article on the Microsoft site and a good deal of information in Help, which you can access by pressing F1 after finding event on the Events tab of the property sheet for the combobox.

 

[ZOR]

Dont know if this helps, it is what I am using now and works okay. I have a combo box named KWords. In the combo box my query is :

SELECT Keywords.KeywordID, Keywords.Keyword
FROM Keywords;

In the notInList event I use this code:

Private Sub KWords_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
Dim ctl As Control

Set ctl = Me.KWords
Response = False 'acDataErrContinue
If MsgBox("The Keyword of " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then
Set db = CurrentDb()

SQL = "Select * From Keywords"
Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
rst.AddNew
NewData = MyProperCase(NewData, 1)
rst![Keyword] = NewData
rst.Update
Set rst = Nothing
Response = acDataErrAdded
DoEvents


Else

Response = acDataErrContinue
ctl.Undo
End If
End Sub

Hope its of help.

Regards

 

[ZOR]

Sorry, forgot to add, My combo columncount is 2, my widths are 0;2 and my bound column is 2. Cannot remember if it immediately shows the added keyword, I think it does, otherwise you will have to requery the combo box afterwards.

 

[BrianLe]

I have something very similar in Access 97.

You may try adding a query tblKeywords Query as follows:

SELECT tblKeywords.Keyword
FROM tblKeywords

The form with the combobox would have a record source of tblKeywords Query

The combobox would have a control source of Keyword,a row source of tblKeywords Query, column count 2, bound column 1, column width 1",0"

 

[sawilliams]

Thanks, remou, ZOR and BrianLe. Your combined input sent me in the right direction and I have made it work.