APPEND QUERY RUNS ON ITS OWN BUT NOT IN A MACRO
in [Tables]
|
From: ALLAN_ASHFORD via AccessMonster.com on 10 Dec 2009 13:15 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
From: Gina Whipp on 10 Dec 2009 13:34 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(a)uwe> wrote in message news:a063ef550745c(a)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 >
From: ALLAN_ASHFORD via AccessMonster.com on 10 Dec 2009 13:54 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
From: Gina Whipp on 10 Dec 2009 14:15 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(a)uwe> wrote in message news:a0644608416b6(a)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 >
From: ALLAN_ASHFORD via AccessMonster.com on 10 Dec 2009 18:34
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 |