Update Append Queries (1 Viewer)

[jua_kali]

Hi I am trying to run append queries that update tables. How can I code this in the simplest way possible.

To briefly explain my problem, I am working on a Bill Of Materials:

1) I have a table where I have a list of items for eg FinishedItem.

2) I have created queries for raw materials and depending on the FinishedItem's values of its fields the item gets selected in a query.

3) There are about 20 append queries that I have created, after adding a new FinishedItem I need to run all the append queries.

At present I have a form that has add item button which can allow you to add the FinishedItem. I have also got another button "update item", which on click I want it to run all the append queries one after the other so it will get the new item selected in some of the queries which will obviously be appended to its respective table.

What is the best way to code this?

At present I have the following for one of the append queries but cant seem to get past it cause I dont know how to create an enter event when the append dialog boxes come up to ask user weather to continue or not.

Public Function RunQuery()

DoCmd.OpenQuery "AppenQuery1", , acEdit

' thsi is where I wanted to create "click enter" event or
' something that does that

DoCmd.Close acQuery, "AppendQuery2", acSavePrompt

End Function

Can someone please help on this?

Thank you in advance

 

[cogent1]

To clarify: ONE raw material is present in MANY Finished Items? And adding ONE Finished Item entails updating MANY raw materials? So you have a MANY-to-MANY relationship between RMs and FIs?

Please verify this, because if that's the case, you don't need to run multiple append queries. Forms/subforms should deal with it.

 

[jua_kali]

Yes you are right cogent1. One raw material is present in many finished products and adding one finished product entails updating many raw material queries.

So how can you update all the queries from the form?

Please explain cause I dont understand how.

Thanks.

 

[cogent1]

First, we have to establish that you have a primary key field ("RAWID") in the RM table, another PK field ("FMID") in the Materials table, and a third table containing both these fields as foreign keys, and linked to the respective primary tables as the MANY side of a relationship.

You can then set up a form linked to a subform. Each time you create a record for a new Finished Item, you can enter the raw materials it uses in the subform, by means of a lookup list. This updates the third table automatically, so you end up with a means of associating Raw materials with their Finished Items and vice-versa. No need for append queries at all.

If you don't understand the foregoing, send me the db as a zip file.

 

[jua_kali]

I understand what you are saying in terms of the keys but my database setup is slightly different. I will explain below:

I do not have a lookup list for raw materials because what I have is that each raw material has an excel file. What I mean by that is that the data from the query of each raw material is retrieved in an excel file. In the excel file of raw materials calculations are carried out and depending on the calculations the raw materials are assigned to the finished product. This is done so that certain finished goods may require more or less quantity of the raw material AND also for instance I have a raw material group say Plastic bags, I have 5 different types of plastic bags so it is here in excel which decides which plastic bag (type 1, type 2, etc etc)the finished product uses (eg type 2), this depends on the finished products attributes ..... say its diameter and length etc etc.

So I what I have in access is a list which filters out only the finished goods for a particular raw material. This filtered list is then extracted into an excel file for assigning its raw material and quantity.

At present I have the data for finished goods and its raw materials and quantity. But now if I were to add a new finished product it will have to be selected by some of the raw material queries (being selected by the criteria of this query means that the finished product will be using the particular raw material) which then is to be updated into excel, which I will have to deal with after this problem, but first I wanna deal with this issue of how to run all the append queries at once from the form?

I hope you can understand the problem. Thank you.

 

[jua_kali]

In short what I am trying to do is that when I run the query using the code using DoCmd OpenQuery I get the key violation error message. I understand this would be because query attempts to append or update records that contain primary key values that already exist in the destination table. But because it will only append the one new item that I have added I want it to carry on with the running the action query anyway and therefore need to click on “YES” for it to add the item.

What I want is a way that I can do this automatically ie. append queries without the user having to see the error message and click “YES” manually for the query. The user should be unaware of the append query and unaware that the item is being appended by running the query. All the user does is either to run the code / click the button that runs the code knowing that at the end of that the new item would be added to the respective raw materials append destination, in this case to the table which is later imported to an excel file.

How can I automate the clicking of “YES” button in the error message or something that does similar action?
Just to further explain what I have done is to run each of the raw materials query one after the other by using the DoCmd OpenQuery and DoCmd Close. In between I need to click the “YES” button to the error message. At present I have to click yes every time the queries run so say for eg I had 5 queries I / the user would click five times to “YES” button.
Thanks.

 

[cogent1]

Sorry It is taking so long to reply, but our time-zones don't coincide too well.

