ComboBox

[S_DeLaire_2002]

I cretaed a combobox that looks up values from a table but I want to make it so that if I entered something other than what was in the table It would save it in the table

 

[ColinEssex]

Someone answered this the other day. It is very good so I'll retype it for you.

The trick is to set the LimitToList property of the combo-box to True - that way you can trap the fact that you're trying to enter something not in the list.
The event that your code goes in is the OnNotInList event. Here's an example

Private Sub ComboBoxName_NotInList(Newdata as string,response as integer)

Dim rec As Recordset

If msgbox("Value is not in list. Will add it now", vbOKCancel, "Adding Data") = vbOK Then
Set rec = CurrentDb.OpenRecordset("TableName")
rec.AddNew
rec!FieldName = NewData
rec.Update
Set rec = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
ComboBoxName.Undo
End If
End Sub

I can't remember who wrote this first but thanks anyway

Colin

 

[DJN]

You need to use the On Not In List event procedure for the combo box. First set the Limit To List property for the combo box to Yes. Then place this code in the On Not In List event procedure.

On Error GoTo err_cboYourComboBoxName_NotInList

Dim strMsg As String
Dim rst As Recordset
Dim db As Database

NewData = Capitalize(NewData)' Need the Capitalize function for this to work.

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If MsgBox(strMsg, vbOKCancel, "FieldName") = vbOK Then

Set db = CurrentDb()
Set rst = db.OpenRecordset("YourTableName")
rst.AddNew
rst!FieldName = NewData

rst.Update
Response = acDataErrAdded


rst.Close

Else

Response = acDataErrContinue

End If

exit_cboYourComboBoxName_NotInList:

Exit Sub

err_cboYourComboBoxName_NotInList:

MsgBox str(Err)
MsgBox Err.Description
Resume exit_cboYourComboName_NotInList
End If

 

[lorenzoaj]

I used ColinEssex's code and I get a run time error 13 type mismatch in this line.

Set rec = CurrentDb.OpenRecordset("TblUnit")

What does that mean and what am I doing wrong?

AJ