Building a Macro

  • Thread starter Thread starter Alison Downing via AccessMonster.com
  • Start date Start date
A

Alison Downing via AccessMonster.com

This is, I guess, a very basic question.
Using info gathered from this forum, I am trying to set up a form that will
let me enter criteria once only for a report which has 4 subreports.
I found a great thread [Double Criteria???, posted 28 Jan 2005] and have
followed it exactly however, Access is now telling me that it can't find
the macro and I guess this is because ... "the macro is new and hasn't been
saved"
I have never created a macro before and I'd be really grateful for some
simple, idiot-proof, step-by-step explanations as to how I load up the
following;

DoCmd.OpenForm "DateEntryForm", , , , , acDialog
DoCmd.Close acForm, "DateEntryForm"

I would also like to know if I have to do the same for another piece of
code in the thread;

Me.Visible = False

which is used to code a command button click event.

My original queries are also now refusing to return any data but I figure
this is probably because Access doesn't recognise the macros yet.

I'm sure someone can help - thanks to you guys I've gone further with
Access in the last month than I have in the past 5 years. You are so much
more helpful than the Access Help text!!!!

Many Thanks
Alison
amd(AT)dircon.co.uk
 
Alison,

I am afraid I can't offer much specific help at this stage. But maybe I
have a few comments which may help get on the right track. First of
all, the examples you gave have got nothing to do with macros. This is
VBA code which would be part of a VBA procedure. Second of all, it is
not at all clear what is the purpose of this code...
DoCmd.OpenForm "DateEntryForm", , , , , acDialog
DoCmd.Close acForm, "DateEntryForm"
Maybe you could give some more information about what you are trying to
achieve here.
Thirdly, I don't know why your "original queries are also now refusing
to return any data". But whatever the reason, I can't see how this
could be related to "recognising the macros", or to recognising VBA code
for that matter. Maybe you could post back with the SQL view of the
uncooperative query, as this may help others to understand your situation.
 
Hi Steve,
I'm trying to set up a form that will allow me to enter parameter dates for
report that contain four subreports. I had originally put the parameters
in the queries but this resulted in me having to enter the dates for every
subreport (8 date entries).
The key points in the thread I followed are;

" .... Each query requires it's own parameters.
The thing to do is create a form to enter the parameters in.
Leave it open while the reports are tun. Here is how.

Create an unbound form. Add 2 Text Controls.
Set the Format property to an appropriate Date Format.
Name one StartDate and the 2nd EndDate.

Add a command button.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In each query, in it's Date field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

Next, code the main report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the main report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report. The form will display
and wait for the entry of the dates. Click the command button and the
report will run without need for any further parameter entries. When
the report closes, it will close the form. ..."

I followed the instructions exactly (but have named my form "DateEntryForm")
.. Now, when I try to run the report I get a message saying 'Microsoft
Access can't find the macro 'DoCmd'.' which is why I assumed I had to build
and save the macro.

I'm now really confused ..... please help me out.
 
Hi there,
I did some more searching and I have solved part of the problem!
I had entered the 'DoCmd' line as an event, rather than an [Event Procedure]
and my 'DateEntryForm' is now working correctly and Thank You for pointing
me to VBA rather than macros

However, my original queries are still not working and the subreports that
they were driving do not appear on my main report at all except in design
view.

As both queries are similar; here is the layout of the first one - maybe
you can tell me what is wrong;

[Date Paid] Criteria: [forms]![DateEntryForm]![StartDate] And [forms]!
[DateEntryForm]![EndDate]
[Net Price]
[InvVATCalc]
[Customer]

I had hoped that this would return all records where [Date Paid] was
between the two dates entered into my [DateEntryForm] form and when I run
the query it prompts me for 'Forms!DateEntryForm!StartDate' and then for
'Forms![DateEntryForm]!EndDate' but returns no records at all.

Two steps forward, one step back again!!!
 
Duh!!!!!
Sorted it ..... I forgot to put "Between" at the beginning of my criteria.
I guess that what comes of trying to do this stuff at 3 in the morning.
Many Thanks for your help.
 
Alison,

Very pleased to hear that you have got this sorted out now.
Congratulations! :-)
 
Back
Top