Automatic activation of a macro or event.

[Craig Cerff]

I have a complaints data base. When I recieve three
complaints for a certain product made on a certain date,
then a warning must be given. Currently I am using a
query that groups the info according to the dates and
product type and then does a count if>2 the query will see
this. When I open a form it runs the query and gives
either a blank form or comes up with the name of nthe
product, the date and the number of complaints if >2.
This data is enetered via a separate data entry form. SO
what I would like is a macro or event to occur on update
or some other action, that opens the form but only if
there are > 2 comnplaints. It seems almost chicken and
egg as the form opens the query that looks to see if the
conditions are met but then I want a separate macro to run
as an event to see if the conditions are met and open the
form that will run the query.
Please help
Craig

 

[Steve Schapel]

Craig,

This is certainly possible, via the use of a macro Condition. The
specifics depend on how your query criteria work. If I understand you
correctly, you already have a query which returns a list of any products
for which there are more than 2 complaints for the current date. In
this case, if you make an OpenForm macro and put it on the AfterUpdate
event of the data entry form, and in the Condition you put something
like this...
DCount("*","NameOfYourQuery")>0
If you want this to apply only to the product for which you have just
entered a new complain record, the easiest approach might be to just
make a new query specifically to apply your "test", so you would just
make a query based on your data table, and in the criteria of the date
field you put Date() nad in the criteria of the ProductID (or whatever
is the field that identifies the product) you put the equivalent of...
[Forms]![NameOfYourDataForm]![ProductID]
.... and then your macro Condition might be more like this...
DCount("*","YourTestQuery")>2

 

[Craig Cerff]

Thanks that is brilliant
Regards
Craig

-----Original Message-----
Craig,

This is certainly possible, via the use of a macro Condition. The
specifics depend on how your query criteria work. If I understand you
correctly, you already have a query which returns a list of any products
for which there are more than 2 complaints for the current date. In
this case, if you make an OpenForm macro and put it on the AfterUpdate
event of the data entry form, and in the Condition you put something
like this...
DCount("*","NameOfYourQuery")>0
If you want this to apply only to the product for which you have just
entered a new complain record, the easiest approach might be to just
make a new query specifically to apply your "test", so you would just
make a query based on your data table, and in the criteria of the date
field you put Date() nad in the criteria of the ProductID (or whatever
is the field that identifies the product) you put the equivalent of...
[Forms]![NameOfYourDataForm]![ProductID]
.... and then your macro Condition might be more like this...
DCount("*","YourTestQuery")>2

--
Steve Schapel, Microsoft Access MVP

I have a complaints data base. When I recieve three
complaints for a certain product made on a certain date,
then a warning must be given. Currently I am using a
query that groups the info according to the dates and
product type and then does a count if>2 the query will see
this. When I open a form it runs the query and gives
either a blank form or comes up with the name of nthe
product, the date and the number of complaints if >2.
This data is enetered via a separate data entry form. SO
what I would like is a macro or event to occur on update
or some other action, that opens the form but only if
there are > 2 comnplaints. It seems almost chicken and
egg as the form opens the query that looks to see if the
conditions are met but then I want a separate macro to run
as an event to see if the conditions are met and open the
form that will run the query.
Please help
Craig

.