Buggy macro -- performing actions after the fact

  • Thread starter Thread starter Kendra
  • Start date Start date
K

Kendra

Hello,

I have a macro created from the combination of two action
queries, a delete query and an append query. The delete
query deletes a record if a certain criteria is met, in my
case [AdminApproval]="decline". Similarly, the append
query appends a record onto another table when a different
criteria is met, [AdminApproval]="accept". To create the
macro, I dragged each of these queries into the first two
lines of my action column (within the macro design view).
In the form, where I am trying to implement the macro, I
have called for the macro in the "afterchange" box of the
properties/events window of the field (the event is
applied to a pull down menu).

I am not sure whether the error is introduced by the query
or the macro. Whenever I open the queries in design view,
their design has changed from what I originally created.
Instead of listing the criteria ([AdminApproval]="accept"
for the append query and [AdminApproval]="deline" for the
delete query) under each column of my query design view
(as shown in the query) the very last column of the append
says "Accept" and "Accept" and "Accept" and the very last
column of the delete query says "Decline" and "Decline"
and "Decline".

I have no idea what is going on, however, the resulting
macro does not behave as it should. When the AdminApproval
field is changed either to "accept" or "decline" for the
first record, nothing happens. When the field is changed
for the second record "accept" or "decline", the change
requested for first record is implemented for the first
record. When the field is changed for the third record,
the change requested for the second record is implemented
for the second record. The changes requested are
eventually implemented (except for the final record), but
this is really bizarre. Any suggestions?

I really appreciate your time,
Kendra
 
Kendra,

In the query design window of your Delete and Append queries, could you
please select SQL from the View menu, and then copy/paste the SQL view
of the queries into your reply post here. Also, can you please give
full details of the macro... actions and arguments. Thanks. I assume
you meant the AfterUpdate event of your AdminApproval combobox where the
macro is assigned?
 
SQL of append query (AcceptoAppend):
INSERT INTO tbl_New_Report_DB ( Record_ID, [Date],
Jurisdiction, [Zone], LU_Type, Units, ExUnits, [Fees
Paid], Credits, ConCredits, [Obl Fees] )
SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data
Entry].Date, [tbl_Inital Data Entry].Jurisdiction,
[tbl_Inital Data Entry].Zone, [tbl_Inital Data
Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital
Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid],
[tbl_Inital Data Entry].Credits, [tbl_Inital Data
Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees]
FROM [tbl_Inital Data Entry]
WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Accept"
And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept"));

