Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Macros (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=11)
-   -   Macro problem - Re querying (https://www.access-programmers.co.uk/forums/showthread.php?t=306764)

Ceebee86 09-09-2019 06:38 AM

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?

arnelgp 09-09-2019 06:44 AM

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.

theDBguy 09-09-2019 06:45 AM

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.

Ceebee86 09-09-2019 07:20 AM

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 09-09-2019 07:22 AM

Re: Macro problem - Re querying
 
sorry...had to post the links like that to get around the 10 post minimum thing for links... :(

theDBguy 09-09-2019 07:29 AM

Re: Macro problem - Re querying
 
Quote:

Originally Posted by Ceebee86 (Post 1639433)
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...

Ceebee86 09-09-2019 07:32 AM

Re: Macro problem - Re querying
 
2 Attachment(s)
Didn't see that icon, think i need new glasses.. :)

Please find attached.

theDBguy 09-09-2019 07:33 AM

Re: Macro problem - Re querying
 
Quote:

Originally Posted by Ceebee86 (Post 1639432)
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...

Ceebee86 09-09-2019 07:40 AM

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?

theDBguy 09-09-2019 07:50 AM

Re: Macro problem - Re querying
 
Quote:

Originally Posted by Ceebee86 (Post 1639442)
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...

Ceebee86 09-09-2019 08:37 PM

Re: Macro problem - Re querying
 
Quote:

Originally Posted by theDBguy (Post 1639438)
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 09-10-2019 01:09 AM

Re: Macro problem - Re querying
 
Quote:

Originally Posted by Ceebee86 (Post 1639551)
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!

theDBguy 09-10-2019 01:45 PM

Re: Macro problem - Re querying
 
Quote:

Originally Posted by Ceebee86 (Post 1639563)
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.


All times are GMT -8. The time now is 09:27 AM.

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