Update append queries?

Hi all

I have a database that stores customer records and their associated
insurance details. I need to be able to archive any changes made to
that record. Currently the user selects 'record change' whenever
anything is changed in the details. So I have created an append query
for the appropriate tables and stored the details in a new version of
the table called 'hsttablename'. This has worked well and I now have a
table with all the changes in it.

But what I want to do now is automate the query(s) to run each time the
user presses the submit button. Obviously rerunning the append query
gives me an error of primary key violation. Would it be possible to get
the append query just to update the new changes in one particular
record when the submit button was pressed?

Any help would be greatly appreciated

re: Update append queries?

dd_bdlm@yahoo.co.uk wrote:[color=blue]
> Hi all
>
> I have a database that stores customer records and their associated
> insurance details. I need to be able to archive any changes made to
> that record. Currently the user selects 'record change' whenever
> anything is changed in the details. So I have created an append query
> for the appropriate tables and stored the details in a new version of
> the table called 'hsttablename'. This has worked well and I now have[/color]
a[color=blue]
> table with all the changes in it.
>
> But what I want to do now is automate the query(s) to run each time[/color]
the[color=blue]
> user presses the submit button. Obviously rerunning the append query
> gives me an error of primary key violation. Would it be possible to[/color]
get[color=blue]
> the append query just to update the new changes in one particular
> record when the submit button was pressed?
>
> Any help would be greatly appreciated[/color]


I think that the problem is that you have autonumber in the archive
table. Change it to number only. Add a new field to your archive table
and make that autonumber and the primary key.

re: Update append queries?

Absolutely, But I wouldn't rely on the user to click on a link or abutton.
Access on its own does not have triggers for tables (dang). MSDE and SQL
Server and others do. Access' implementation of VBA forms, though, does
have events which behave like triggers. So, you can add your code to the
AfterUpdate event of a form and each time the form detects an update it will
run your code. As for SQL, it's an UPDATE statement and look in the help
files for how to write one.
--
Alan Webb
knoNOgeek@SPAMhotmail.com
"It's not IT, it's IS

<dd_bdlm@yahoo.co.uk> wrote in message
news:1112353106.939294.286350@f14g2000cwb.googlegr oups.com...[color=blue]
> Hi all
>
> I have a database that stores customer records and their associated
> insurance details. I need to be able to archive any changes made to
> that record. Currently the user selects 'record change' whenever
> anything is changed in the details. So I have created an append query
> for the appropriate tables and stored the details in a new version of
> the table called 'hsttablename'. This has worked well and I now have a
> table with all the changes in it.
>
> But what I want to do now is automate the query(s) to run each time the
> user presses the submit button. Obviously rerunning the append query
> gives me an error of primary key violation. Would it be possible to get
> the append query just to update the new changes in one particular
> record when the submit button was pressed?
>
> Any help would be greatly appreciated
>[/color]

re: Update append queries?

> Hi all[color=blue]
>
> I have a database that stores customer records and their associated
> insurance details. I need to be able to archive any changes made to
> that record. Currently the user selects 'record change' whenever
> anything is changed in the details. So I have created an append query
> for the appropriate tables and stored the details in a new version of
> the table called 'hsttablename'. This has worked well and I now have a
> table with all the changes in it.
>
> But what I want to do now is automate the query(s) to run each time the
> user presses the submit button. Obviously rerunning the append query
> gives me an error of primary key violation. Would it be possible to get
> the append query just to update the new changes in one particular
> record when the submit button was pressed?
>
> Any help would be greatly appreciated[/color]

Go to Macros
Select new
OpenQuery
Write the query name

re: Update append queries?

Did you read this article yet?
http://www.allenbrowne.com/AppAudit.html

re: Update append queries?

Alan Webb wrote:[color=blue]
> Absolutely, But I wouldn't rely on the user to click on a link or[/color]
abutton.[color=blue]
> Access on its own does not have triggers for tables (dang). MSDE and[/color]
SQL[color=blue]
> Server and others do. Access' implementation of VBA forms, though,[/color]
does[color=blue]
> have events which behave like triggers. So, you can add your code to[/color]
the[color=blue]
> AfterUpdate event of a form and each time the form detects an update[/color]
it will[color=blue]
> run your code. As for SQL, it's an UPDATE statement and look in the[/color]
help[color=blue]
> files for how to write one.
> --
> Alan Webb
> knoNOgeek@SPAMhotmail.com
> "It's not IT, it's IS[/color]

Thanks for this Alan. I have now added the code to the AfterUpdate
event of my forms which is performing the desired action. However it is
still attempting to update all the 'changed' records (user selects
'change' when its an update and I have a query that filters on that and
has appended them to a seperate table). Is there anyway to get it to
just update the *current* record? I cant figure out the use of the
update ststement. Many thanks for any help you can provide. I feel I am
almost there!
DD

re: Update append queries?

dd_bdlm,
Knowledge is power, learning SQL increases your knowledge, Increased
knowledge increases your power, women are attracted to power, learning SQL
makes you more attractive, learning SQL will improve your sex life. It's an
almost valid logical argument. Now, to the point--try something like this:
"UPDATE CUSTOMER_TBL SET ACCOUNT_NO=""" & strNewAccountNo & """ WHERE
CUSTOMER_TBL.ID=" & Me.txtCustomerID & ";". The key bit of SQL is the WHERE
clause. You would write the update statement so it only changed the row
identified by your primary key. In my example is is CUSTOMER_TBL.ID.
--
Alan Webb
knoNOgeek@SPAMhotmail.com
"It's not IT, it's IS

<dd_bdlm@yahoo.co.uk> wrote in message
news:1112604992.485386.123780@f14g2000cwb.googlegr oups.com...[color=blue]
>
> Alan Webb wrote:[color=green]
>> Absolutely, But I wouldn't rely on the user to click on a link or[/color]
> abutton.[color=green]
>> Access on its own does not have triggers for tables (dang). MSDE and[/color]
> SQL[color=green]
>> Server and others do. Access' implementation of VBA forms, though,[/color]
> does[color=green]
>> have events which behave like triggers. So, you can add your code to[/color]
> the[color=green]
>> AfterUpdate event of a form and each time the form detects an update[/color]
> it will[color=green]
>> run your code. As for SQL, it's an UPDATE statement and look in the[/color]
> help[color=green]
>> files for how to write one.
>> --
>> Alan Webb
>> knoNOgeek@SPAMhotmail.com
>> "It's not IT, it's IS[/color]
>
> Thanks for this Alan. I have now added the code to the AfterUpdate
> event of my forms which is performing the desired action. However it is
> still attempting to update all the 'changed' records (user selects
> 'change' when its an update and I have a query that filters on that and
> has appended them to a seperate table). Is there anyway to get it to
> just update the *current* record? I cant figure out the use of the
> update ststement. Many thanks for any help you can provide. I feel I am
> almost there!
> DD
>[/color]