RowSource Property Lost

[Moon66]

I have a bound combo box. the RowSourceType is set to ValueList. The
LimitToList is set to true. Using the NotInList event and the AddItem method
to add an item to the combo box the added item shows up in the property
window and also shows up in the combo box. Close the form and reopen it and
Poof it's gone. I have also tried to do this by
ctlCurrentControl.RowSource = ctlCurrentControl.RowSource & NewData & ";"
Again Poof

 

[Dirk Goldgar]

I have a bound combo box. the RowSourceType is set to ValueList. The
LimitToList is set to true. Using the NotInList event and the AddItem
method to add an item to the combo box the added item shows up in the
property window and also shows up in the combo box. Close the form
and reopen it and Poof it's gone. I have also tried to do this by
ctlCurrentControl.RowSource = ctlCurrentControl.RowSource & NewData &
";" Again Poof

For the most part, design changes like this that you make at run time --
when the form is not open in design view -- don't stick. That gives you
a lot of freedom to switch form properties around at run time to control
the form's behavior, but there are some times when it may keep you from
doing what you want.

If you want to add something to a value-list rowsource permanently,
you'll have to put the form into design view, at least momentarily. As
an alternative to that, I'd suggest you use a table/query rowsource and
add the new item to the table instead.

 

[Ofer]

If you want the new value that you added to apear in the form after you close
it, you need to save the form on the UnLoad event of the form

docmd.Save acForm,"FormName"

But that will save everyting else that you might have done while the form
was open, like assign a default value to one of the fields using code.
In that case did you consider using a table as the RowSource, and all you
have to do, is add a new value to the table

 

[Dirk Goldgar]

If you want the new value that you added to apear in the form after
you close it, you need to save the form on the UnLoad event of the
form

docmd.Save acForm,"FormName"

But that will save everyting else that you might have done while the
form was open, like assign a default value to one of the fields using

And I don't think it will work, either, unless you assigned the
rowsource property while in design view.

 

[Moon66]

I have 17 forms with an average of 20 combo box's that are being used for a
selection process and none of the items in the cbo are the same. The code I'm
using came from the MSDN web site and I thought this was a run time method of
setting the RowSource property. I'm trying to find a method of dealing with
the 340 cbo's client side and not have a table with that many record sets.

 

[Ofer]

One of the problem I had is, if you have a code break on the form, while the
form is running, and you change the code and save it, it save all the setting
I applied on the OnLoad event of the form, like defaults, colors, filter etc.
So I assumed that it should work.
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[Brendan Reynolds]

You need to store the modified row source somewhere, and re-assign it the
next time the form is opened. Here's one possible solution (assumes Access
2000 or later, won't work in Access 97 or earlier) ...

Option Compare Database
Option Explicit

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

If MsgBox("The text '" & NewData & "' you entered is not in the list -
do you want to add it?", vbYesNo) = vbYes Then
Me.Combo0.AddItem NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Private Sub Form_Close()

Dim aob As AccessObject

Set aob = CurrentProject.AllForms(Me.Name)
aob.Properties.Add "Combo0RowSource", Me.Combo0.RowSource

End Sub

Private Sub Form_Open(Cancel As Integer)

Dim aob As AccessObject
Dim aop As AccessObjectProperty

Me.Combo0.RowSourceType = "Value List"
Me.Combo0.LimitToList = True

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

End Sub

 

[Dirk Goldgar]

One of the problem I had is, if you have a code break on the form,
while the form is running, and you change the code and save it, it
save all the setting I applied on the OnLoad event of the form, like
defaults, colors, filter etc. So I assumed that it should work.

Argh! Don't do that, unless you want to run the risk of corrupting your
VB project. No, the corruption doesn't happen all the time -- I've done
it myself on occasion, without thinking -- but there are enough reports
of it happening under those circumstances that I strongly recommend you
never modify code behind a form while the form is open other than in
design view.

I think the reason your setting changes got saved before is that editing
and saving the code forces Access to save design changes to the form.
But normally, Access won't save run-time property changes other than
Filter and Order By, and maybe one or two others I can't think of off
the top of my head.

 

[Rob Oldfield]

Very nice indeed.

Though, using A2K and a value list, had to change

Me.Combo0.AddItem NewData

to

Me.Combo0.RowSource = Me.Combo0.RowSource & ";" & NewData

 

[Brendan Reynolds]

Ah, yes, thanks Rob, I think AddItem was new in Access 2002.