Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    6

    Unanswered: cascading multi-select list boxes

    Hi

    I am trying to change the Price of an item in my access 97 database based on the selections made by the user in the form.

    I have the following controls on my form
    1.combobox for selecting "Division"
    2.Text Box for entering the item No
    3. multi-select listbox for "VolumeClass" ( this will be populated dynamically based on the combo box and item selection)
    4. Multi-select listbox for "markets (this should be populated dynamically too based on all the other three selections)

    I used a parameterised query(selectMarkets) for populating the Markets listbox. I actually have a hidden text box to store the selections of VolumeClass. wrote a function to capture all the selections made in VC and assigned it to the textbox.the value in text box looks like this 1,15,2

    my selectMarkets looks as below:
    ------------------------------------
    SELECT tblMarkets.MarketName, tblmarkets.MarketId
    FROM tblMarkets
    WHERE tblMarkets.MarketId IN (select MarketId from tblShells
    WHERE tblShells.Div = [Forms]![PricingSystem]![cmbDiv]
    AND tblShells.itemNo = [Forms]![PricingSystem]![txtItem]
    AND tblShells.VC IN ([Forms]![PricingSystem]![txtVC]));

    ------------------------------------

    I am doing Me.lstMarlets.requery on the after-update event of the lstVolumeClass.

    it works when user selects 1 item from the volumeClass list box. However I see nothing when user makes multiple selection . I ran the same query in sql view ....it shows all the related markets for the selections user made. what am I doing wrong here. Any help is really appreciated.

    Thanks
    Jyothsna.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I don't quite understand and if you don't get a response (maybe someone else understands better), you might try uploading a sample.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2006
    Posts
    6
    Let me put it in a better way.

    I have two list boxes. Both of them are multi select.
    Based on the values selected in the first list box a select query is run and the results showld be shown in the second list box.

    How do i get this.

    Thanks,
    Jyothsna.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Question: if you were to substitute the

    tblShells.VC IN ([Forms]![PricingSystem]![txtVC]));

    for

    tblShells.VC IN (1,15,2));

    Does it work in the QBE? In other words, verify your consolidation function works properly ... 'Nother question: is your itemno numeric? If not you have an error in your query there cause you're not treating it as a string ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Oct 2006
    Posts
    6
    Owen,

    As I said it works just fine in QBE when i replaced
    tblShells.VC IN ([Forms]![PricingSystem]![txtVC]));

    for

    tblShells.VC IN (1,15,2));

    Also all the markets pertaining to one VolumeClass(VC) are being shown in the Markets listbox. The problem occurs only when the user selects multiple VolumeClasses....in this case the Markets list box is empty where as when I check for the same multiple VC's in the QBE the pertaining markets are being shown

    FYI my itemNo is numeric.....can you see anything that I am doing wrong.

    Thanks,
    Jyothsna

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Jyothsna
    Owen,

    As I said it works just fine in QBE when i replaced
    tblShells.VC IN ([Forms]![PricingSystem]![txtVC]));

    for

    tblShells.VC IN (1,15,2));

    Also all the markets pertaining to one VolumeClass(VC) are being shown in the Markets listbox. The problem occurs only when the user selects multiple VolumeClasses....in this case the Markets list box is empty where as when I check for the same multiple VC's in the QBE the pertaining markets are being shown

    FYI my itemNo is numeric.....can you see anything that I am doing wrong.

    Thanks,
    Jyothsna
    Again, you didn't state that you verified that your routine that picks the VC items from the list box is correct ... Obviously now, the SQL is ok. Go back to where your next failure point is: That function.

    Why don't you post it here ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Oct 2006
    Posts
    6
    Quote Originally Posted by M Owen
    Again, you didn't state that you verified that your routine that picks the VC items from the list box is correct ... Obviously now, the SQL is ok. Go back to where your next failure point is: That function.

    Why don't you post it here ...

    Here is the Function.....

    Function selectVCs() As String
    Dim frm As Form, ctl As Control
    Dim varItem As Variant
    Dim strSQL As String

    Set frm = Forms!PricingSystem
    Set ctl = frm!lstVC
    strSQL = ""

    'enumerate selected items and
    'concatenate to strSQL
    For Each varItem In ctl.ItemsSelected
    strSQL = strSQL & ctl.ItemData(varItem) & ","
    Next varItem

    'Trim the end of strSQL
    strSQL = Left(strSQL, Len(strSQL) - 1)

    selectVCs = strSQL
    End Function

    The user clicks a ok button after he selects the VolumeClasses

    on Click event of the Ok button is as follows

    Private Sub cmdOK_Click()
    On Error GoTo Err_cmdOK_Click
    txtVC.Value = selectVCs
    If Me.lstMarket.Enabled = False Then
    Me.lstMarket.Enabled = True
    End If
    Me.lstMarket.Requery
    Exit_cmdOK_Click:
    Exit Sub
    Err_cmdOK_Click:
    MsgBox Err.Description
    Resume Exit_cmdOK_Click
    End Sub

    the rowsource of the Markets list box is the following query

    SELECT tblMarkets.MarketName, tblmarkets.MarketId
    FROM tblMarkets
    WHERE tblMarkets.MarketId IN (select MarketId from tblShells
    WHERE tblShells.Div = [Forms]![PricingSystem]![cmbDiv]
    AND tblShells.itemNo = [Forms]![PricingSystem]![txtItem]
    AND tblShells.VC IN ([Forms]![PricingSystem]![txtVC]));

    Thanks again,
    Jyothsna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •