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.