APPEND QUERY RUNS ON ITS OWN BUT NOT IN A MACRO

Hi All

A database I created for a client some years ago has a macro which contains
an append query. This macro repeats itself until a condition is met and has
run thousands of times without any problem.

The client has asked that I make the database work for multiple factories.
Without going into too much detail the same macro and append query are needed
in the spin-off databases (one for each factory).

The problem is that the append query will not run in any of the spin-off
databases as part of the macro although the macro itself is unchanged, but
will run in isolation when it performs correctly and gives no error messages.
I have run the macro with SetWarnings ON, but only get the warnings that you
would normally expect.

I have pasted over the macro, the append query and the underlying select
queries from the original database, but to no effect.

Please has anyone any ideas on what is happening? I have spent nearly 2 days
on this without any success.

Many thanks in advance for any guidance offered.

Allan Ashford

Allan,

Is there anything in the Append query that points it to a particular form or
table?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"ALLAN_ASHFORD" u56735@uwe wrote in message news:a04d1ad9aeb87@uwe...
Hi All

A database I created for a client some years ago has a macro which
contains
an append query. This macro repeats itself until a condition is met and
has
run thousands of times without any problem.

The client has asked that I make the database work for multiple factories.
Without going into too much detail the same macro and append query are
needed
in the spin-off databases (one for each factory).

The problem is that the append query will not run in any of the spin-off
databases as part of the macro although the macro itself is unchanged, but
will run in isolation when it performs correctly and gives no error
messages.
I have run the macro with SetWarnings ON, but only get the warnings that
you
would normally expect.

I have pasted over the macro, the append query and the underlying select
queries from the original database, but to no effect.

Please has anyone any ideas on what is happening? I have spent nearly 2
days
on this without any success.

Many thanks in advance for any guidance offered.

Allan Ashford

Hi Gina

Not quite sure what you mean by "points it to a particular form or table".
The Append Query takes data from a Select Query which in turn takes data from
tables and queries and should append it to a linked table, but doesn't!!

Hope this helps.

Allan

Gina Whipp wrote:
Allan,

Is there anything in the Append query that points it to a particular form or
table?

Hi All

[quoted text clipped - 27 lines]

Allan Ashford

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200912/1

Allan,

Sometimes in the query you can have a field pointing to a particular field
in a form and the reason the query fails is because when you reused the
query that form is not there. OR something else on the criteria line is
causing the records to go nowhere. Copy/Paste the SQL of the query here and
let's see if that is the case here.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"ALLAN_ASHFORD via AccessMonster.com" u56735@uwe wrote in message
news:a05f7ff0f13c5@uwe...
Hi Gina

Not quite sure what you mean by "points it to a particular form or table".
The Append Query takes data from a Select Query which in turn takes data
from
tables and queries and should append it to a linked table, but doesn't!!

Hope this helps.

Allan

Gina Whipp wrote:
Allan,

Is there anything in the Append query that points it to a particular form
or
table?

Hi All

[quoted text clipped - 27 lines]

Allan Ashford

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200912/1

A couple of "narrowing down" ideas. Test/see if:

The same query will run in a different (one step) macro
A different query will run in that macro

Hi Gina

Here is the code:-

INSERT INTO [20 - RAW MATERIALS FOR ASSIGNED BOMs] ( [ASSIGNED REFERENCE],
MATCODE, DESCRIPTION, [QTY REQ] )
SELECT [20 - CALCULATE RAW MATERIALS FOR LINE].AUTONUMBER, [20 - CALCULATE
RAW MATERIALS FOR LINE].MATCODE, [20 - CALCULATE RAW MATERIALS FOR LINE].
DESCRIPTION, [20 - CALCULATE RAW MATERIALS FOR LINE].Expr1
FROM [20 - CALCULATE RAW MATERIALS FOR LINE];

A typical line from the select Query 20 - CALCULATE RAW MATERIALS FOR LINE IS
as follows:-

19349 135/PLAYINGCARDS BLACK PLAYING CARDS/210gsm in printed box
1000

Allan


Gina Whipp wrote:
Allan,

Sometimes in the query you can have a field pointing to a particular field
in a form and the reason the query fails is because when you reused the
query that form is not there. OR something else on the criteria line is
causing the records to go nowhere. Copy/Paste the SQL of the query here and
let's see if that is the case here.

Hi Gina

[quoted text clipped - 18 lines]

Allan Ashford

--
Message posted via http://www.accessmonster.com

Allan,

A couple of questions...

1. What is this field? [20 - CALCULATE RAW MATERIALS FOR LINE].Expr1
2. Perhaps showing the SQL of 20 - CALCULATE RAW MATERIALS FOR LINE would
help...
3. Do you get records returned when you run 20 - CALCULATE RAW MATERIALS
FOR LINE?
4. QTY REQ sounds like a numeric field, when you run the query (without
actually appending anything) is there any text in that field?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"ALLAN_ASHFORD via AccessMonster.com" u56735@uwe wrote in message
news:a063ef550745c@uwe...
Hi Gina

Here is the code:-

INSERT INTO [20 - RAW MATERIALS FOR ASSIGNED BOMs] ( [ASSIGNED REFERENCE],
MATCODE, DESCRIPTION, [QTY REQ] )
SELECT [20 - CALCULATE RAW MATERIALS FOR LINE].AUTONUMBER, [20 - CALCULATE
RAW MATERIALS FOR LINE].MATCODE, [20 - CALCULATE RAW MATERIALS FOR LINE].
DESCRIPTION, [20 - CALCULATE RAW MATERIALS FOR LINE].Expr1
FROM [20 - CALCULATE RAW MATERIALS FOR LINE];

