Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-12-2019, 07:25 AM   #1
dxz
Newly Registered User
 
Join Date: Nov 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
dxz is on a distinguished road
Skip Append Query if 0 Rows

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 by dxz; 11-13-2019 at 01:03 AM.
dxz is offline   Reply With Quote
Old 11-12-2019, 07:35 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,383
Thanks: 13
Thanked 4,122 Times in 4,054 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Skip Append Query if 0 Rows

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
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-12-2019, 07:40 AM   #3
dxz
Newly Registered User
 
Join Date: Nov 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
dxz is on a distinguished road
Re: Skip Append Query if 0 Rows

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 by dxz; 11-13-2019 at 09:17 AM.
dxz is offline   Reply With Quote
Old 11-12-2019, 07:40 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,538
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Skip Append Query if 0 Rows

Hi Alex. Welcome to AWF!


What does "doesn't work" mean? Can you show us your macro code? Thanks.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-12-2019, 07:42 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,383
Thanks: 13
Thanked 4,122 Times in 4,054 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Skip Append Query if 0 Rows

I'll get out of the way.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-13-2019, 01:02 AM   #6
dxz
Newly Registered User
 
Join Date: Nov 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
dxz is on a distinguished road
Re: Skip Append Query if 0 Rows

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!
dxz is offline   Reply With Quote
Old 11-13-2019, 08:09 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,538
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Skip Append Query if 0 Rows

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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-13-2019, 08:30 AM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,344
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Skip Append Query if 0 Rows

Quote:
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'
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
dxz (11-13-2019)
Old 11-13-2019, 09:15 AM   #9
dxz
Newly Registered User
 
Join Date: Nov 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
dxz is on a distinguished road
Re: Skip Append Query if 0 Rows

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:

Quote:
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 is offline   Reply With Quote
Old 11-13-2019, 09:17 AM   #10
dxz
Newly Registered User
 
Join Date: Nov 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
dxz is on a distinguished road
Re: Skip Append Query if 0 Rows

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!
dxz is offline   Reply With Quote
Old 11-13-2019, 09:20 AM   #11
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,538
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Skip Append Query if 0 Rows

Quote:
Originally Posted by dxz View Post
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

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Append and also skip fields with certain text? kb44 Queries 3 01-31-2019 08:25 AM
Append Query - too many rows allredkj Queries 4 07-25-2012 03:52 PM
Insert rows skip duplicates toka Queries 2 12-29-2006 04:43 AM
Append query to skip blank fileds??? King_kamikaze Queries 3 03-20-2005 09:19 AM
Creating an Append Query that can SKIP blank (null) fields pmcleod Queries 2 01-19-2004 12:55 PM




All times are GMT -8. The time now is 02:24 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World