SQL of delete query (DeleteonDecline):
DELETE [tbl_Inital Data Entry].*, [tbl_Inital Data
Entry].AdminStatus, [tbl_Inital Data Entry].Record_ID,
[tbl_Inital Data Entry].Date, [tbl_Inital Data
Entry].Jurisdiction, [tbl_Inital Data Entry].Zone,
[tbl_Inital Data Entry].LU_Type, [tbl_Inital Data
Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital
Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits,
[tbl_Inital Data Entry].ConCredits, [tbl_Inital Data
Entry].[Obl Fees], [tbl_Inital Data Entry]![AdminStatus]
FROM [tbl_Inital Data Entry]
WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Decline"
And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline"));

I have put these both into a macro
called "AcceptDeclineMac" which only has two actions:
1. Open Query (argument is open AccepttoAppend)
2. Open Query (argumeent in open DeleteonDecline)

AcceptDeclineMac is referenced to in the
properties/event/"on change", as the AdminStatus field is
a pull down menu. I also tried linking the macro to
the "after update field" -- no changes in performance.

I really appreciate your time, I will keep an eye out for
your response.

Thanks,
Kendra
-----Original Message-----
Kendra,

In the query design window of your Delete and Append queries, could you
please select SQL from the View menu, and then copy/paste the SQL view
of the queries into your reply post here. Also, can you please give
full details of the macro... actions and arguments. Thanks. I assume
you meant the AfterUpdate event of your AdminApproval combobox where the
macro is assigned?

--
Steve Schapel, Microsoft Access MVP

Hello,

I have a macro created from the combination of two action
queries, a delete query and an append query. The delete
query deletes a record if a certain criteria is met, in my
case [AdminApproval]="decline". Similarly, the append
query appends a record onto another table when a different
criteria is met, [AdminApproval]="accept". To create the
macro, I dragged each of these queries into the first two
lines of my action column (within the macro design view).
In the form, where I am trying to implement the macro, I
have called for the macro in the "afterchange" box of the
properties/events window of the field (the event is
applied to a pull down menu).

I am not sure whether the error is introduced by the query
or the macro. Whenever I open the queries in design view,
their design has changed from what I originally created.
Instead of listing the criteria ([AdminApproval] ="accept"
for the append query and [AdminApproval]="deline" for the
delete query) under each column of my query design view
(as shown in the query) the very last column of the append
says "Accept" and "Accept" and "Accept" and the very last
column of the delete query says "Decline" and "Decline"
and "Decline".

I have no idea what is going on, however, the resulting
macro does not behave as it should. When the AdminApproval
field is changed either to "accept" or "decline" for the
first record, nothing happens. When the field is changed
for the second record "accept" or "decline", the change
requested for first record is implemented for the first
record. When the field is changed for the third record,
the change requested for the second record is implemented
for the second record. The changes requested are
eventually implemented (except for the final record), but
this is really bizarre. Any suggestions?

I really appreciate your time,
Kendra
Click to expand...
.
Click to expand...
 
Kendra,

Part of the problem is that the value of the AdminStatus field in the
tbl_Initial Data Entry table is not "Accept" or "Decline" at the point
where you make the selection from the combobox, because the record is
not saved. In any case, if it is just the current record you are
interested in, I would advise against using the query criteria to decide
which records to append or delete. This should really be a job for the
macro Condition. If you can't see the Condition column in the macro
design window, select it from the View menu. Here's how I would do it:

In the first line of the macro, put...
Action: RunCommand
.... and in the Command Line argument, put...
SaveRecord

In the next line of the macro, put...
Condition: [AdminStatus]="Accept"
Action: OpenQuery
QueryName argument: AcceptoAppend

Change the SQL of the AcceptToAppend query to:
INSERT INTO tbl_New_Report_DB ( Record_ID, [Date], Jurisdiction, [Zone],
LU_Type, Units, ExUnits, [Fees Paid], Credits, ConCredits, [Obl Fees] )
SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data Entry].Date,
[tbl_Inital Data Entry].Jurisdiction, [tbl_Inital Data Entry].Zone,
[tbl_Inital Data Entry].LU_Type, [tbl_Inital Data Entry].Units,
[tbl_Inital Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid],
[tbl_Inital Data Entry].Credits, [tbl_Inital Data Entry].ConCredits,
[tbl_Inital Data Entry].[Obl Fees]
FROM [tbl_Inital Data Entry]
WHERE [Record_ID] = [Forms]![NameOfYourForm]![Record_ID]

In the next line of the macro, put...
Condition: [AdminStatus]="Decline"
Action: RunCommand
Command Line argument: DeleteRecord

Remove the DeleteonDecline query altogether from your database, it is
not required, but for the record, the SQL of this query should have been
like this...
DELETE * FROM [tbl_Inital Data Entry]
WHERE ([tbl_Inital Data Entry]![AdminStatus])="Decline"

--
Steve Schapel, Microsoft Access MVP
SQL of append query (AcceptoAppend):
INSERT INTO tbl_New_Report_DB ( Record_ID, [Date],
Jurisdiction, [Zone], LU_Type, Units, ExUnits, [Fees
Paid], Credits, ConCredits, [Obl Fees] )
SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data
Entry].Date, [tbl_Inital Data Entry].Jurisdiction,
[tbl_Inital Data Entry].Zone, [tbl_Inital Data
Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital
Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid],
[tbl_Inital Data Entry].Credits, [tbl_Inital Data
Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees]
FROM [tbl_Inital Data Entry]
WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Accept"
And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept"));

SQL of delete query (DeleteonDecline):
DELETE [tbl_Inital Data Entry].*, [tbl_Inital Data
Entry].AdminStatus, [tbl_Inital Data Entry].Record_ID,
[tbl_Inital Data Entry].Date, [tbl_Inital Data
Entry].Jurisdiction, [tbl_Inital Data Entry].Zone,
[tbl_Inital Data Entry].LU_Type, [tbl_Inital Data
Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital
Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits,
[tbl_Inital Data Entry].ConCredits, [tbl_Inital Data
Entry].[Obl Fees], [tbl_Inital Data Entry]![AdminStatus]
FROM [tbl_Inital Data Entry]
WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Decline"
And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline"));

I have put these both into a macro
called "AcceptDeclineMac" which only has two actions:
1. Open Query (argument is open AccepttoAppend)
2. Open Query (argumeent in open DeleteonDecline)

AcceptDeclineMac is referenced to in the
properties/event/"on change", as the AdminStatus field is
a pull down menu. I also tried linking the macro to
the "after update field" -- no changes in performance.

I really appreciate your time, I will keep an eye out for
your response.

Thanks,
Kendra
Click to expand...
 
Steve -- THANK YOU!!!!! You have really made my week
= ). I hope someone out-there is able to do something
equally wonderful for you.