A typical line from the select Query 20 - CALCULATE RAW MATERIALS FOR LINE
IS
as follows:-

19349 135/PLAYINGCARDS BLACK PLAYING CARDS/210gsm in printed box
1000

Allan


Gina Whipp wrote:
Allan,

Sometimes in the query you can have a field pointing to a particular field
in a form and the reason the query fails is because when you reused the
query that form is not there. OR something else on the criteria line is
causing the records to go nowhere. Copy/Paste the SQL of the query here
and
let's see if that is the case here.

Hi Gina

[quoted text clipped - 18 lines]

Allan Ashford

--
Message posted via http://www.accessmonster.com

Hi Gina

1. Expr1 is the field giving the quantity required to be appended to QTY
REQ

2. SELECT [20 - NEXT PLAN ORDER LINE TO BE ADDED TO RAW MATERIALS].
AUTONUMBER, [PRODCOST COMPONENTS].MATCODE, [PRODCOST COMPONENTS].DESCRIPTION,
[PRODCOST COMPONENTS].QTY, [PRODCOST COMPONENTS]![QTY]*[20 - NEXT PLAN ORDER
LINE TO BE ADDED TO RAW MATERIALS]![QTY ORDERED] AS Expr1
FROM [20 - NEXT PLAN ORDER LINE TO BE ADDED TO RAW MATERIALS] INNER JOIN
[PRODCOST COMPONENTS] ON [20 - NEXT PLAN ORDER LINE TO BE ADDED TO RAW
MATERIALS].[PRODUCT CODE] = [PRODCOST COMPONENTS].PRODCODE;

3. Yes

4. Running 20 - APPEND RAW MATERIALS RE LOWEST ASSIGNED BOM as a Select
Query shows the data that needs to be appended

Gina Whipp wrote:
Allan,

A couple of questions...

1. What is this field? [20 - CALCULATE RAW MATERIALS FOR LINE].Expr1
2. Perhaps showing the SQL of 20 - CALCULATE RAW MATERIALS FOR LINE would
help...
3. Do you get records returned when you run 20 - CALCULATE RAW MATERIALS
FOR LINE?
4. QTY REQ sounds like a numeric field, when you run the query (without
actually appending anything) is there any text in that field?

Hi Gina

[quoted text clipped - 30 lines]

Allan Ashford

--
Message posted via http://www.accessmonster.com

Allan,

Forgot to ask Access version?

And if I am understanding you... running the query appends the records BUT
running it from the macro does not work. Since the query appears fine.
What are the macro settings at in each front end or at each location?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"ALLAN_ASHFORD via AccessMonster.com" u56735@uwe wrote in message
news:a0644608416b6@uwe...
Hi Gina

1. Expr1 is the field giving the quantity required to be appended to
QTY
REQ

2. SELECT [20 - NEXT PLAN ORDER LINE TO BE ADDED TO RAW MATERIALS].
AUTONUMBER, [PRODCOST COMPONENTS].MATCODE, [PRODCOST
COMPONENTS].DESCRIPTION,
[PRODCOST COMPONENTS].QTY, [PRODCOST COMPONENTS]![QTY]*[20 - NEXT PLAN
ORDER
LINE TO BE ADDED TO RAW MATERIALS]![QTY ORDERED] AS Expr1
FROM [20 - NEXT PLAN ORDER LINE TO BE ADDED TO RAW MATERIALS] INNER JOIN
[PRODCOST COMPONENTS] ON [20 - NEXT PLAN ORDER LINE TO BE ADDED TO RAW
MATERIALS].[PRODUCT CODE] = [PRODCOST COMPONENTS].PRODCODE;

3. Yes

4. Running 20 - APPEND RAW MATERIALS RE LOWEST ASSIGNED BOM as a
Select
Query shows the data that needs to be appended

Gina Whipp wrote:
Allan,

A couple of questions...

1. What is this field? [20 - CALCULATE RAW MATERIALS FOR LINE].Expr1
2. Perhaps showing the SQL of 20 - CALCULATE RAW MATERIALS FOR LINE would
help...
3. Do you get records returned when you run 20 - CALCULATE RAW MATERIALS
FOR LINE?
4. QTY REQ sounds like a numeric field, when you run the query (without
actually appending anything) is there any text in that field?

Hi Gina

[quoted text clipped - 30 lines]

Allan Ashford

--
Message posted via http://www.accessmonster.com

Hi Gina

Access 2000

Being self-taught I'm not sure what you mean by "macro settings at in each
front end or at each location?". However the database from which the problem
database was derived runs in parallel presumeably under the same
configuration (local & network) without any problem as the one that is giving
the problem so the macro settings (whatever they are) should be the same. Can
you please give me some pointers to check?

Sorry I can't be of more help, but having used Access for 15 years this is
the first time I have come across a problem I haven't managed to solve.

Allan

Gina Whipp wrote:
Allan,

Forgot to ask Access version?

And if I am understanding you... running the query appends the records BUT
running it from the macro does not work. Since the query appears fine.
What are the macro settings at in each front end or at each location?

Hi Gina

[quoted text clipped - 35 lines]

Allan Ashford

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200912/1