combo box

[Guest]

I am trying to program a combo box to accept extra words when the form is
open. this works, but when i close the form the extra words i added to the
combo box disappear.
this is the code i am using
Private Sub TITLE_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!TITLE
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub
can anyone help

 

[Brendan Reynolds]

A change to the Row Source property is a design change, and would only be
saved if the form were open in design view when the change was made. To
persist the change, you'll need to store the changed value somewhere when
the form is closed, and restore it when the form opens. Given the
information available, it appears that it might be simpler to use a table
rather than a value list as the row source for your combo box, but here's an
example of one way to save and restore the values programmatically ...

Option Compare Database
Option Explicit

Private Sub Combo0_NotInList(NewData As String, Response As Integer)

Dim strWork As String

strWork = Me.Combo0.RowSource
If Right$(strWork, 1) <> ";" Then
strWork = strWork & ";"
End If
strWork = strWork & NewData
Me.Combo0.RowSource = strWork
Response = acDataErrAdded

End Sub

Private Sub Form_Close()

CurrentProject.AllForms(Me.Name).Properties.Add "Combo0RowSource", _
Me.Combo0.RowSource

End Sub

Private Sub Form_Open(Cancel As Integer)

Dim aop As AccessObjectProperty

For Each aop In CurrentProject.AllForms(Me.Name).Properties
If aop.Name = "Combo0RowSource" Then
Me.Combo0.RowSource = aop.Value
Exit For
End If
Next aop

End Sub

 

[Brendan Reynolds]

I just noticed that the code I posted will add a semi-colon to the beginning
of the string if the string is empty, which results in a blank row in the
combo box. I should have tested for an empty string before adding the
semi-colon ...

If Len(strWork) > 0 And Right$(strWork, 1) <> ";" Then
strWork = strWork & ";"
End If