Solved list box selection to show results in another listbox

[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.

 

[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.

 

[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