Listbox Itemdata editable at runtime?

[yanguo]

I have a listbox with data populated from a SQL Server 2000 database table. I
have a subform that works like editbox for the record selected on the
listbox. I am trying to make the listbox values be updated when data is
changed and the record is updated from subform. I am doing this in the
subform's afterupdate event:

Private Sub Form_AfterUpdate()
Me.Parent.lstORServiceLevelApprovals.ItemData(6, lIndex) = fieldvalue
End Sub

Here the lIndex is presaved listindex of the listbox in mainform and the
fieldvalue is value of the bonded field value on subform same as column 6 of
the listbox on mainform. When it runs to this line I got a rumtime error
"450" - Wrong number of argumens or invalid property assignment. I am sure
that the indexes are correct. So I am now suspecting if ItemData for
listboxes are editable.

Any comments? Thanks!

 

[yanguo]

Just found from Access help file:

The ItemData property returns the data in the bound column for the specified
row in a combo box or list box. Read-only Variant.

So I need try other ways to refresh listbox.

Thanks!

 

[Ron2006]

Just found from Access help file:

The ItemData property returns the data in the bound column for the specified
row in a combo box or list box. Read-only Variant.

So I need try other ways to refresh listbox.

Thanks!








- Show quoted text -

do a requery on the listbox.

 

[yanguo]

That was my 1st try but it didn't work. The only way I found so far is to
reload the recordset and assign it to the listbox everytime I want to refresh
the listbox. Though it could be time-comsuming sometimes but at least it
works.

Thanks Ron!

 

[Kou Vang]

in form_current()

If Not Me.NewRecord Then
lstbox.requery
end if

 

[Ron2006]

in form_current()

If Not Me.NewRecord Then
lstbox.requery
end if







- Show quoted text -

Are you populating a list that the listbox is then displaying or is
the rowsource for the listbox simply a query of the underlying table.
Anther way of asking the question is: Does the Rowsource of the
listbox say "Table/query" or does it say "Value List"?

If it says value list then the only way is to reload the data into
rowsource.

If it says Table/query then it should be reset when you do the requery
of the listbox.

Ron

 

[yanguo]

My list is type "Table/View/StoredProc" and the rowsource is a select
statement. I have the listbox poulated based on selecetion of a few other
comboboxes on the main form. I don't know if that's the reason or if I used
wrong recordset setting.

Private Sub LoadListBox()
Dim rs As New ADODB.Recordset
Dim stsql as string

Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorLocation = adUseClient

stsql = "SELECT statement"

rs.Open stsql, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

Set mylist.Recordset = rs
End Sub

Private Sub MySubform_AfterUpdate()
me.parent.mylist.requery
End Sub

it never refreshes unless I do this:

Private Sub MySubForm_AfterUpdate()
me.parent.LoadListBox()
End Sub

 

[Ron2006]

My list is type "Table/View/StoredProc" and the rowsource is a select
statement. I have the listbox poulated based on selecetion of a few other
comboboxes on the main form. I don't know if that's the reason or if I used
wrong recordset setting.

Private Sub LoadListBox()
Dim rs As New ADODB.Recordset
Dim stsql as string

Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorLocation = adUseClient

stsql = "SELECT statement"

rs.Open stsql, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

Set mylist.Recordset = rs
End Sub

Private Sub MySubform_AfterUpdate()
me.parent.mylist.requery
End Sub

it never refreshes unless I do this:

Private Sub MySubForm_AfterUpdate()
me.parent.LoadListBox()
End Sub









- Show quoted text -

You definitely have it set up differently than I normally set up
combobox sources.

It seems to me that although you have the type and rowsource set up as
I would you are then doing something I have never done with the code
in the LoadListBox sub routine.

Have you tried NOT executing that code at all and just letting the
default settings work. It seems to me that by doing the loadlistbox
routine you are changeing the record source to something other that
what was originally there.

Try commenting out the code in the load listbox and just trying the
requery method.

But since you do have a method that works this may be a mute point,
but I would be interested in why myself.

Ron

 

[yanguo]

Yes, Ron. I just did a quick try by commenting out the query loading in my
code. The listbox realy refreshes by itself. This is very interesting.....

 

[Ron2006]

Yes, Ron. I just did a quick try by commenting out the query loading in my
code. The listbox realy refreshes by itself. This is very interesting.....











- Show quoted text -

combobox and listbox queries work fine just by defining them in the
form.

The times that they need to be refreshed include but may not be
entirely limited to:
1) change of criteria that may occur while on that record (cascading
comboboxes)
2) addition or deletion of records in the underlying table if done
progromatically while sitting on that record.

Ron