APPEND QUERY RUNS ON ITS OWN BUT NOT IN A MACRO

[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

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

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]

Allan

 

[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

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

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

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]

Allan

 

[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

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

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

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]

Allan

 

[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 (e-mail address removed),
my mobile +44 777 556 5340.

Allan

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]

 

[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

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

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

 

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

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

 

[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

 

[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

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]

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

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

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