Skip Append Query if 0 Rows (1 Viewer)

dxz

New member
Local time
Today, 22:30
Joined
Nov 12, 2019
Messages
5
Hi All,

I am designing a macro which has several append queries. (About 30.)

All appends from Access go to an upload tool before being loaded to a database.

Because of this, each time the append rows is more than 0, a message box will need to display something along the lines of:

"Open transfer tool and upload appended data - do not click 'ok' until this has been done."

This gives you time to open the tool and push the data, then click 'ok' to continue with the macro.

I have tried using IF's:
Code:
DCount("*","Query1")>0
but this doesn't seem to work (i.e. it does not skip past when the result is 0) within the macro on append queries. (Works with queries.)

Note: I am doing this within the macro, not VBA.

TLDR: How can I make a macro skip an append query if update rows is 0?

Thanks in advance for your help and time!

Alex
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:30
Joined
Aug 30, 2003
Messages
36,118
I'd test in the macro, not in the query. Actually I'd use VBA, as would most of us here. I think you can test a DCount in a macro, you definitely can in VBA:

Code:
If DCount("*","Query1")>0 Then
  'your code here
End If
 

dxz

New member
Local time
Today, 22:30
Joined
Nov 12, 2019
Messages
5
Hi Paul,

Thank you for your reply. Sorry if I was not clear. I am testing within a macro but the DCount seems to only work with select queries - not append queries. *original post edited*.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 15:30
Joined
Oct 29, 2018
Messages
21,358
Hi Alex. Welcome to AWF!


What does "doesn't work" mean? Can you show us your macro code? Thanks.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:30
Joined
Aug 30, 2003
Messages
36,118
I'll get out of the way.
 

dxz

New member
Local time
Today, 22:30
Joined
Nov 12, 2019
Messages
5
Hi theDBguy,

Thank you for your reply.

The code is quite simple, basically several instances of the below code:


Code:
If DCount("*","Query1")>0
OpenQuery
Query Name Test
View Datasheet
Data Mode Edit

MessageBox
Message Open transfer tool and upload appended data - do not click 'ok' until this has been done
Beep Yes
Type None
Title Test

When used on regular queries the if statement works as expected, and the macro will successfully skip when there are no results to show.

I was hoping that there was some way that you can acheive something similar with append queries. Unfortunately due to the extra clicks involved in an append, you still get a message saying "You are about to append 0 row(s)".

I was hoping that it would skip the instances where there are 0 results.

The ultimate result is to have a macro with several append queries, that only alerts you when there are actual results to show. (And then action.)

I am not sure if this is possible, but any advice is much appreciated!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:30
Joined
Oct 29, 2018
Messages
21,358
Hi. It's very possible, we just need to fully understand what you're trying to do. Can you please post the SQL statement for one of your APPEND queries? Thanks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:30
Joined
Feb 19, 2013
Messages
16,553
the DCount seems to only work with tables - not append queries. *original post edited*.
it will also work with select queries but as you have surmised not action queries such as insert.

you could have two queries - a select query and an append query using the select query as it's 'source'
 

dxz

New member
Local time
Today, 22:30
Joined
Nov 12, 2019
Messages
5
Hi theDBguy,

The code is very long, but simplified it is as follows:

Code:
INSERT INTO dbo_TRANSFER_ALLC (Tableone)
SELECT dbo_TRANforALLC (Tableone.object)
FROM [QueryinAccess]


CJ_London has found a viable work around below:

it will also work with select queries but as you have surmised not action queries such as insert.

you could have two queries - a select query and an append query using the select query as it's 'source'

But if you have any other ideas I would be more than happy to hear them!
 

dxz

New member
Local time
Today, 22:30
Joined
Nov 12, 2019
Messages
5
Hi CJ_London - this is a viable workaround for me long term. Although the initial set up of tables would be a bit of a pain, this would allow me to run the macros as expected. Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:30
Joined
Oct 29, 2018
Messages
21,358
Hi theDBguy,

The code is very long, but simplified it is as follows:

Code:
INSERT INTO dbo_TRANSFER_ALLC (Tableone)
SELECT dbo_TRANforALLC (Tableone.object)
FROM [QueryinAccess]
Hi. Based on that, this should work.
Code:
If DCount("*","QueryinAccess")>0 Then
 

Users who are viewing this thread

Top Bottom