Sorry also to give you the lesson in elementary relationships, but I misunderstood both your level of competence and what you need to do.


You can override error messages smply by issuing the command

DoCmd.SetWarnings.False

The query will then run silently

DoCmd.SetWarnings.True

reverses the effect.

Of course, the query will not append any rows or any parts of a row that violate referential integrity anyway,even if the error message is suppressed. You may either have to remove referential integrity permanently (not recommended) or temporarily to achieve the desired result

 

[cogent1]

still puzzled after all this time...

Running these queries sequentially is very simple. Your button can call a function from a standard module. This sets the warnings false, ripples through your doCmd.Open Query commands and issues a message when done.

But, as a fully paid up member of the SlowOnTheUptake club, I still don't quite get what you mean

You wrote:

But now if I were to add a new finished product it will have to be selected by some of the raw material queries (being selected by the criteria of this query means that the finished product will be using the particular raw material) which then is to be updated into excel"

How exactly is a FP selected by the query? I understand that it is entered as a criterion, but how does the same finished product get to appear in multiple queries?

Append queries add rows to tables. What is in the row that's added?

Why are you getting key violations? This seems to imply that you are trying to add rows that violate referential integrity.

(to be contd)

 

[cogent1]

and this suggests that your relationships are not set up properly.

Sorry to nag you, because you know your own business best, but I still don't see why you need append queries if you have the structure I suggest. What you want to send to Excel is surely a FP and its associated Raw Materials. Excel then handles the apportionment of the RMs.

How do you currently send the data to excel? Why can't you update the rows in a form and then send it using a select query which uses the finished Material as a criterion?

Sorry if I'm missing the point....

 

[jua_kali]

Sorry for the late reply you sure are correct about the time difference not helping. I will try to explain what I am doing by giving examples hopefully that helps

I have a list of finished products with fields as follows:

No. Desc Form Dia Length
1 abc F1 2 5
2 def F2 3 7
3 rty F3 4.5 8
4 qwe F2 4 12

I have raw materials for eg.

RawMtr1
RawMtr2
RawMtr3

Now RawMtr1 is being used by Forms F2 and F3
RawMtr2 IS being used by Forms F2 and F1
RawMtr3 is being used by Forms F3 and F1

Therefore I now have a table for Finished Products which has the Item number as the primary key and form as the foreign key and a table for each raw material where I have a list of Forms that use the raw material. I create a query by using a join on form.

This will give me all the finished products that use the raw material so say for eg

RawMtr1 it will choose item no. 2, 3 and 4.
RawMtr2 will choose item 1, 2 and 4
RawMtr3 will choose item 1 and 3

For the above I have created an append query which appends it to a table. Say for eg.

Table-RM1 for RawMtr1
Table-RM2 for RawMtr2
Table-RM3 for RawMtr3

I have created an append query cause I use the get external data from excel which extracts the data from the TableRM1, 2 and 3. I could just extract the data from the query itself instead of appending to table and then extracting from it but the "get external data" in excel sometimes gives me an error with a complex query.

Note I have just shown a simple query here to try and explain but sometimes the actual query to filter the finished products are a bit complex and need to use union, etc etc.

The data from the table for each raw material table is extracted into an excel file. Here there are some calculations done just to give you an example.

Each of the the above raw materials is a group eg. RawMtr1 for eg is Plastic bags.

Now you have the following plastic bags:
PlasticBag1
PlasticBag2
PlasticBag3

In excel depending on the finished products parameters eg: Length and Dia for example will select the plastic bag. For eg.

PlasticBag 1 will be used for a finished product of
Dia :2 -3
Length:7-9
Qty will be 2

PlasticBag2 will be used for a finished product of
Dia : 4 - 4.5
Length : 8-10
Qty will be 1

So now finished product:

2 will have raw material, PlasticBag1 and its Qty = 2
3 will have raw material, PlasticBag2 and its Qty = 1

So you can see that I have to do this for each raw material in a seperate table and there after seperate excel file.
SOme raw materials will be 1 some will be more than one like the plastic bag example above.

So now if I add a new finished product with the following parameters:

6 ghj F2 4.3 9

you can see it will have PlasticBag2 and its Qty will be 1.

I hope you can now understand what I have done. I know it is not the best way to go about it but like I said I am in a learning process and this is the best solution I came up with.

If you have any suggestion I would greatly appreciate it. Thanks. I not yet tried your suggestions so bear with me I have been a bit caught up with other work. I will see how that works abd maybe that will do the trick from what I read I think it will work.

Thanks once again.