Multible Pop Up Windows (Lists)

[Carl71227]

I need to scroll through a list of Item Numbers and have one or more (4) lists pop up if any one or more of the lists contain an on hand quantity greater than zero. This needs to occur as the user highlights the item number in the primary list either by scrolling through the list or selecting with the mouse. The one or more Pop Up windows is required because the item can be in stock at more than one location. Is this possible with access or should I look elsewhere?

 

[JoeMiller]

Umm.. let me be a user for a sec. I'm working on a form, and I'm looking for part XYZ. I see part XYZ in the list and it has a quantity of 4 (1 in each location I assume?) so I continue to highlight it. Now 4 popup boxes will jump up and show me the quantity in each location? is that the case? Why not just make the listbox have a column for each location and show the qty of each? Or how about a subform that for each location shows the selected items in the list? So when I click on items, I see the item listed in the subform with it's quantity. Access can do what your proposing, I just don't see why you want to pop up a window that the user then has to clear away when you could just show them on the same form. If my inventory program did that I'd throw it out the window when dealing with the 30 locations we have!

Joe Miller
joe.miller@flotech.net

 

[raskew]

Agree 100% with Joe's assessment. If I were the user, I'd want to kill you because you're causing me to step through a bunch of unnecessary steps in order to get to where I need to go. This might not seem like a big deal, but if you've got 200 documents that you must process, those extra steps will kill you, time-wise.

Try approaching this as a case of cascading combo boxes, with the contents of the second combo box limited by the selection made in the first combo box. Here's an example, using an unbound form with two combo boxes (cbo1 and cbo2)

Using Northwind's Order Details table, I wanted to know which orders (OrderID) included a particular product. My first combo box (cbo1) would use this as the RecordSource

'***** (SQL follows)
SELECT DISTINCT Products.ProductID, Products.ProductName
FROM Products
ORDER BY Products.ProductName;
'***** (SQL ends)

With
bound column: 1
Column count: 2
Column widths: 0";2"
List Width: 2"

Cbo1's AfterUpdate event would be:

'***** (code follows)
Private Sub cbo1_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT ProductID, OrderID, Quantity FROM [Order Details] WHERE "
strSQL = strSQL & "ProductID = " & Me![cbo1]
strSQL = strSQL & " ORDER BY OrderID;"

With Me!cbo2
.SetFocus
.RowSource = strSQL
.Requery
.Dropdown
End With

End Sub
'***** (code ends)

cbo2 would be setup as follows:

bound column: 1
Column count: 3
Column widths: 0";0.5"; 0.5"
List Width: 1"
Column Heads: Yes

Now, all that remains is to write the code for the AfterUpdate event of cbo2, to do whatever it is you need to do.