Solved list box selection to show results in another listbox (1 Viewer)

[ahmad_rmh]

I am trying to show results in a list box as per the selection in the list box.

for example:

List Box 1:
Current Stock
Received Stock
Transferred Stock

List Box 2:

List box 2 should have to show results as per the selection.

I have attached the screenshots.

How to get the solution.

Private Sub btnInventoryManagement_Click()
    Me.DynamicList.RowSource = "Select * from MasterTables where MasterTablesPK>3;"
    Me.DynamicList.ColumnCount = 3
    Me.DynamicList.ColumnHeads = False
    Me.DynamicList.ColumnWidths = "0;3.1042cm;0"
End Sub

Private Sub DynamicList_Click()
    If Me.DynamicList.RowSource = "Select * from MasterTables where MasterTablesPK>3;" Then
    Me.DynamicList1.RowSource = "SELECT ItemsPK, ItemsListName, CurrentStock, Nz([Opening Balance],0) AS Opening, Nz([NetPurchases],0) AS Purchases, Nz([NetTransfers],0) AS Transfers, Nz([MonthlyAdjustments],0) AS Adjustments, Round(Nz([NetTransfers],0)/Day(Date())*-1,2) AS AvgUsage FROM qryStockSummary;"
    Me.DynamicList1.ColumnCount = 8
    Me.DynamicList1.ColumnHeads = True
    Me.DynamicList1.ColumnWidths = "0;8cm;3cm;3cm;3cm;3cm;3cm;3cm"
    End If
End Sub

The problem is dynamiclist rowsource is not working if i am trying as under

"Select * from MasterTables where MasterTablesPK=4;"

kindly suggest the solution.

 

[The_Doc_Man]

If you look up "Cascading Combo Boxes" in this forum I think you will find many threads that might help you. That is, if I understood your question, that might help. It is a commonly asked question. The SEARCH button is in the upper right.

 

[Pat Hartman]

The only code you actually need to handle a two-level cascading combo is a requery of combo2. This technique is identical for both listboxes and combos.

In the click event of combo1, use the following code:

Me.cbo2.Requery

This works because you use a query as the RowSource for combo2 that references the current value of combo1
Select .. From ... Where SomeID = Forms!yourform!combo1

If you have additional levels, "cascade" the technique. Combo3's query refers to Combo2, Combo4's query refers to combo3, etc. At each level, you need to requery or clear all lower levels.

 

[ahmad_rmh]

It works with < or > operator but not not showing results with equal sign operator.

 

[MajP]

Any chance you mistakenly made MasterTablePK a shortText and not an integer? Common issue since short text is default.

 

[ahmad_rmh]

No, It's autonumber primary key as an integer.

 

[MajP]

Are you saying?

"Select * from MasterTables where MasterTablesPK>3;"

Works
But

"Select * from MasterTables where MasterTablesPK = 3;"

Does not work

Without seeing your code can only guess. But clearly you are making a mistake we cannot see. If MasterTablesPK is numeric it will work, so there is something going on you are not showing us.

 

[Pat Hartman]

The likely mistake is that you are incorrectly using a table level lookup. In that case, you would start by going to the table definition and REMOVING any lookup. Change the property to a text box and remove all the other lookup properties if any remain. That leaves the field as the pure ID and there is no way to confuse it with some text value.

 

[ahmad_rmh]

I have got the mistake,

I was using .rowsource in place of .listindex,

I have resolved the error afer reading the articles as under;

ListIndex:

ListBox.ListIndex property (Access)

Office VBA reference topic

learn.microsoft.com

Rowsource:

ListBox.RowSourceType property (Access)

Office VBA reference topic

learn.microsoft.com

thanks

 

[Pat Hartman]

You seem to have made this harder than it needed to be. But, as long as you're happy, we're happy. Maybe your problem was that you were using a multi-value field and that is why you couldn't cascade normally.