Apply Filter macro crashing Access

[got_access:]]

I'm playing with macros for the first time.

I would like to create a split form based on a query, and have a text field in the top of the form - in which I could enter a search string - which would be used in the where clause of the query.

I thought I could do this by using an on enter event in the text field and have it start an ApplyFilter macro. But every time I try to do this - Access crashes.

I am however able to create macros that do things like open forms and message boxes.

Any thoughts on why its crashing?

And does anyone know of a step-by-step resource on macros in Access 2016?

Thanks!

 

[theDBguy]

Hi. First question, why macros? As for suggestions, I'd say try using the AfterUpdate event instead.

 

[got_access:]]

Hi. First question, why macros? As for suggestions, I'd say try using the AfterUpdate event instead.

Sounds interesting - where would I go after starting the "AfterUpdate" event option?
Its going to want a maro, expression builder, or code builder.

Thanks

 

[theDBguy]

Hi. I would use the code builder, but you could still try the macro builder. We’re just trying to find out if using a different event will not crash Access.

 

[got_access:]]

Oh I see -
Well I just tried the "AfterUpdate" event also and it crashed.

With the code builder, are you referring to VBA?

 

[got_access:]]

I have another form that I have this functionality in.
In that one I do use an "AfterUpdate" event - and have in an [event procedure]
And the VBA code is pretty simple

Private Sub txtSearchbox_AfterUpdate()
Me.Refresh
End Sub

And this works.
But I was wondering if there were a way to do it with a macro

 

[theDBguy]

Hi. I'm sure there is a way to do the same thing with macros. Isn't there a Refresh or Requery action? What exactly does your macro code look like now?

 

[got_access:]]

I'm not familiar enough with Access macros to know much more beyond what I've done already.

And the system won't create any macro code at all - the system crashes and won't save it - so I don't see any macro code either.

I have a suspicion I'm asking to do something that is invalid.

 

[theDBguy]

Hi. If a macro doesn't work and VBA does, can you just use VBA? Otherwise, you'll have to post a copy of your db, so we can see why macros don't work.

 

[got_access:]]

Thanks, I wouldn't want to go to that trouble.
I'm just playing to get myself familiar with macros.

If however you do think of any resource on macros in Access 2016 I would love to look at it. I have a small kindle book on it but that has one or two macros at the most in it. And so it will not be comprehensive enough. And also I understand that macro functionality has changed over different versions.

It may be that the major changes were made in Access 2013 though.

 

[theDBguy]

Okay, but going back to my first question, if you already know VBA, why are you now interested in using macros? Are you planning on doing something with your database that macros are required? Just curious...

 

[Gasman]

FWIW I managed to do just this with a macro in 2007.
Haven't worked out how to remove the filter though.

I'd rather use VBA as 2007 macroes are quite basic. They improved a lot with conditions with later versions.

 

[got_access:]]

I'm familiar with a few different languages and have used VBA in excel for a number of years.

But I've never done anything with macros in Access before, and I would really like to have some hands-on experience. That's why I was hoping to find a book on it. But even the Access 2016 Bible doesn't go into macros much beyond an introduction.

 

[theDBguy]

But even the Access 2016 Bible doesn't go into macros much beyond an introduction.

I think that should say a lot about using macros in Access. I'll see if I can find other resources for you.

 

[got_access:]]

That's very kind!

Thank you theDBguy!

 

[got_access:]]

FWIW I managed to do just this with a macro in 2007.
Haven't worked out how to remove the filter though.

I'd rather use VBA as 2007 macroes are quite basic. They improved a lot with conditions with later versions.

Hey Gasman,
What kind of form did you use it in?
I have a suspicion the ApplyFIlter can only be used in a datasheet form.

 

[Gasman]

Split form, as that is what you said you were using, and split forms have a few strange restrictions.

Unbound control on the non datasheet part of the form.
Code in the AfterUpdate event.

 

[got_access:]]

Split form, as that is what you said you were using, and split forms have a few strange restrictions.

Unbound control on the non datasheet part of the form.
Code in the AfterUpdate event.

Interesting! And thanks.
Well that is what I did try alright - but with Access continuing to crash.
I'll keep tinkering around with it to see if I can't get it to work.
Thanks for that!