Microsoft Access help

  Paranoid Android 00:03 23 Oct 2004

Here's one to warm you up on the cold wintery nights - I'll try to keep it fairly simple.

I have an Access form with a button that, On Click, calls an Event Procedure. The Event Procedure runs a Macro (DoCmd.RunMacro)

The macro calls up a filtered query using the ApplyFilter property, where the filter criteria are applied using Like "*"&[Enter name to search]&"*" , which filters the results of the form according to the name entered in a dialog box by the user.

The Event Procedure then goes on to hide certain buttons, show others etc.

All Ok so far.

However, if a user hits the search button, then instead of entering a name they hit Cancel, the Macro does not run (that's OK) but the remaining items in the Event Procedure are carried out, resulting in all the wrong buttons being hidden.

How can I stop the Event Procedure running if the user cancels out of the macro?


  Paranoid Android 09:12 23 Oct 2004

Please? :)

  AccessMoron 14:10 23 Oct 2004

This is a limitation of macros. one way round this is to do the filtering from within the form.

The following code will do it for you. You will need
1. text box called txtFilter
2. Toggle button called cmdFilter


Private Sub cmdFilter_Click()

Select Case cmdFilter

'Find the state of the togglr button

Case True

'Check that the user has entered some text into the text box

If Trim(" " & txtFilter) = "" Then

MsgBox "Please enter a name to filter by", vbInformation, "Sys Admin"

'the havent so turn the toggle button off

cmdFilter = False


'Set up thre filter

Me.Filter = "FieldName like '*" & txtFilter & "*'"

cmdFilter.Caption = "Filter On"

'Turn the filter on

Me.FilterOn = True

End If

Case False

cmdFilter.Caption = "Filter Off"

'Trun the filter off

Me.FilterOn = False

End Select

'What ever has happened turn the command buttons on or off. The not statement just means that we are reversing the current setting of the togglr button.

HideButtons Not cmdFilter

End Sub

Private Sub Form_Load()

cmdFilter = False


End Sub

Sub HideButtons(blnStatus As Boolean)

cmdOne.Visible = blnStatus

End Sub

  Paranoid Android 20:18 24 Oct 2004

Thank you, I'll give it a try.


  Paranoid Android 20:51 24 Oct 2004

OK, I have implemented the code and I am getting a VB runtime error 438, 'object doesn't support this property or method'. The debugger points to the second line of code ie Select Case cmdFilter

Unfortunately I am now in completely unknown territory, so any help would be great.



  AccessMoron 09:45 25 Oct 2004

you have a toggle button called cmdFilter?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

Iconic New York graphic designer Milton Glaser on his uplifting new subway posters

New iMac Pro release date, UK price & specs rumours

Comment suivre le parcours du père Noël ?