Access: Update Listbox from query

[Chucara]

Hi,

I'm trying to build a simple search in Access. I'll just give a simplified example, as I think I can solve the problem, if you can help me with this subproblem..

I have 2 listboxes - "Region" and "Country". "Region" supports extended multiple select.

I want the Country listbox to populate with all countries in all of the selected regions.

So far, I can do it without multiple select, using requery.

I've created a subroutine that extracts each selected value from the "Region" listbox and creates a new query.

The "Country" listbox is based on this query. But when I do requery, "Country" doesn't seem to load the altered query - it just refreshes the already selected query. This means that the box isn't updated with the new selection, but it is when I go to design view and back again.

How do I get the listbox to populate from the new version of the stored query?

--- Or is there a simpler way to do it?

[sashi]

Hi,

I'm trying to build a simple search in Access. I'll just give a simplified example, as I think I can solve the problem, if you can help me with this subproblem..

I have 2 listboxes - "Region" and "Country". "Region" supports extended multiple select.

I want the Country listbox to populate with all countries in all of the selected regions.

So far, I can do it without multiple select, using requery.

I've created a subroutine that extracts each selected value from the "Region" listbox and creates a new query.

The "Country" listbox is based on this query. But when I do requery, "Country" doesn't seem to load the altered query - it just refreshes the already selected query. This means that the box isn't updated with the new selection, but it is when I go to design view and back again.

How do I get the listbox to populate from the new version of the stored query?

--- Or is there a simpler way to do it?

Hi there,

You seems to have a great theory here, have you started initial programming task regarding the above mentioned issue? Please post your code segment for further validation / debuging. Good luck & Take care.

[Chucara]

Private Sub region_Click()
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim varItem As Variant
   Dim strCriteria As String
   Dim strSQL As String
   
   Set db = CurrentDb()
   Set qdf = db.QueryDefs("qryMultiSelect")
   
   If Me!region.ItemsSelected.Count > 0 Then
      For Each varItem In Me!region.ItemsSelected
         strCriteria = strCriteria & "tbl_country.Region = " & Chr(34) _
                       & Me!region.ItemData(varItem) & Chr(34) & "OR "
      Next varItem
      strCriteria = Left(strCriteria, Len(strCriteria) - 3)
   Else
      strCriteria = "tbl_country.Region Like '*'"
   End If
   
   strSQL = "SELECT Country FROM tbl_country " & _
            "WHERE " & strCriteria & ";"
   qdf.SQL = strSQL
   
   'DoCmd.OpenQuery "qryMultiSelect"
   
   Set db = Nothing
   Set qdf = Nothing

' This line does nothing
    Forms!Search!Country.Requery
End Sub

Above code is from the "region" listbox. This produces the correct query. The problem is that the final line does not update the "country" listbox with the next query..

Perhaps an example is in order:

There form has two listboxes. If I in the first listbox (region" select "Europe" and "North Africa"), the second listbox (country) should display "Algeria, Denmark, Germany, Libya etc" -- all countries in the selected regions.