Running an Update query before closing a form

[randria]

Hello everyone,

I have form ( record sourece table1)that has a control (ctr1) whose value I
need to update to a field in a different table ( table2). I have a command
button that I have to click everytime before closing the form to run an
update query so that the current value of ctr1 is copied to the field in
table2.
- It works fine when new records were posted. But when previous records were
changed or deleted, it gives an error message ( the db couldn't run the
update query and couldn t save all records because of various violations
.....) .
2 questions:
1-is it possible to remove the command button and programmatically run
the update query before closing ? I have not written an SQL in codes. I
tried to copy the SQL from SQL View but I get errors.

2- What should I do before running the update query in a situation when
the users changed or deleted the records ?

Many thanks.

 

[Maurice]

You can replace the button for code. In the close event of the form you can
place the following command:

Docmd.openquery [name of your updatequery here]

if you don't want the message to appear code it as follows

Docmd.SetWarnings false
Docmd.openquery [name of your updatequery here]
Docmd.setwarnings true

better yet if you want to trap any errors try the following

currentdb.execute [name of your updatequery here], dbfailonerror

If there is an error the query just won't execute.

hth

 

[randria]

Hello Maurice,

Many thanks for your help, it is working perfectlly. The only part that I
could not fit in is the trapping of errors:
currentdb.execute [Query name],dbfailonerror-------> giving Run-time
error '3061' : ( too few parameters. Expected2.)

But without it, it works perfectly.
Many thanks.

You can replace the button for code. In the close event of the form you can
place the following command:

Docmd.openquery [name of your updatequery here]

if you don't want the message to appear code it as follows

Docmd.SetWarnings false
Docmd.openquery [name of your updatequery here]
Docmd.setwarnings true

better yet if you want to trap any errors try the following

currentdb.execute [name of your updatequery here], dbfailonerror

If there is an error the query just won't execute.

hth
--
Maurice Ausum

Hello everyone,

I have form ( record sourece table1)that has a control (ctr1) whose value I
need to update to a field in a different table ( table2). I have a command
button that I have to click everytime before closing the form to run an
update query so that the current value of ctr1 is copied to the field in
table2.
- It works fine when new records were posted. But when previous records were
changed or deleted, it gives an error message ( the db couldn't run the
update query and couldn t save all records because of various violations
....) .
2 questions:
1-is it possible to remove the command button and programmatically run
the update query before closing ? I have not written an SQL in codes. I
tried to copy the SQL from SQL View but I get errors.

2- What should I do before running the update query in a situation when
the users changed or deleted the records ?

Many thanks.