Macro problem - Re querying (1 Viewer)

Ceebee86

Registered User.
Local time
Today, 15:00
Joined
Sep 9, 2019
Messages
25
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:00
Joined
May 7, 2009
Messages
19,169
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.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 15:00
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 15:00
Joined
Sep 9, 2019
Messages
25
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

Registered User.
Local time
Today, 15:00
Joined
Sep 9, 2019
Messages
25
sorry...had to post the links like that to get around the 10 post minimum thing for links... :(
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:00
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 15:00
Joined
Sep 9, 2019
Messages
25
Didn't see that icon, think i need new glasses.. :)

Please find attached.
 

Attachments

  • Macro.PNG
    Macro.PNG
    10.3 KB · Views: 131
  • gui.PNG
    gui.PNG
    10.6 KB · Views: 131

theDBguy

I’m here to help
Staff member
Local time
Today, 15:00
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 15:00
Joined
Sep 9, 2019
Messages
25
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

I’m here to help
Staff member
Local time
Today, 15:00
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 15:00
Joined
Sep 9, 2019
Messages
25
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

Registered User.
Local time
Today, 15:00
Joined
Sep 9, 2019
Messages
25
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

I’m here to help
Staff member
Local time
Today, 15:00
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom