APPEND QUERY RUNS ON ITS OWN BUT NOT IN A MACRO

[ALLAN_ASHFORD]

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

[Gina Whipp]

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...

[ALLAN_ASHFORD via AccessMonster.com]

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/Forums.aspx/access-tablesdbdesign/200912/1

[Gina Whipp]

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...

[Fred]

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

[ALLAN_ASHFORD via AccessMonster.com]

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

[Gina Whipp]

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...

[ALLAN_ASHFORD via 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]

[Gina Whipp]

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...

[ALLAN_ASHFORD via 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/Forums.aspx/access-tablesdbdesign/200912/1

[Gina Whipp]

Allan,

I THINK this applies to Access 2000, I forget when this started. To check
the Macro Settings, which is via Access not via the network, follow these
steps...

1. Open any database
2. Go to Tools... Macro... click Security...
3. On the machine that works check the setting but it sounds like it's set
to Low. Insure all the other machines are set the same way. Yep, you get a
message about being unsafe... just say Yes or OK.

Let's see if that's the problem.

--
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:a066b80b970e3@uwe...

[AccessVandal via AccessMonster.com]

Macro Security is only avialable in 2003 and 2007. For 2000 there's no
security for macros. What Gina is refering to is the end users PC, what
version are they using? If it is 2003 or 2007, you might want to ask them to
to disable macros for this test. If they are using 2007, make sure the
database is in Trusted locations.

You might want to check for WorkGroup Security just in case even the
owner/user or the Admin user setting was incorrect where the user might not
be able to execute the database macro. You can go to the security - user and
group permissions - object type - select the your macro name and make sure
they have the neccessary permission. They are open/run, read design, modify
design, administer. I doubt it, as it is very unlikely that someone might
change this, but than again who knows? (even if you don't use workgroup
security, it still worthwhile to check just in case someone setup user
security accidentaly). 2007 format databse(accdb) does not support workgroup
security.

ALLAN_ASHFORD wrote:
>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

--
Please Rate the posting if helps you.

[Gina Whipp]

AccessVandal,

Thanks, I wasn't sure if that was available in 2000.

--
Gina Whipp

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

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

"AccessVandal via AccessMonster.com" <u18947@uwe> wrote in message
news:a067a0b8d850a@uwe...

[ALLAN_ASHFORD via AccessMonster.com]

Hi Gina

I'll check security out in the morning (I'm UK based nand it's now 2.15 am).
But I'm running both databases on the same PC under Access 2000 and both with
linked tables onto the same other PC across my own network, i.e. I'm still at
the testing stage, so none of the modifications have been loaded onto the
clients's PCs.

Allan

Gina Whipp wrote:
>AccessVandal,
>
>Thanks, I wasn't sure if that was available in 2000.
>

>> Macro Security is only avialable in 2003 and 2007. For 2000 there's no
>> security for macros. What Gina is refering to is the end users PC, what

>[quoted text clipped - 36 lines]
>>>
>>>Allan

[Gina Whipp]

Allan,

Then the Macro Security is not an option if using Access 2000. How is the
macro run? Is it a button on a form? What is the code?

--
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:a0682ab3954ac@uwe...

[ALLAN_ASHFORD via AccessMonster.com]

Hi Gina

The macro that has the problem runs from within another macro which is
activated by clicking a button on a form. The problem macro has a Repeat
Count of 200 and a Repeat Expression in the form of a Form that shows the
difference between 2 numbers and runs whilst this difference is >0.

Gina Whipp wrote:
>Allan,
>
>Then the Macro Security is not an option if using Access 2000. How is the
>macro run? Is it a button on a form? What is the code?
>

>> Hi Gina
>>
>[quoted text clipped - 18 lines]
>>>>>
>>>>>Allan

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200912/1

[Gina Whipp]

Allan,

Have you you tried *Stepping* thru the macro? Also can you post the Actions
of the Macro here? Since both databases are connected to the same back-end,
are you running them at the same time?

--
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:a06eca20f244e@uwe...

[ALLAN_ASHFORD via AccessMonster.com]

Hi Gina

Have tried stepping but still no success.

Sorry to seem so dim, but how do I post the Macro Actions?

I am not running the 2 databases simultaneously, they merely sit alongside
each other on the local hard drive and the 2 back-ends likewise on the other
PC.

Gina Whipp wrote:
>Allan,
>
>Have you you tried *Stepping* thru the macro? Also can you post the Actions
>of the Macro here? Since both databases are connected to the same back-end,
>are you running them at the same time?
>

>> Hi Gina
>>
>[quoted text clipped - 13 lines]

[Gina Whipp]

Allan,

No need to be sorry... now once you do you will have learned something new
today! Unfortunately, no easy way to do this... You have to open the Macro
and type each line

Action
blah

Action Arguements
blah
blah
etc...

--
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:a07105549fac7@uwe...

[ALLAN_ASHFORD via AccessMonster.com]

Hi Gina

This is the macro that is giving the problem:-

Set Warnings Warnings On No

Close Form 20 - FIND HIGHEST/LOWEST
DIFFERENCE COUNT FORM

Save Prompt

OpenQuery 20 - FIND LOWEST UNADDED
LINE

View Datasheet

Data Mode: Edit

***************************************************************************************************

OpenQuery 20 - APPEND RAW MATERIALS
RE LOWEST
ASSIGNED BOM

View Datasheet

Data Mode: Edit

This is the Query that doesn't work in the Macro
****************************************************************************************************

OpenQuery 20 - HIGHEST ASSIGNED BOM
LINE

View Datasheet

Data Mode: Edit

OpenQuery 20 - FLAG BOM LINE AS
ASSIGNED TO RAW MATERIALS

View Datasheet

Data Mode: Edit

OpenForm 20 - FIND HIGHEST/LOWEST DIFFERENCE COUNT FORM

View: Form
Filter Name:
Where Condition:
Data Mode: -1
Window Mode: Hidden

The Form is needed to give the Repeat Expression condition in the parent
macro.

Allan

Gina Whipp wrote:
>Allan,
>
>No need to be sorry... now once you do you will have learned something new
>today! Unfortunately, no easy way to do this... You have to open the Macro
>and type each line
>
>Action
>blah
>
>Action Arguements
>blah
>blah
>etc...
>

>> Hi Gina
>>
>[quoted text clipped - 20 lines]

[Gina Whipp]

Allan,

If the query is dependent on the form to run why does it open after the
query is set to run. Might be a timing issue?

--
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:a091868c5a4e5@uwe...

[ALLAN_ASHFORD via AccessMonster.com]

Hi Gina

The Form contains a numeric value which is the current difference between the
Lowest & Highest Unadded Lines in a table. Whilst this is >0 the macro will
run, hence the reason for closing the form and then reopening so that the
test can be carried out in the Repeat Expression ([Forms]![20 -FIND
HIGHEST/LOWEST DIFFERENCE COUNT FORM]!{Expr1])>0 in the parent macro.

I have tried putting a MsgBox in the troublesome macro immediately before the
OpenForm command, but to no avail.

Allan
Gina Whipp wrote:
>Allan,
>

>If the query is dependent on the form to run why does it open after the
>query is set to run. Might be a timing issue?
>

>> Hi Gina
>>
>[quoted text clipped - 78 lines]

[Gina Whipp]

Allan,

To no avail, does that mean the message box didn't even open? Also, have
you considered refreshing the value in the form as opposed to closing and
then opening? Once the query opens it's static, it won't look for... oh a
form opened and I need that value...

--
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:a096e8e75eb4d@uwe...

[ALLAN_ASHFORD via AccessMonster.com]

Hi Gina

The MsgBox opened OK. To no avail referred to the fact that the problem query
still did not append the data it should.

Going back to the start we have the situation that the problem query will run
on its own but not from within a macro yet on the original database which
sits alongside it exactly the same macro works OK.

So the question is basically why exactly the same query will not run off a
macro in one database but will run off a macro in another databse which has
exactly the same structure and runs the same queries in this regard.

Yes, the 2 databases do have differences as effectively the database that is
OK is a "parent" to the one with the problem, but those differences are
elsewhwre and indeed are working OK.

Allan
Gina Whipp wrote:
>Allan,
>

>To no avail, does that mean the message box didn't even open? Also, have
>you considered refreshing the value in the form as opposed to closing and
>then opening? Once the query opens it's static, it won't look for... oh a
>form opened and I need that value...
>

>> Hi Gina
>>
>[quoted text clipped - 21 lines]

[Gina Whipp]

Allan,

There is SOMETHING different and I'll be d***ed I can't figure it out. Any
chance I get look at the *bad* database?

--
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:a09df1afa5ec2@uwe...

[ALLAN_ASHFORD via AccessMonster.com]

Hi Gina

You sure can have a look at the "bad" database. As a Zip file it's c. 9 MB
and its back-end c. 17 MB. I can log on remotely and transfer the zip files
or you can log on to me and do the same. The back-end is installed on a drive
mapping of E:\ACCESS, so to use this will save you having to relink the
tables. Also there are ODBC links to Sage Line50 Accounts, but these are not
used in the problem area, although the AUTOEXEC macro will fail to run
properly.

I can give you further pointers as to where the macro runs from as and when.

Let me know how you want to handle it. My e-mail is allan...@btconnect.com,
my mobile +44 777 556 5340.

Allan

Gina Whipp wrote:
>Allan,
>

>There is SOMETHING different and I'll be d***ed I can't figure it out. Any
>chance I get look at the *bad* database?
>

>> Hi Gina
>>
>[quoted text clipped - 30 lines]
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>Allan

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

[Gina Whipp]

Allan,

I looked at evey query attached to that macro, ummm and there were alot, and
I see no reason why they would fail. I am now as stumped as you are. There
are no issues, nothing. The only thing I can think of is if they are
running at the same time then the second one gets stuck because the first
one has *frozen* the process and the second one can't get access to it until
it's done. But even that would be odd unless they are updating the same
data at the same time.

As a side note... Your field names, in some cases, use Reserved Words and
this will cause issues, especially should you upgrade to Access 2007 where
it really has a fit. You might want to have a look at...

http://allenbrowne.com/AppIssueBadWord.html

Some of your tables are designed in a flat file format, that would be more
like Excel spreadsheets then a relational database. If you would like to
take advantage of the power of using a well normalized database, have a look
at...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

http://www.databasedev.co.uk/table-of-contents.html

I am also sending this reply to your email...

--
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:a0a4ef46d0365@uwe...

> Hi Gina
>
> You sure can have a look at the "bad" database. As a Zip file it's c. 9 MB
> and its back-end c. 17 MB. I can log on remotely and transfer the zip
> files
> or you can log on to me and do the same. The back-end is installed on a
> drive
> mapping of E:\ACCESS, so to use this will save you having to relink the
> tables. Also there are ODBC links to Sage Line50 Accounts, but these are
> not
> used in the problem area, although the AUTOEXEC macro will fail to run
> properly.
>
> I can give you further pointers as to where the macro runs from as and
> when.
>

> Let me know how you want to handle it. My e-mail is > my mobile >

[AccessVandal via AccessMonster.com]

Gina,

I notice the macros OpenQuery "View = Datasheet" and "Data Mode = Edit". Are
these necessary? It's already in datasheet. Not all joint query is Editable.

As far as I know, if you open the Append (Action Query) in DataSheet View,
the Append query will not modify the records, it will only show the data of
the records of the Append query.

Try to remove these, see if it works.

Gina Whipp wrote:
>Allan,
>
>I looked at evey query attached to that macro, ummm and there were alot, and
>I see no reason why they would fail. I am now as stumped as you are. There

snip...

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200912/1

[Gina Whipp]

AccessVandal,

In the macro yes those settings are required and the other options are not
an option. The subject line is kind of deciptive because those queries he
is referring to are actually, make table and update queries. Oddly, enough
the macro runs in one database FE/BE set-up but not the other. He sent me a
copy of the database and I was looking for some reference to a table or
query that existed in the one database but not the other and after reviewing
MANY queries, found one. So now I am as stumped as he is.

--
Gina Whipp

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

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

"AccessVandal via AccessMonster.com" <u18947@uwe> wrote in message
news:a0e5bd16150f5@uwe...

[ALLAN_ASHFORD via AccessMonster.com]

Maybe no longer stumped!!

The table that I was trying to append the data to was, as you know, copied
over from the fully functional database. As such it did contain well over 100,
000 redundant records. So I deleted the lot, did a repair & compact and hey
presto everything appears to be OK. Only problem now is - trying to remember
what I was trying to do when the problem hit!!

Many thanks for all the input - have a great Xmas.

Allan

Gina Whipp wrote:
>AccessVandal,
>
>In the macro yes those settings are required and the other options are not
>an option. The subject line is kind of deciptive because those queries he
>is referring to are actually, make table and update queries. Oddly, enough
>the macro runs in one database FE/BE set-up but not the other. He sent me a
>copy of the database and I was looking for some reference to a table or
>query that existed in the one database but not the other and after reviewing
>MANY queries, found one. So now I am as stumped as he is.
>

>> Gina,
>>
>[quoted text clipped - 17 lines]
>>>There
>> snip...

[Gina Whipp]

Allan,

Just say thank goodness it works!

Happy Holidays!
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:a0fcbd65df3d1@uwe...