Macro Question

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

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


Steve Schapel said:
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


Stacey Crowhurst said:
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!
Click to expand...
Click to expand...
 
Back
Top