Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-09-2019, 06:38 AM   #1
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 25
Thanks: 5
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Macro problem - Re querying

Hi Access world!

I'm a new user, and a bit of an Access novice. I've created a form, and on this form there's a side bar. It contains two radio buttons, a list of hyper links, and a sub form that displays data.

When you select one of the radio buttons (Active or Not Active) and click one of the hyper links. An embedded Macro looks at which radio button has been selected, and then runs a Filter on the sub form, depending on the value of the radio button box. This all works quite well.

Now...

If I have selected Active, clicked a link to display Active data, and it displays....But now, I click the 'Not active' radio button.

How do I get THAT event of clicking not active, to change the Filter to its opposite brother, and refresh the sub form query?

Ceebee86 is offline   Reply With Quote
Old 09-09-2019, 06:44 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Macro problem - Re querying

use OptionGroup and create two radio buttons.
on your macro test if OptionButton = 1 or 2, whether on OptionButton wizard you elect 1 as Active or Not active.
__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 09-09-2019 at 06:50 AM.
arnelgp is offline   Reply With Quote
Old 09-09-2019, 06:45 AM   #3
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,570
Thanks: 58
Thanked 1,437 Times in 1,418 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Macro problem - Re querying

Hi. Welcome to AWF! Sounds like you could try executing a macro to do the same thing as the hyperlink macro does. Can you show us the macro for them? 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 09-09-2019, 07:20 AM   #4
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 25
Thanks: 5
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Macro problem - Re querying

Hi!

Thanks for your replies!

I have 8 hyper links..

Agent, consignee, customer, Haulier, Invoice party, Manufacturer, Shipper, Shipping line, all with a variation of the Filter's seen in this picture.



Macro
https://imgur.com/a/FZ5pN3R"]https://imgur.com/a/FZ5pN3R

Form
https://imgur.com/LpEbnYD"]https://imgur.com/LpEbnYD
Ceebee86 is offline   Reply With Quote
Old 09-09-2019, 07:22 AM   #5
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 25
Thanks: 5
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Macro problem - Re querying

sorry...had to post the links like that to get around the 10 post minimum thing for links...
Ceebee86 is offline   Reply With Quote
Old 09-09-2019, 07:29 AM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,570
Thanks: 58
Thanked 1,437 Times in 1,418 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Macro problem - Re querying

Quote:
Originally Posted by Ceebee86 View Post
sorry...had to post the links like that to get around the 10 post minimum thing for links...
Were you not able to attach the images here? Just curious...
__________________
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 09-09-2019, 07:32 AM   #7
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 25
Thanks: 5
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Macro problem - Re querying

Didn't see that icon, think i need new glasses..

Please find attached.
Attached Images
File Type: png Macro.PNG (10.3 KB, 6 views)
File Type: png gui.PNG (10.6 KB, 3 views)

Ceebee86 is offline   Reply With Quote
Old 09-09-2019, 07:33 AM   #8
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,570
Thanks: 58
Thanked 1,437 Times in 1,418 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Macro problem - Re querying

Quote:
Originally Posted by Ceebee86 View Post
Hi!

Thanks for your replies!

I have 8 hyper links..

Agent, consignee, customer, Haulier, Invoice party, Manufacturer, Shipper, Shipping line, all with a variation of the Filter's seen in this picture.



Macro
https://imgur.com/a/FZ5pN3R"]https://imgur.com/a/FZ5pN3R

Form
https://imgur.com/LpEbnYD"]https://imgur.com/LpEbnYD
Okay, one idea is to check the filter applied, if any, and then replace it with the opposite. For example, pseudo macro code:


1. Check if filter is applied
2. If not, do nothing
3. If there's filter, store the filtername in a temp variable
4. Search the filter name for the word "Not"
5. If exists, delete it, or replace "NotActive" with "Active"
6. If not exists, replace "Active" with "NotActive"
7. Reapply new filter using the temp variable


Hope it helps...
__________________
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 09-09-2019, 07:40 AM   #9
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 25
Thanks: 5
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Macro problem - Re querying

Hi DBguy,

I have been scouring the internet on how to identify what filter is in place, but I am coming up empty handed so far.

What Option should I be selecting to determine if a Filter is in place?

Where and on what event would you recommend using this?
Ceebee86 is offline   Reply With Quote
Old 09-09-2019, 07:50 AM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,570
Thanks: 58
Thanked 1,437 Times in 1,418 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Macro problem - Re querying

Quote:
Originally Posted by Ceebee86 View Post
Hi DBguy,

I have been scouring the internet on how to identify what filter is in place, but I am coming up empty handed so far.

What Option should I be selecting to determine if a Filter is in place?

Where and on what event would you recommend using this?
Hi. As for the event, I would suggest the AfterUpdate event of the radio button or Option Group. I don't use macros much, so using VBA, I would check for a Filter with something like:
Code:
strFilter = Me.Filter
or
Code:
If Me.Filter<>"" Then
Hope it helps...
__________________
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 09-09-2019, 08:37 PM   #11
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 25
Thanks: 5
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Macro problem - Re querying

Quote:
Originally Posted by theDBguy View Post
Okay, one idea is to check the filter applied, if any, and then replace it with the opposite. For example, pseudo macro code:


1. Check if filter is applied
2. If not, do nothing
3. If there's filter, store the filtername in a temp variable
4. Search the filter name for the word "Not"
5. If exists, delete it, or replace "NotActive" with "Active"
6. If not exists, replace "Active" with "NotActive"
7. Reapply new filter using the temp variable


Hope it helps...
Am I right in thinking [FilterOn] is the correct variable to search against for the filtername?

Should I be using a regular expression to extract 'Active' and 'NotActive'?
Ceebee86 is offline   Reply With Quote
Old 09-10-2019, 01:09 AM   #12
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 25
Thanks: 5
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Macro problem - Re querying

Quote:
Originally Posted by Ceebee86 View Post
Am I right in thinking [FilterOn] is the correct variable to search against for the filtername?

Should I be using a regular expression to extract 'Active' and 'NotActive'?
Hi DBguy, I cracked it by going full vba route, and having two subforms rather than one placed in a tab control box!
Ceebee86 is offline   Reply With Quote
Old 09-10-2019, 01:45 PM   #13
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,570
Thanks: 58
Thanked 1,437 Times in 1,418 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Macro problem - Re querying

Quote:
Originally Posted by Ceebee86 View Post
Hi DBguy, I cracked it by going full vba route, and having two subforms rather than one placed in a tab control box!
Hi. Glad to hear you got it sorted out. I would have used VBA too. Good luck with your project.

__________________
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

Tags
macro actions , macro builder

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Question? Querying a table to create a new report smelff Queries 1 04-15-2010 11:09 AM
querying SQL server - a problem jay_l_a Queries 4 09-10-2008 10:52 AM
A Very Odd Querying Problem Mithander Queries 8 07-07-2008 05:29 AM
querying an empty date field with IsNull problem alant37 Queries 4 06-16-2005 02:37 AM
Querying with Date Format Problem (Again) Ally Queries 6 06-19-2002 01:44 AM




All times are GMT -8. The time now is 10:47 PM.


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