Append Query

an update query would be the way to go ...

Billystyx

UPDATE Table1 SET Table1.stockitem= "111"
WHERE (((Table1.stockitem)="222"));

Billystyx

You could also do an unmatched append query.
The wizard will talk you through this when you select to create a new query.

:-)

OK - thanks both for the help Q to both of you for further advice

Billystyx can you tell me where I put your coding? is it in the criteria field any help and further explanation please


dannywareham - I cannot find how the wizard helps me as it does not ask anything about unmatched - so how do I get to the unmatched wizard Q

regards Dave

You can paste this sql directly in the sql view screen of the query editor, but it won't give you any proper query until you adjust the values to suit your tables and fields.

In the query editor, set the table to the one you want to update, and select update query from the query menu at the top. The format should make sense and you can edit it all inside the query editor, without having to use the sql view at all.

Billystyx

ok I will try it - thanks for the immediate reply - regards Dave

I have tried the coding below - can you see any problems - as I cannot get it to work - as you can see I have substitued my table names - regards Dave

UPDATE [Stock Name File used as list for drop down], [Stock In and Out] SET [Stock Name File used as list for drop down].[Stock Name] = "111"
WHERE ((([Stock In and Out].[Stock Name])="222"));

which is the table you are adding records to, and which is the table you want the first table's records added to?
I am guessing now you want to add some records to one table, and then append records to the second, or update existing records if the data is different in the second table than in the first. Is this correct?
Billystyx

Yes I wish to add stock items to a table (no probs) which is the basis of the drop down in Stock Name Files used as list for drop down- this table then updates the Stock In and Stock Out table with the new stock items. Once I know how to do this I will wish to update 2 other tables as well but I am going one step at a time at the moment.

Dave

so it is an append, you don't want the same values in twice do you?
So do an append query - it won't add records where the pk is duplicated.

What you need to do is add an id field to the stockname files table, set it as a primary key with no duplicates.
Now add an ID field to the second table - stockin and out, with the same name, but this time as a number. Not as a primary key but with it set to no duplicates allowed.

Then just create a simpe append query. If there is a conflict of IDs (ie you are trying to add one to stock in and out where the id field is the same, it will not do it. It will append the other new records, but not the duplicate.

Billystyx

You can see the append query in the query editor go to query->append query, and it will ask you which table to append to.

Or you can do with sql like
insert into stockinandout select * from stocknamefiles

Billystyx

OK I have tried this on a completely new tables and after the first data is appended I get the error message conflict of data types - is this correct? it appears to work if I say proceed anyway - but I do not want the conflict error message appearing each time  - Dave

This means that the tables have different datatypes for the same fields. Which is not a good idea but will work if it doesn't have any problem values, for example between text and number.
You need to go into the appending to table and check all the datattypes and then see which ones don't match with those in the first table and change them.

Billystyx

OK well I am now geting the following ERROR MESSAGE

Access set 0 fields to null due to type conversions failure and it didnt add 4 records to the table due to key violations, 0 records due to lock violations and 0 records due to validation rule vilolations -

 if I carry on any way it appears to work - any comments Dave

Yes that is fine. If you are doing it through code then
docmd.SetWarnings false
...before the sql and

docmd.SetWarnings true

should stop the message appearing. But it is doing exactly what you want it to do  - not allowing duplicate records.

Billystyx

OK - thanks but need help on the following - I have it working at the moment with the error message

1st Q I have had to change the Additional ID field on both tables to an AutoNumber otherwise I had to manually put a number in this field - or I got the message Null value not allowed - is this a problem OR have I not followed your idea correctly with regard to the additional NO DUPLICATES filed?

The sql to remove the warning messages is causing me a problem - I have gone into the append query and gone to sql view - is this where you intended me to put the DoCmd.SetWarnings as it does not like it and my sql experience is almost zero.

So where do I put the sql code

Thanks for being patient on this one but we are almost there I am sure -

Best regards Dave

docmd.setwarning is vba code, soyou would need to execute this code in vba - under the loadevent of a form for example. (Depends on where and when you need it to happen).

try this
'not sure if you need the warnings here though
docmd.setwarnings false
strSQL="insert into stockinandout select * from stocknamefiles;"
currentdb.execute(strSQL)
docmd.setwarnings false

Is there some other field of each form where the id fields match up - are the same on each table? If so use that id field and and don't add a new.
If they run in order on both tables but don't have any other id field, then set them to autonumber, and then change the 'many' table to number once it has add the autonumber, and not a pk.

This may be the trickiest part...
How do you know they are duplciates by looking at the table? What fields tell you this?

Billystyx

OK about vba code

I am only working using tables at the moment - I have only just started on the Stock Control Prog and am trying to get the basic concept working first with stock in and out and updating my other tables -

I have three tables which are Current Stock - Previous Stock - Stock in & out

I have qures and macros working which when I put stock into alread entered stock names all the tables are updated - no problem

I then came to entering new stock names into the file Stock In & Out by way of a drop down based on a stock names etc table - so far so good

Problem is that when I try and update a new name into the other tables the data does not go accross unless I put the name into the table first - this brings us to the present situation of updating the other tables and getting rid of duplicates.

Now what I have done so far may be the incorrect approach but its working and I understand what I have done.

All the tables have a common field called Stock Name - I think this should help (your latest comment)

I am wondering - hence my explanation above that maybe you think I am further in than just tables

I feel that this is now a 250 point value question and have increased accordingly as I really do need the help

Dave

I then moved to the point of entering new stock items and found that my

can you elaborate on this?
>>unless I put the name into the table first - this brings us to the present situation of updating the other tables and getting rid of duplicates

as far as the vba code goes - paste it into a new module (goto modules->new)
put this in there

function myFunction
docmd.setwarnings false
strSQL="insert into stockinandout select * from stocknamefiles;"
currentdb.execute(strSQL)
docmd.setwarnings false
end function

you can run this code by going to the immediate window whilst in the module (view->immediate window)
and typing
?myFunction

and then pressing return
You will need to replace the names of the tab;les with the correct names first though

Billystyx

OK b4 elaboration - I must admit that some of what you are telling me is not familier to me and I do not understand all - OK nor for the elaboration.

I have 3 tables CURRENT STOCK

SORRY

PREVIOUS STOCK
STOCK IN STOCK OUT

I have 3 queries which update the 1st 2 tables from the stock in stock out table - the last query sets the stock in stock out back to zero in readiness for the next increase or decrease in stock.

In readiness for when I put all this into a forem I have a marco which runs all 3 queries - so thats all woking fine.

Now for the elaboration bit - when I put a NEW stock NAME  in the stock in stock out table my macro (3 queries) does not include the new name or any data attached on that line.

The only way I can get it to work correctly is by manually putting the new stock name into the 1st 2 tables -

This is where the append query Q started - I need to append any new names in the Stock in Stock out table to the 1st 2 tables when they are there I can run my update stock macro and job done.

But I only want to append the new stock names and this is where we started.

Modules OK - had a read up on Modules and have a brief understanding of what they do - but as yet I have not used them -so if I put your code into a module you have told me how to run it - but how do I run it automatically? is it similar to a macro?

Oh by the way are you in the UK - I am in Wisbech Cambs UK - sheers Dave
 

Yes, I am in the UK. Perhaps if you post the db or relevant part of it I could look at it to see what you mean. If you cannot post, you can send it to my email(please include the ee link though), but you must also send the db sample to anyone else who wishes to help too.

Billystyx

OK - last night I sent it to your email address as an attachement - Q did you get it - Q how do I send an ee link? -Dave

Yes I will also sent to others if I get offers of help Dave

OK - last night I sent it to your email address as an attachement - Q did you get it - Q how do I send an ee link? -Dave

Yes I will also sent to others if I get offers of help Dave

I have made a few changes here, but I'm not sure if it is what you are after. I have set up some relationships between thentables, I also added a form to illustrate what can be done - It has a save button (for adding new records only), the data is updated to the current stock table, and saved directly into the stock in and out table.
I could not see a reason for the lookup table on it s own, but if it is necessary it is easy enough to change.

By going into form1 and adding a new record and then clicking on the save record button, you will see that it saves the record to the stockin and out table, and to the current stock table.

You cannot add duplicate records to the stockinandout table, so you will not end up with duplicate data in either of the other either, because the records cascade updates and deletes from the stockinandout table.

Take a look at it and if it is not what you are looking for please explain what is wrong, or (at worst) what is right.

The code I placed on the save button was as follows:

On Error GoTo Err_Command7_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
mySQLstr = "insert into [Current Stock]([Stock Name],StockID) select [Stock In And Out].[Stock Name],[Stock In And Out].StockID from [Stock In And Out];"
CurrentDb.Execute mySQLstr
Exit_Command7_Click:
    Exit Sub

Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click

Billystyx
Hope we are getting somewhere:)