Nanette said:
Steve,
You understand what I need. I'm curious why the list box for the Line Item
should have a hidden column for the Charge # and a concatenated column also?
How would I create a concatenated column in a list box?
To limit the "Line Item" records based on the "Charge#"s selected. Hidded
because I was thinking of combo box instead of List box... <slap>. To create
a concatenated column (LB or Combo), in the rowsource query, you would write
something like:
ChargeItem: [Charge#] & "-" & [LineItem]
As you said in the other post, you could use a single list box to show both
the chage # and the associated line item. This way, you *will* have to
generate the recordsource SQL "on-the-fly" (in code).
Plus, how would I create a record source for the form/report that works "on
the fly"?
Could you also explain about the code for the button to loop thru the Items
selected in the 2nd list box and create the above filter/ where statement?
On the main form you have a combo box to select the RFQ# and list box to
select the Charge# and Line Item.
Lets call the combo box "cboRFQ" and the list box "ChargeItemList". And
lets call the button "ShowMe".
It sounds like there is a subform that has the results of the query. The
subform would have a recordsource (a query) that would look something like
(to start out with):
SELECT [RFQ#], [Charge#], [LineItem] FROM RF_Details WHERE [RFQ#] = &
Forms![MyRFQForm.cboRFQ
At this point, the subform would (should) have the same rows as the listbox.
But a query cannot have multiple criteria unless you want to edit the query
each time you want to use different criteria (charge# and line items). So you
have to generate a SQL string in code....but let the computer do the work.
Now some code for the button. To start out, the code would be:
Private Sub ShowMe_Click()
Dim strSQL As String
strSQL = "SELECT [RFQ#], [Charge#], [LineItem] FROM RF_Details WHERE
[RFQ#] = & Forms![MyRFQForm.cboRFQ"
End Sub
Not very useful yet. So let's add more criteria from the list box. To get
the Charge# and the LineItem we need to know which columns in the list box
that they are in.
Here is an example of the code you might use to set the recordsource of a
subform (that is not linked to the main form).
*** some air code
***watch for line wrap
'---------beg code--------------------------
Private Sub ShowMe_Click()
Dim strSQL As String
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim intI As Integer
Dim k As Integer
Dim x As Integer
' make changes here
'-----------------------------------------------
' use your form name
Set frm = Forms!MyRFQForm
' use your listbox name
Set ctl = frm!ChargeItems_listbox
strSQL = "SELECT [RFQ#], [Charge#], [LineItem] FROM RF_Details WHERE
[RFQ#] = '" & frm.cboRFQ & "' "
'-----------------------------------------------
'k = number of rows selected in list box
k = ctl.ItemsSelected.Count
'x = count of number of times thru the FOR EACH...NEXT loop
x = 1
If k > 0 Then
'add opening paren
strSQL = strSQL & " AND ("
For Each varItm In ctl.ItemsSelected
'change the the 0 and 1 in the following line to the column numbers
' for Charge# and LineItem in yourlistbox
strSQL = strSQL & "([Charge#] = '" & ctl.Column(0, varItm) & "'
AND [LineItem] = '" & ctl.Column(1, varItm) & "' "
If x < k Then
strSQL = strSQL & ") OR "
End If
'add closing parens
If x = k Then
strSQL = strSQL & "))"
End If
'inc x
x = x + 1
Next varItm
End If
'-----------------------------------------------
' this is where you would set the subform recordsource = to strSQL
'something like:
' Me.SubFormName.recordsource = strSQL
'using your object names of course
'-----------------------------------------------
Debug.Print strSQL
End Sub
'----------end code-----------------
I'm a newbie. But, I'm taking a course in VB that starts tonight (don't
remember my VB college work from 3 years ago). I'm assuming VB and VBA are
fairly similar.
Yes, I think VBA is a sub-set of VB.
HTH
