Something's Wrong with this code!!

Quote:

Quote:

I have a form that has 2 types of selection boxes at the top (Number or
Name) and a listbox on the bottom to show the results.

If the user chooses a single PO#, just that PO will (Should!) show in
the list box below. If they choose a Name, then all the POs for that
person should show in the list box.

I planned to call a function, passing the "type" as text (name or
number) and the "key" (one PO# or the table key associated with the
name), and build the Where clause and the SQL string for the list box
accordingly.

It's not working. If I put the code directly in the form in the
RowSource for the ListBox, I can get data in the list box. But (what
looks to me to be) the same code as code doesn't work.

When I step through the code, I see the variables are being passed
correctly; just nothing shows up in the list box. I've tried
adding/removing parenthesis, nothing works.

Thanks. I really thought I'd be able to do this....
Sara

(code below):

In the form - RowSource for the listbox:

SELECT tblPOData.POKey, tblPOData.PONum, tblPOData.PODate,
tblMerchant.MerchFirstName, tblVendors.Vendor, tblPOData.Description
FROM (tblPOData LEFT JOIN tblVendors ON tblPOData.VendorKey =
tblVendors.VendorKey) LEFT JOIN tblMerchant ON tblPOData.MerchantKey =
tblMerchant.MerchantKey WHERE
(((tblMerchant.MerchantKey)=[Forms]![frmSelectPO]![cboMerchKey]));


Function:
Public Function fcnLoadPOList(lngKey As Long, strSource As String) As
Boolean
On Error GoTo Err_fcnLoadPOList

' Load the PO list with either the PO number from the combo box
' or all the POs for the Merchant from the combo box (in desc
POdate order)

Dim strSQLPO As String
Dim strSQLMerch As String

fcnLoadPOList = False

If strSource = "Merch" Then
strSQLMerch = "SELECT tblPOData.POKey, tblPOData.PONum,
tblPOData.PODate, " _
& " tblMerchant.MerchFirstName, tblVendors.Vendor,
tblPOData.Description " _
& " FROM tblPOData " _
& " LEFT JOIN (tblVendors " _
& " ON tblPOData.VendorKey = " & " tblVendors.VendorKey) "
_
& " LEFT JOIN tblMerchant " _
& " ON tblPOData.MerchantKey = " & "
tblMerchant.MerchantKey " _
& " WHERE (tblMerchant.MerchantKey = " & lngKey & ")" _
& " ORDER BY tblPOData.PODate DESC "

Forms!frmSelectPO.lstPOs.RowSource = strSQLMerch
Forms!frmSelectPO.lstPOs.Requery

Else
strSQLPO = "SELECT tblPOData.POKey, tblPOData.PONum, " _
& " tblPOData.PODate, tblMerchant.MerchFirstName, " _
& " tblVendors.Vendor, tblPOData.Description " _
& " FROM tblPOData " _
& " LEFT JOIN tblVendors " _
& " ON tblPOData.VendorKey = " & "tblVendors.VendorKey " _
& " LEFT JOIN tblMerchant " _
& " ON tblPOData.MerchantKey = " &
"tblMerchant.MerchantKey " _
& " WHERE (tblPOData.POKey = " & lngKey & ")" _
& " OrderBy tblPOData.PODate DESC;"

Forms!frmSelectPO.lstPOs.RowSource = strSQLPO
Forms!frmSelectPO.lstPOs.Requery


End If

fcnLoadPOList = True

Quote: