docmd.runsql update

[Mitch_Aus]

Hi Guys;


I have a database with 2 tables. The first table called "shots" has a field "shotstodate" that is updated by an import from a master database. i open the second the second table by a button on a menu, when i click the open button i would like all the data in the table "shots", with the field called "shotstodate" to be automatically updated to the second table called "diemaster" into the field "shotstodate".

I cant get my head around it, i know i need to use the DoCmd.RunSQL update but what do i do next, can someone please help me. I have goggled by i still can't work it out.

I don't want to use a query, i would just lie the entire field updated every time i open the form.

Thanks

kevin

[ssanfu]

Hi Kevin,

If I understand correctly, you want to take *ALL* of the records in the table "Shots" for one field "shotstodate" and append them into a table named "diemaster" in a field named "shotstodate".

To do this, a query would look like this:

INSERT INTO diemaster ( shotstodate )
SELECT shots.shotstodate
FROM shots;


Using code, you can do it using one line using an append query:

Public Sub Shots()
   CurrentDb.Execute "INSERT INTO diemaster ( shotstodate ) SELECT shots.shotstodate FROM shots;", dbFailOnError
End Sub

[TheShabz]

I dont think an insert-into would work here because, as I understand it, he is looking to update the number of shots taken per primary key. so if until yesterday, key 123 took 50 shots, and today took 3 more, the shotstodate should show 53 for key 123. what you would want is an update query:

UPDATE diemaster
SET shotstodate = shots.shotstodate
WHERE shotstodate.PrimaryKey = shots.PrimaryKey