updating a listbox

[Guest]

I'm having trouble with a pair of multilist boxes. The first (NameListBox)
provides parameters for a query that updates on click. The second multilist
box (List136) is populated by the results of the query.

My problem is that the second list box does not refresh when I requery with
additional parameters. I have been able to accomplish this by giving the
second list box a GotFocus Refresh command, but I would rather have it
automatically refresh each time the query is run - it's just too clunky
without an automatic refresh. I'm including the two sub routines.

I'm sure that there's a simple way to do this, but I'm going to need help!

Thanks!


Private Sub List136_GotFocus()
Me.Refresh
End Sub

Private Sub NameListBox_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT DISTINCT Records.Project FROM Records "
strWhere = "Where Tech IN( "
For i = 0 To NameListbox.ListCount - 1
If NameListbox.Selected(i) Then
strWhere = strWhere & "'" & NameListbox.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
'MsgBox strSQL

'*** delete the previous query
db.QueryDefs.Delete "ProjectExtractionQuery"
Set qdf = db.CreateQueryDef("ProjectExtractionQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "ProjectExtractionQuery", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

 

[Guest]

You are going about this the hard way. Rather than building and running
query which really does nothing since it is only the row source for the list
box, manipulate the list box's row source property:

strSQL = "SELECT DISTINCT Records.Project FROM Records "
strWhere = "Where Tech IN( "
For i = 0 To NameListbox.ListCount - 1
If NameListbox.Selected(i) Then
strWhere = strWhere & "'" & NameListbox.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
Me.List136.RowSource = strSQL & strWhere

No need to refresh, changing the rowsource forces a requery

 

[Guest]

This looks great - the only problem is that the list box row source property
forces me to use a Table/Query, a Value list or a Field list. Is there
another way to place the code in the list box other than the properties box?