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

When you have populated the try refreshing the records, i.e.
docmd.runcommand accmdrefresh

Hope this helps

Dean

"sara" <saraqpost@yahoo.comwrote in message
news:1165853236.311571.281430@j72g2000cwa.googlegr oups.com...

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

This is not working. I am getting "The command or action Refresh isn't
available now"

Any other ideas? This is killing me!

Sara


Dean Spencer wrote:

When you have populated the try refreshing the records, i.e.
docmd.runcommand accmdrefresh
>
Hope this helps
>
Dean
>
"sara" <saraqpost@yahoo.comwrote in message
news:1165853236.311571.281430@j72g2000cwa.googlegr oups.com...

First, I wouldn't set up two different strSQL variables. I'd set up
something like the following:

Dim strSQL As String

fcnLoadPOList = False

If strSource = "Merch" Then
strSQL = "SELECT tblPOData.POKey, tblPOData.PONum,
blah blah blah
Else
strSQL = "SELECT tblPOData.POKey, tblPOData.PONum, " _
blah blah blah
End If

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

End If

which is a little simpler and easier to maintain. That being said, I'd
put a debug.print line after my first End If as follows:

debug.print strSQL

Then I would
1. run the form
2 copy whatever gets printed out by the debug.print statement (press
ctrl G first if you don't see the debug window)
3. open a new query window in SQL view
4. paste what you copied in step 1 into the new query window and run
it.

I suspect that when you perform step 4 you'll find a syntax error or
some such that Access didn't tell you about.

Bruce

Thank you, Thank you, Thank you!!!!

I carefully followed your steps (starting with neatening up the code,
thank you), and found a join error, then a syntax error when trying to
fix the join.

Bottom line - I started over, with the Debug.Print and voila! I can
now move on to the next piece of development.

Many thanks.

Sara

deluxeinformation@gmail.com wrote:

First, I wouldn't set up two different strSQL variables. I'd set up
something like the following:
>
Dim strSQL As String
>
fcnLoadPOList = False
>
If strSource = "Merch" Then
strSQL = "SELECT tblPOData.POKey, tblPOData.PONum,
blah blah blah
Else
strSQL = "SELECT tblPOData.POKey, tblPOData.PONum, " _
blah blah blah
End If
>
Forms!frmSelectPO.lstPOs.RowSource = strSQL
Forms!frmSelectPO.lstPOs.Requery
>
End If
>
which is a little simpler and easier to maintain. That being said, I'd
put a debug.print line after my first End If as follows:
>
debug.print strSQL
>
Then I would
1. run the form
2 copy whatever gets printed out by the debug.print statement (press
ctrl G first if you don't see the debug window)
3. open a new query window in SQL view
4. paste what you copied in step 1 into the new query window and run
it.
>
I suspect that when you perform step 4 you'll find a syntax error or
some such that Access didn't tell you about.
>
Bruce