By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Access: Update Listbox from query

P: 2
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?
Nov 30 '06 #1
Share this Question
Share on Google+
2 Replies


sashi
Expert 100+
P: 1,754
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.
Nov 30 '06 #2

P: 2
Expand|Select|Wrap|Line Numbers
  1. Private Sub region_Click()
  2.    Dim db As DAO.Database
  3.    Dim qdf As DAO.QueryDef
  4.    Dim varItem As Variant
  5.    Dim strCriteria As String
  6.    Dim strSQL As String
  7.  
  8.    Set db = CurrentDb()
  9.    Set qdf = db.QueryDefs("qryMultiSelect")
  10.  
  11.    If Me!region.ItemsSelected.Count > 0 Then
  12.       For Each varItem In Me!region.ItemsSelected
  13.          strCriteria = strCriteria & "tbl_country.Region = " & Chr(34) _
  14.                        & Me!region.ItemData(varItem) & Chr(34) & "OR "
  15.       Next varItem
  16.       strCriteria = Left(strCriteria, Len(strCriteria) - 3)
  17.    Else
  18.       strCriteria = "tbl_country.Region Like '*'"
  19.    End If
  20.  
  21.    strSQL = "SELECT Country FROM tbl_country " & _
  22.             "WHERE " & strCriteria & ";"
  23.    qdf.SQL = strSQL
  24.  
  25.    'DoCmd.OpenQuery "qryMultiSelect"
  26.  
  27.    Set db = Nothing
  28.    Set qdf = Nothing
  29.  
  30. ' This line does nothing
  31.     Forms!Search!Country.Requery
  32. End Sub
  33.  
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.
Nov 30 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.