Storing entry in combo box

  • Thread starter Thread starter atie.joseph
  • Start date Start date
A

atie.joseph

What I currently have is a combo box that looks up a col
of values from a table. the table consists solely of this
one col.

What i need to do, is to save the manually entered value,
if it is entered.

i.e if the required value isnt in the list user then
manually enters correct value, db then saves users data
to make it available in future implementations of the
combo box.

any suggestions???
 
1) Limit to List property of combo box set to Yes
2) Use Not In List event of combo box to add the item.

Example:
Private Sub cboNamePrefix_NotInList(NewData As String, Response As Integer)

On Error GoTo CheckError

Dim msg As String, ctl As Control, Reply As Integer
Dim db As DAO.Database, rst As DAO.Recordset

Set ctl = Me!cboNamePrefix

msg = "You have entered a value that doesn't exist." & vbCrLf & "Do you want
to add it?"
Reply = MsgBox(msg, vbYesNo + vbQuestion, "Not in List")
If Reply = vbYes Then
Response = acDataErrAdded
Set db = CurrentDb
Set rst = db.OpenRecordset("tblNamePrefix")
With rst
.AddNew
![Name Prefix] = NewData 'Add data.
.Update 'Save changes.
.Close
End With
Else
Response = acDataErrContinue
ctl.Undo
End If

CleanUp:
On Error Resume Next
rst.Close
Set rst = Nothing
Set ctl = Nothing
Set db = Nothing
Exit Sub

CheckError:
msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg, vbOKOnly + vbExclamation, "Error", Err.HelpFile, Err.HelpContext
GoTo CleanUp

End Sub
 
Back
Top