Kendra
-----Original Message-----
Kendra,

Part of the problem is that the value of the AdminStatus field in the
tbl_Initial Data Entry table is not "Accept" or "Decline" at the point
where you make the selection from the combobox, because the record is
not saved. In any case, if it is just the current record you are
interested in, I would advise against using the query criteria to decide
which records to append or delete. This should really be a job for the
macro Condition. If you can't see the Condition column in the macro
design window, select it from the View menu. Here's how I would do it:

In the first line of the macro, put...
Action: RunCommand
.... and in the Command Line argument, put...
SaveRecord

In the next line of the macro, put...
Condition: [AdminStatus]="Accept"
Action: OpenQuery
QueryName argument: AcceptoAppend

Change the SQL of the AcceptToAppend query to:
INSERT INTO tbl_New_Report_DB ( Record_ID, [Date], Jurisdiction, [Zone],
LU_Type, Units, ExUnits, [Fees Paid], Credits, ConCredits, [Obl Fees] )
SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data Entry].Date,
[tbl_Inital Data Entry].Jurisdiction, [tbl_Inital Data Entry].Zone,
[tbl_Inital Data Entry].LU_Type, [tbl_Inital Data Entry].Units,
[tbl_Inital Data Entry].ExUnits, [tbl_Inital Data Entry]. [Fees Paid],
[tbl_Inital Data Entry].Credits, [tbl_Inital Data Entry].ConCredits,
[tbl_Inital Data Entry].[Obl Fees]
FROM [tbl_Inital Data Entry]
WHERE [Record_ID] = [Forms]![NameOfYourForm]![Record_ID]

In the next line of the macro, put...
Condition: [AdminStatus]="Decline"
Action: RunCommand
Command Line argument: DeleteRecord

Remove the DeleteonDecline query altogether from your database, it is
not required, but for the record, the SQL of this query should have been
like this...
DELETE * FROM [tbl_Inital Data Entry]
WHERE ([tbl_Inital Data Entry]![AdminStatus])="Decline"

--
Steve Schapel, Microsoft Access MVP
SQL of append query (AcceptoAppend):
INSERT INTO tbl_New_Report_DB ( Record_ID, [Date],
Jurisdiction, [Zone], LU_Type, Units, ExUnits, [Fees
Paid], Credits, ConCredits, [Obl Fees] )
SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data
Entry].Date, [tbl_Inital Data Entry].Jurisdiction,
[tbl_Inital Data Entry].Zone, [tbl_Inital Data
Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital
Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid],
[tbl_Inital Data Entry].Credits, [tbl_Inital Data
Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees]
FROM [tbl_Inital Data Entry]
WHERE ((([tbl_Inital Data Entry]![AdminStatus]) ="Accept"
And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept"));

SQL of delete query (DeleteonDecline):
DELETE [tbl_Inital Data Entry].*, [tbl_Inital Data
Entry].AdminStatus, [tbl_Inital Data Entry].Record_ID,
[tbl_Inital Data Entry].Date, [tbl_Inital Data
Entry].Jurisdiction, [tbl_Inital Data Entry].Zone,
[tbl_Inital Data Entry].LU_Type, [tbl_Inital Data
Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital
Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits,
[tbl_Inital Data Entry].ConCredits, [tbl_Inital Data
Entry].[Obl Fees], [tbl_Inital Data Entry]![AdminStatus]
FROM [tbl_Inital Data Entry]
WHERE ((([tbl_Inital Data Entry]![AdminStatus]) ="Decline"
And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline"));

I have put these both into a macro
called "AcceptDeclineMac" which only has two actions:
1. Open Query (argument is open AccepttoAppend)
2. Open Query (argumeent in open DeleteonDecline)

AcceptDeclineMac is referenced to in the
properties/event/"on change", as the AdminStatus field is
a pull down menu. I also tried linking the macro to
the "after update field" -- no changes in performance.

I really appreciate your time, I will keep an eye out for
your response.

Thanks,
Kendra
Click to expand...
.
Click to expand...
 
Back
Top