List box problem

[frank]

I have a multi select list box that displays four columns.
The record source for the listbox is a query that
concatinates two fields in column two. I also have a
function on the same form that will iterate through the
list box control and create a string of the selected items
in column two.

The problem is that when I attempt to run a delete query
followed by the function call, the list box is showing no
value in column two and the function is not returning any
values except for "/". I cant figure it out because it
works if I just use the function call but if I attempt any
sql statment the value in column two of the list box is
somehow being reset to nothing. The delete query has
nothing to do with the record source of the list box and I
am not changing the recordsouce for the list box. Any
sugestions? I've tried repair. That didn't work.

Thanks in advance.

Here is the code i'm using:

Private Sub sInsertNewDescription()
'Remove existing numbers
strSQL = "DELETE FROM tbl_MediaInternalNumber " & _
"WHERE MediaID=" & Me.txtMediaID
DoCmd.RunSQL strSQL

'Create a single string of items selected
strDesc = fCreateInternalNumDesc()

Debug.print strDesc

End Sub

Private Function fCreateInternalNumDesc() As String
Dim varItm As Variant
Dim strInternalDesc As String
Dim ctl As Control


Set ctl = Me.lstInternalNums

For Each varItm In ctl.ItemsSelected
strInternalDesc = strInternalDesc & "/" &
ctl.Column(1, varItm)
Next varItm

fCreateInternalNumDesc = Right(strInternalDesc, Len
(strInternalDesc) - 1)

End Function

 

[Ken Snell]

For clarification -- A list box has a control source and a row source, but
not a recordsource. I believe your post refers to the row source.

I admit that I don't understand the logic of deleting records from a table
and then building a string from a list box that isn't affected by that
delete query, but...

Is the listbox bound to a field in the form's recordsource? is the form
bound to the table tbl_MediaInternalNumber or to a query that is based on
this table? have you tried debug mode to verify that the listbox's
ItemsSelected property actually has any values? What is the row source query
for the listbox (are you positive that it isn't affected?)?

We need more information here I think...

 

[John Vinson]

The problem is that when I attempt to run a delete query
followed by the function call, the list box is showing no
value in column two and the function is not returning any
values except for "/".

What is the Bound Column of the listbox? It need not be 1, nor need it
be visible - it should be whichever column in the Listbox's Rowsource
query contains the unique ID of the record to be deleted.