|
|
Nothing is showing in List 13 on the click event...any ideas?
Private Sub List11_Click()
List13.RowSource = "SELECT DateDep, Account, AccountName,
Format(SUM(Amount),'currency')" & _
"FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
"GROUP BY Month(DateDep)" & _
"ORDER BY DateDep DESC;"
End Sub
|
|
Hi Gator,
Try adding a List13.Requery after setting the row source.
Clifford Bass
"Gator" wrote:
[Quoted Text] > Nothing is showing in List 13 on the click event...any ideas?
>
> Private Sub List11_Click()
> List13.RowSource = "SELECT DateDep, Account, AccountName,
> Format(SUM(Amount),'currency')" & _
> "FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
> "GROUP BY Month(DateDep)" & _
> "ORDER BY DateDep DESC;"
> End Sub
>
|
|
"Gator" <Gator[ at ]discussions.microsoft.com> wrote in message
news:D4C6BCE7-B6C1-417C-9EE8-CF4C246CC44C[ at ]microsoft.com...
[Quoted Text] > Nothing is showing in List 13 on the click event...any ideas?
>
> Private Sub List11_Click()
> List13.RowSource = "SELECT DateDep, Account, AccountName,
> Format(SUM(Amount),'currency')" & _
> "FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
> "GROUP BY Month(DateDep)" & _
> "ORDER BY DateDep DESC;"
> End Sub
I don't see anything obvious, but here are some ideas:
Is List11 a multiselect list box, by any chance? If it is, it will have a
value of Null and you won't get any records returned.
Is Fund a text field? The code assumes that it is. If it's a number field,
the quotes around the value from List11 would be wrong.
The only problem I see in the actual SQL is that you have no space before
the FROM keyword. However, I think that would still be parse properly
because of the preceding parenthesis.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
still the same...
"Clifford Bass" wrote:
[Quoted Text] > Hi Gator,
>
> Try adding a List13.Requery after setting the row source.
>
> Clifford Bass
>
> "Gator" wrote:
>
> > Nothing is showing in List 13 on the click event...any ideas?
> >
> > Private Sub List11_Click()
> > List13.RowSource = "SELECT DateDep, Account, AccountName,
> > Format(SUM(Amount),'currency')" & _
> > "FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
> > "GROUP BY Month(DateDep)" & _
> > "ORDER BY DateDep DESC;"
> > End Sub
> >
|
|
multiselect - No
Fund in List11 is 'Text' field in Funds Table.
still the same....
thanks
"Dirk Goldgar" wrote:
[Quoted Text] > "Gator" <Gator[ at ]discussions.microsoft.com> wrote in message
> news:D4C6BCE7-B6C1-417C-9EE8-CF4C246CC44C[ at ]microsoft.com...
> > Nothing is showing in List 13 on the click event...any ideas?
> >
> > Private Sub List11_Click()
> > List13.RowSource = "SELECT DateDep, Account, AccountName,
> > Format(SUM(Amount),'currency')" & _
> > "FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
> > "GROUP BY Month(DateDep)" & _
> > "ORDER BY DateDep DESC;"
> > End Sub
>
>
> I don't see anything obvious, but here are some ideas:
>
> Is List11 a multiselect list box, by any chance? If it is, it will have a
> value of Null and you won't get any records returned.
>
> Is Fund a text field? The code assumes that it is. If it's a number field,
> the quotes around the value from List11 would be wrong.
>
> The only problem I see in the actual SQL is that you have no space before
> the FROM keyword. However, I think that would still be parse properly
> because of the preceding parenthesis.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
"Gator" <Gator[ at ]discussions.microsoft.com> wrote in message
news:96F983D5-ABE7-4C0E-9E7E-9B3989D20EA6[ at ]microsoft.com...
[Quoted Text] > multiselect - No
> Fund in List11 is 'Text' field in Funds Table.
>
> still the same....
You tried correcting the minor SQL error I pointed out, and that didn't
help?
The next thing to do is add a line
Debug.Print List13.RowSource
after you set it the rowsource, then examine the rowsource query as it is
printed in the Immediate window. Does it look correct? If it does, try
copying it and pasting it into the SQL view of a new query, and see if that
query returns any rows.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
The (" & _) is actually in the code to break lines...which I don't think is
having any affect.
I tried adding a space before the FROM like this....
" FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _ .........instead
of.......
"FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
The form is in a Tab control.
The Debug.Print is in the code like below......and I'm not sure how it is
supposed to work..............
Private Sub List11_Click()
List13.RowSource = "SELECT DateDep, Account, AccountName,
Format(SUM(Amount),'currency')" & _
" FROM Deposits" & _
" WHERE ((Fund)='" & List11 & "'))" & _
" GROUP BY Month(DateDep)" & _
" ORDER BY DateDep DESC;"
List13.Requery
Debug.Print List13.RowSource
End Sub
thanks
"Dirk Goldgar" wrote:
[Quoted Text] > "Gator" <Gator[ at ]discussions.microsoft.com> wrote in message
> news:96F983D5-ABE7-4C0E-9E7E-9B3989D20EA6[ at ]microsoft.com...
> > multiselect - No
> > Fund in List11 is 'Text' field in Funds Table.
> >
> > still the same....
>
> You tried correcting the minor SQL error I pointed out, and that didn't
> help?
>
> The next thing to do is add a line
>
> Debug.Print List13.RowSource
>
> after you set it the rowsource, then examine the rowsource query as it is
> printed in the Immediate window. Does it look correct? If it does, try
> copying it and pasting it into the SQL view of a new query, and see if that
> query returns any rows.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
No, putting a space before FROM did not help....
"Dirk Goldgar" wrote:
[Quoted Text] > "Gator" <Gator[ at ]discussions.microsoft.com> wrote in message
> news:96F983D5-ABE7-4C0E-9E7E-9B3989D20EA6[ at ]microsoft.com...
> > multiselect - No
> > Fund in List11 is 'Text' field in Funds Table.
> >
> > still the same....
>
> You tried correcting the minor SQL error I pointed out, and that didn't
> help?
>
> The next thing to do is add a line
>
> Debug.Print List13.RowSource
>
> after you set it the rowsource, then examine the rowsource query as it is
> printed in the Immediate window. Does it look correct? If it does, try
> copying it and pasting it into the SQL view of a new query, and see if that
> query returns any rows.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
"Gator" <Gator[ at ]discussions.microsoft.com> wrote in message
news:2A3D1FB6-CAA8-46D6-A800-40C02A2A6D65[ at ]microsoft.com...
[Quoted Text] > The (" & _) is actually in the code to break lines...which I don't think
> is
> having any affect.
I didn't say anything about the line continuations. I know what those are
for.
> I tried adding a space before the FROM like this....
> " FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _ .........instead
> of.......
> "FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
That should take care of that minor issue, which I didn't think was really
the cause of the problem anyway.
> The form is in a Tab control.
You mean this is a subform on a tab control? I don't see the relevance, so
long as the code and the two list boxes are all on the same form object.
> The Debug.Print is in the code like below......and I'm not sure how it is
> supposed to work..............
> Private Sub List11_Click()
> List13.RowSource = "SELECT DateDep, Account, AccountName,
> Format(SUM(Amount),'currency')" & _
> " FROM Deposits" & _
> " WHERE ((Fund)='" & List11 & "'))" & _
> " GROUP BY Month(DateDep)" & _
> " ORDER BY DateDep DESC;"
> List13.Requery
> Debug.Print List13.RowSource
> End Sub
You don't need the line "List13.Requery", so get rid of it. Changing a list
box's RowSource always requeries it.
The "Debug.Print" line is just there for debugging, to cause the RowSource
to be printed to the Immediate Window. With that line in place, click on
List11, then press Ctrl+G to display the Immediate Window. You'll see the
value you assigned to List13.RowSource there. If inspection doesn't reveal
any errors, copy the SQL statement from there to the clipboard, then create
a new query, put that query into SQL View, and paste the SQL statement into
it. The switch that query to datasheet view and see if any rows are
returned.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
=?Utf-8?B?R2F0b3I=?= <Gator[ at ]discussions.microsoft.com> wrote in
news:2A3D1FB6-CAA8-46D6-A800-40C02A2A6D65[ at ]microsoft.com:
[Quoted Text] > The (" & _) is actually in the code to break lines...which I don't
> think is having any affect.
>
> I tried adding a space before the FROM like this....
> " FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
> .........instead of.......
> "FROM Deposits WHERE (((Fund)='" & List11 & "'))" & _
>
> The form is in a Tab control.
>
> The Debug.Print is in the code like below......and I'm not sure
> how it is supposed to work..............
> Private Sub List11_Click()
> List13.RowSource = "SELECT DateDep, Account, AccountName,
> Format(SUM(Amount),'currency')" & _
> " FROM Deposits" & _
> " WHERE ((Fund)='" & List11 & "'))" & _
> " GROUP BY Month(DateDep)" & _
> " ORDER BY DateDep DESC;"
> List13.Requery
> Debug.Print List13.RowSource
> End Sub
>
> thanks
>
you have a totals query with 4 columns.
A totals query must have an aggregate function or a Group By entry
for each column. I count 1 aggregate (the Sum) and 1 group by which
is not a column , so Access throws up an error message.
I bet you have an on error resume next statement above what you sho
us. Comment that out, and report the error message.
Fix the SELECT Statement "SELECT month(DateDep) ...
add ", Account, AccountName" to the Group By
Q
> "Dirk Goldgar" wrote:
>
>> "Gator" <Gator[ at ]discussions.microsoft.com> wrote in message
>> news:96F983D5-ABE7-4C0E-9E7E-9B3989D20EA6[ at ]microsoft.com...
>> > multiselect - No
>> > Fund in List11 is 'Text' field in Funds Table.
>> >
>> > still the same....
>>
>> You tried correcting the minor SQL error I pointed out, and that
>> didn't help?
>>
>> The next thing to do is add a line
>>
>> Debug.Print List13.RowSource
>>
>> after you set it the rowsource, then examine the rowsource query
>> as it is printed in the Immediate window. Does it look correct?
>> If it does, try copying it and pasting it into the SQL view of a
>> new query, and see if that query returns any rows.
>>
>> --
>> Dirk Goldgar, MS Access MVP
>> www.datagnostics.com
>>
>> (please reply to the newsgroup)
>>
>>
--
Bob Quintal
PA is y I've altered my email address.
** Posted from http://www.teranews.com **
|
|
"Bob Quintal" <rquintal[ at ]sPAmpatico.ca> wrote in message
news:Xns9B60BC84E323FBQuintal[ at ]66.175.223.2...
[Quoted Text] > =?Utf-8?B?R2F0b3I=?= <Gator[ at ]discussions.microsoft.com> wrote in
> news:2A3D1FB6-CAA8-46D6-A800-40C02A2A6D65[ at ]microsoft.com:
>
> you have a totals query with 4 columns.
>
> A totals query must have an aggregate function or a Group By entry
> for each column. I count 1 aggregate (the Sum) and 1 group by which
> is not a column , so Access throws up an error message.
>
> I bet you have an on error resume next statement above what you sho
> us. Comment that out, and report the error message.
>
> Fix the SELECT Statement "SELECT month(DateDep) ...
> add ", Account, AccountName" to the Group By
Good spot, Bob. I wish I'd noticed that.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
Hi Gator,
If I follow the conversion with the others you still do not have
success. So, a really basic question. It the code even being executed?
Maybe put a MsgBox "Clicked on List13" at the beginning of the subroutine.
See if you get the message when you click.
Clifford Bass
"Gator" wrote:
[Quoted Text] > still the same...
>
> "Clifford Bass" wrote:
>
> > Hi Gator,
> >
> > Try adding a List13.Requery after setting the row source.
> >
> > Clifford Bass
|
|
On Nov 24, 6:49 pm, "Dirk Goldgar"
<d...[ at ]NOdataSPAMgnostics.com.invalid> wrote:
[Quoted Text] > "BobQuintal" <rquin...[ at ]sPAmpatico.ca> wrote in message
>
> news:Xns9B60BC84E323FBQuintal[ at ]66.175.223.2...
>
> > =?Utf-8?B?R2F0b3I=?= <Ga...[ at ]discussions.microsoft.com> wrote in
> >news:2A3D1FB6-CAA8-46D6-A800-40C02A2A6D65[ at ]microsoft.com:
>
> > you have a totals query with 4 columns.
>
> > A totals query must have an aggregate function or a Group By entry
> > for each column. I count 1 aggregate (the Sum) and 1 group by which
> > is not a column , so Access throws up an error message.
>
> > I bet you have an on error resume next statement above what you sho
> > us. Comment that out, and report the error message.
>
> > Fix the SELECT Statement "SELECT month(DateDep) ...
> > add ", Account, AccountName" to the Group By
>
> Good spot,Bob. I wish I'd noticed that.
I have lots of experience, debugging my own code :-)
Q
|
|
|