Something's Wrong with this code!!

**Dean Spencer** · Dec 12 '06, 03:35 PM

Re: Something's Wrong with this code!!

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

Hope this helps

Dean

"sara" <saraqpost@yaho o.comwrote in message
news:1165853236 .311571.281430@ j72g2000cwa.goo glegroups.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.PODat e,
tblMerchant.Mer chFirstName, tblVendors.Vend or, tblPOData.Descr iption
FROM (tblPOData LEFT JOIN tblVendors ON tblPOData.Vendo rKey =
tblVendors.Vend orKey) LEFT JOIN tblMerchant ON tblPOData.Merch antKey =
tblMerchant.Mer chantKey WHERE
(((tblMerchant. MerchantKey)=[Forms]![frmSelectPO]![cboMerchKey]));
>
>
Function:
Public Function fcnLoadPOList(l ngKey As Long, strSource As String) As
Boolean
On Error GoTo Err_fcnLoadPOLi st
>
' 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.PODat e, " _
& " tblMerchant.Mer chFirstName, tblVendors.Vend or,
tblPOData.Descr iption " _
& " FROM tblPOData " _
& " LEFT JOIN (tblVendors " _
& " ON tblPOData.Vendo rKey = " & " tblVendors.Vend orKey) "
_
& " LEFT JOIN tblMerchant " _
& " ON tblPOData.Merch antKey = " & "
tblMerchant.Mer chantKey " _
& " WHERE (tblMerchant.Me rchantKey = " & lngKey & ")" _
& " ORDER BY tblPOData.PODat e DESC "
>
Forms!frmSelect PO.lstPOs.RowSo urce = strSQLMerch
Forms!frmSelect PO.lstPOs.Reque ry
>
Else
strSQLPO = "SELECT tblPOData.POKey , tblPOData.PONum , " _
& " tblPOData.PODat e, tblMerchant.Mer chFirstName, " _
& " tblVendors.Vend or, tblPOData.Descr iption " _
& " FROM tblPOData " _
& " LEFT JOIN tblVendors " _
& " ON tblPOData.Vendo rKey = " & "tblVendors.Ven dorKey " _
& " LEFT JOIN tblMerchant " _
& " ON tblPOData.Merch antKey = " &
"tblMerchant.Me rchantKey " _
& " WHERE (tblPOData.POKe y = " & lngKey & ")" _
& " OrderBy tblPOData.PODat e DESC;"
>
Forms!frmSelect PO.lstPOs.RowSo urce = strSQLPO
Forms!frmSelect PO.lstPOs.Reque ry
>
>
End If
>
fcnLoadPOList = True
>

**sara** · Dec 12 '06, 06:05 PM

Re: Something's Wrong with this code!!

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.runcomman d accmdrefresh
>
Hope this helps
>
Dean
>
"sara" <saraqpost@yaho o.comwrote in message
news:1165853236 .311571.281430@ j72g2000cwa.goo glegroups.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.PODat e,
tblMerchant.Mer chFirstName, tblVendors.Vend or, tblPOData.Descr iption
FROM (tblPOData LEFT JOIN tblVendors ON tblPOData.Vendo rKey =
tblVendors.Vend orKey) LEFT JOIN tblMerchant ON tblPOData.Merch antKey =
tblMerchant.Mer chantKey WHERE
(((tblMerchant. MerchantKey)=[Forms]![frmSelectPO]![cboMerchKey]));

Function:
Public Function fcnLoadPOList(l ngKey As Long, strSource As String) As
Boolean
On Error GoTo Err_fcnLoadPOLi st

' 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.PODat e, " _
& " tblMerchant.Mer chFirstName, tblVendors.Vend or,
tblPOData.Descr iption " _
& " FROM tblPOData " _
& " LEFT JOIN (tblVendors " _
& " ON tblPOData.Vendo rKey = " & " tblVendors.Vend orKey) "
_
& " LEFT JOIN tblMerchant " _
& " ON tblPOData.Merch antKey = " & "
tblMerchant.Mer chantKey " _
& " WHERE (tblMerchant.Me rchantKey = " & lngKey & ")" _
& " ORDER BY tblPOData.PODat e DESC "

Forms!frmSelect PO.lstPOs.RowSo urce = strSQLMerch
Forms!frmSelect PO.lstPOs.Reque ry

Else
strSQLPO = "SELECT tblPOData.POKey , tblPOData.PONum , " _
& " tblPOData.PODat e, tblMerchant.Mer chFirstName, " _
& " tblVendors.Vend or, tblPOData.Descr iption " _
& " FROM tblPOData " _
& " LEFT JOIN tblVendors " _
& " ON tblPOData.Vendo rKey = " & "tblVendors.Ven dorKey " _
& " LEFT JOIN tblMerchant " _
& " ON tblPOData.Merch antKey = " &
"tblMerchant.Me rchantKey " _
& " WHERE (tblPOData.POKe y = " & lngKey & ")" _
& " OrderBy tblPOData.PODat e DESC;"

Forms!frmSelect PO.lstPOs.RowSo urce = strSQLPO
Forms!frmSelect PO.lstPOs.Reque ry

End If

fcnLoadPOList = True

**deluxeinformation@gmail.com** · Dec 12 '06, 07:15 PM

Re: Something's Wrong with this code!!

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!frmSelect PO.lstPOs.RowSo urce = strSQL
Forms!frmSelect PO.lstPOs.Reque ry

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

**sara** · Dec 12 '06, 10:55 PM

Re: Something's Wrong with this code!!

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

deluxeinformati on@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!frmSelect PO.lstPOs.RowSo urce = strSQL
Forms!frmSelect PO.lstPOs.Reque ry
>
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

Something's Wrong with this code!!

Something's Wrong with this code!!

Comment

Comment

Comment

Comment