Macro Question

[Stacey Crowhurst]

Hi. I am very new to macros and am having some problems. I have a form that
shows vendor ID and vendor name as a list box. I created a command button to
take me to the transaction report for the vendor that is highlighed from the
list box.

My macro has two lines:
1. Close - closes the transaction report
2. OpenReport - opens the transaction report
For #2 I have a query acting as the filter. Here is the SQL for that query:
SELECT [Transaction - General].[CCP No], [Vendor - Directory].[Vendor ID],
[Vendor - Directory].[Vendor Name], [Transaction - General].[Date Entered],
[Transaction - Status].Status, [Transaction - General].[Check No],
[Transaction - General].[Check Date], [Transaction - Source].[Source Type],
[Transaction - Details].[Invoice No], [Transaction - Details].[Invoice Date],
[Transaction - Details].Contract, Sum([Transaction - Details].[Line Amount])
AS [SumOfLine Amount]
FROM ((([Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID])
INNER JOIN [Vendor - Directory] ON [Transaction - Details].[Vendor ID] =
[Vendor - Directory].[Vendor ID]) INNER JOIN [Transaction - Source] ON
[Transaction - General].Source = [Transaction - Source].[Source ID]) INNER
JOIN [Transaction - Status] ON [Transaction - General].Status = [Transaction
- Status].[Status ID]
GROUP BY [Transaction - General].[CCP No], [Vendor - Directory].[Vendor ID],
[Vendor - Directory].[Vendor Name], [Transaction - General].[Date Entered],
[Transaction - Status].Status, [Transaction - General].[Check No],
[Transaction - General].[Check Date], [Transaction - Source].[Source Type],
[Transaction - Details].[Invoice No], [Transaction - Details].[Invoice Date],
[Transaction - Details].Contract
HAVING ((([Vendor - Directory].[Vendor ID])=[Forms]![F- Look
Up_Vendors]![Vendor List]));

When I click on the command button it just brings up the entire transaction
report for all vendors. I had success doing this for my contract list form
and contract report but can't seem to translate it to work for here. Any
ideas?

Thank you!

 

[Steve Schapel]

Stacey,

First of all, off on a tangent, a comment not related to your question...
It is not a good idea to use a "-" (or any other non-alpha/digit) character
as part of the name of a field or database object.

I can't understand your macro... why are you closing the report, and then
running it again?

When you say "query acting as the filter", what exactly does this mean?
*Where* and *how* is it acting as a filter? Do you mean that this query is
set as the Record Source of the report?

 

[Stacey Crowhurst]

Hi Steve. Does using non alpha characters confuse Access? I will try to
rename my objects if Access will like it better.

I am creating a new database for my department. The old one had been used
in various forms for ten years or so. I am starting from scratch to make a
new one. But for macros, I copied what had been used in the old version as
it seemed to work there. So I have no good reason to close the report then
open it.

For the query as a filter, in the macro design view under OpenReport I have
my query name "Q- Transaction_Detail 02" in the filter name box. The record
source for the report is "Q- Transaction_Detail 01". I copied query 01 into
query 02 only adding "[Forms]![F- Look Up_Vendors]![Vendor List]" as a
criteria under Vendor ID. (another instance of copying 'best practice' from
the old database)

All I want to happen when I click the command button is to have the
transaction report open up only for the vendor ID that was selected in the
list box on my form "F- Look Up_Vendors". Right now it opens up the
transaction report without a vendor filter showing every vendor.

Thanks for helping. Let me know if you need more information.
Stacey

Stacey,

First of all, off on a tangent, a comment not related to your question...
It is not a good idea to use a "-" (or any other non-alpha/digit) character
as part of the name of a field or database object.

I can't understand your macro... why are you closing the report, and then
running it again?

When you say "query acting as the filter", what exactly does this mean?
*Where* and *how* is it acting as a filter? Do you mean that this query is
set as the Record Source of the report?

--

Steve Schapel, Microsoft Access MVP

Hi. I am very new to macros and am having some problems. I have a form
that
shows vendor ID and vendor name as a list box. I created a command button
to
take me to the transaction report for the vendor that is highlighed from
the
list box.

My macro has two lines:
1. Close - closes the transaction report
2. OpenReport - opens the transaction report
For #2 I have a query acting as the filter. Here is the SQL for that
query:
SELECT [Transaction - General].[CCP No], [Vendor - Directory].[Vendor ID],
[Vendor - Directory].[Vendor Name], [Transaction - General].[Date
Entered],
[Transaction - Status].Status, [Transaction - General].[Check No],
[Transaction - General].[Check Date], [Transaction - Source].[Source
Type],
[Transaction - Details].[Invoice No], [Transaction - Details].[Invoice
Date],
[Transaction - Details].Contract, Sum([Transaction - Details].[Line
Amount])
AS [SumOfLine Amount]
FROM ((([Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID])
INNER JOIN [Vendor - Directory] ON [Transaction - Details].[Vendor ID] =
[Vendor - Directory].[Vendor ID]) INNER JOIN [Transaction - Source] ON
[Transaction - General].Source = [Transaction - Source].[Source ID]) INNER
JOIN [Transaction - Status] ON [Transaction - General].Status =
[Transaction
- Status].[Status ID]
GROUP BY [Transaction - General].[CCP No], [Vendor - Directory].[Vendor
ID],
[Vendor - Directory].[Vendor Name], [Transaction - General].[Date
Entered],
[Transaction - Status].Status, [Transaction - General].[Check No],
[Transaction - General].[Check Date], [Transaction - Source].[Source
Type],
[Transaction - Details].[Invoice No], [Transaction - Details].[Invoice
Date],
[Transaction - Details].Contract
HAVING ((([Vendor - Directory].[Vendor ID])=[Forms]![F- Look
Up_Vendors]![Vendor List]));

When I click on the command button it just brings up the entire
transaction
report for all vendors. I had success doing this for my contract list
form
and contract report but can't seem to translate it to work for here. Any
ideas?

Thank you!