You are about to update xx rows (1 Viewer)

[David44Coder]

When this is generated can you know if advance if the number of rows is 0 and thus turn off Warnings Off?
And the error message about being cancelled.

 

[June7]

Exactly how are you running update action?

In VBA can use CurrentDb.Execute and won't get warnings.

 

[David44Coder]

DoCmd.RunSQL sql
I liked the warnings when the number was > 0. Can you know that anyhow ?

 

[June7]

Options:

1. possibly use DCount()

2. open a recordset and check its recordcount

 

[CJ_London]

Do you want to know the number before as a warning? or after as a report? If the latter you can use .execute rather than runsql

dim db as currentdb
db.execute sql
if db.recordsaffected<>0 then msgbox db.recordsaffected & " records affected"

the potential problem with getting the number before is it may not happen as the action query may not be able to update/append/delete all the records due to key or other violations

 

[Isaac]

@CJ_London wait can you really do that?? Declare "db" as...and then use the word that should be in the Set command? If so I never knew.

Or did you mean

dim db as dao.database
set db=currentdb
db.execute......

(Not intending to be picky, just wondering if I'd missed a cool functionality)

 

[Isaac]

@David44Coder
If you're using SQL Server as a back end or any other RDBMS that supports intricate transaction-related coding, you could leverage that to solve the problem.

 

[CJ_London]

My mistake , you are of course correct.

 

[Isaac]

It looks so much like the manner in which a SQL server variable can be instantiated + set that I wasn't sure. Thanks.

 

[NauticalGent]

@CJ_London wait can you really do that?? Declare "db" as...and then use the word that should be in the Set command? If so I never knew.

One habit I have developed, simply because I think it looks cleaner:

Dim db as DAO.Database : Set db = CurrentDb

 

[David44Coder]

Thanks for the ideas. Yes Junes plan would work fine but I liked CJs and had never come across .recordsaffected so used that.
I've error trapping so will see if a cannot update condition needs any changes.
Now I have what must be a silly question. Why use Set at all?
CurrentDb.Execute sql
Right or wrong, eliminating an instruction feels more efficient and no need to Set = Nothing. But I might be missing something.

 

[NauticalGent]

Thanks for the ideas. Yes Junes plan would work fine but I liked CJs and had never come across .recordsaffected so used that.
I've error trapping so will see if a cannot update condition needs any changes.
Now I have what must be a silly question. Why use Set at all?
CurrentDb.Execute sql
Right or wrong, eliminating an instruction feels more efficient and no need to Set = Nothing. But I might be missing something.

Have a read https://social.msdn.microsoft.com/F...et-a-database-object-variable?forum=accessdev