Link to home
Start Free TrialLog in
Avatar of msmerry
msmerryFlag for Australia

asked on

Event Procedures not working

I have a form which has alphabet buttons to allow filtering.
The following code is attached:-

Private Sub ApplyACustomFilter(strFilter As String)
    If IsDebugMode = 0 Then On Error GoTo ApplyACustomFilter_Error
    Me.[AccountsOutstandingsubfrm].Form.Filter = strFilter
    Me.[AccountsOutstandingsubfrm].Form.FilterOn = True
'    DoCmd.ApplyFilter "", strFilter
'    Me.cmdShowAll.Visible = True

ApplyACustomFilter_Exit:
 Exit Sub

ApplyACustomFilter_Error:
    Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "ApplyACustomFilter")
    Resume ApplyACustomFilter_Exit
End Sub


and each letter has a variant of this code attached:-

Private Sub Cmd_A_Click()
    Call ApplyACustomFilter("([AccountsNotPaidQry].[contactcode] Like ""A*"")")
 
End Sub


When I check the Event, all looks right.  Each letter has the corret details

These all worked perfectly last week, but don't this week.
Any advice would be greatly appreciated
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

VBA, but what are your programming in?

Jim.
Avatar of msmerry

ASKER

Sorry, I missed the most basic information.
I am working in Access 2007
OK, that usually means you renamed controls and the control is no longer attached to the control.

Look at the property sheet for one of your letter buttons and make sure the OnClick property reads "[Event Procedure]"

If it does, then click on the builder button to the right of that line (...), it should bring you to your code.   If it does, then it's hooked up right and should be working and something else is going on.

If you end up in the code window with a blank procedure, then you most likely renamed the control at some point.

The other way to double check for un-attached code is in the VBA editor window.  There's two drop downs at the top.  With the left drop down selected for "General", the right should only show generic procedures (not attached to a control).

 If you see things like "Cmd_A_Click" in the list, then the code is no longer attached to the control.  I usually do that as a quick double check when I'm done editing a form.

 Other thing you can do to check is put a STOP in ApplyACustomFilter() at the top of the procedure.  You should hit it when you click a button.  If you don't, you know it's not getting called.  If you do hit the stop, in the debug window (Ctrl/G) do:

? strFilter

or hover over it in the code window to see the value (the debug window usually let's you see a little more, which is helpful with filters and SQL strings).

Personally, I would do the quoting this way:

Call ApplyACustomFilter("[AccountsNotPaidQry].[contactcode] Like 'A*'")

or better yet:

Call ApplyACustomFilter("[AccountsNotPaidQry].[contactcode] Like " & Chr$(34) & "A*" & chr$(34))

So the quotes are obvious.

Jim.
Avatar of msmerry

ASKER

The alphabet controls are each correctly named - eg, Cmd_B
The Event Procedure ... takes me straight to appropriate code.
The event procedure has been adjusted as you suggested to
Private Sub Cmd_B_Click()
    Call ApplyACustomFilter("[AccountsNotPaidQry].[contactcode] Like " & Chr$(34) & "B*" & Chr$(34))
End Sub

Open in new window

- and still no response
I also tried
Private Sub Cmd_B_Click()
    Call ApplyACustomFilterstop("[AccountsNotPaidQry].[contactcode] Like " & Chr$(34) & "B*" & Chr$(34))
End Sub

Open in new window

at which point a click on B  opened the VBA window with a "Compile Error: Sub or Function not defined".
If I understood you correctly, this means it should be working.
Beyond that, my VBA is not good enough to understand what you are telling me.
I will point out that this code works perfectly on another database
<<at which point a click on B  opened the VBA window with a "Compile Error: Sub or Function not defined".>>

 You can't change the name of the procedure in the call.

Private Sub Cmd_B_Click()
    Call ApplyACustomFilterstop("[AccountsNotPaidQry].[contactcode] Like " & Chr$(34) & "B*" & Chr$(34))
End Sub

This simply won't work. The STOP goes in the procedure itself here:

Private Sub ApplyACustomFilter(strFilter As String)

   STOP

    If IsDebugMode = 0 Then On Error GoTo ApplyACustomFilter_Error
    Me.[AccountsOutstandingsubfrm].Form.Filter = strFilter
    Me.[AccountsOutstandingsubfrm].Form.FilterOn = True
'    DoCmd.ApplyFilter "", strFilter
'    Me.cmdShowAll.Visible = True

ApplyACustomFilter_Exit:
 Exit Sub

Jim.
Avatar of msmerry

ASKER

Thanks Jim.
Put the STOP in - and it stops every time.
Take the stop out, and each letter hides ALL records - in other words, it is behaving as if there were no records beginning with B or P or Y.  The fact that it selects no-one suggests it is actually working, but not recognising anything
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of msmerry

ASKER

Jim,
Thanks so much for your assistance.
Hit F8, Ctrl G typed in ? strfilter as advised, and got - Call ApplyACustomFilter("([AccountsNotPaidQry].[contactcode] Like ""C*"")") - which looks perfect, but maybe I was wrong.

My "form" is really nothing more than a carrier for three subforms, and when I looked YES! you were right - on one subform, the links had fallen off; re-instate them, and everything works just fine.
Again, many thanks.
Aikimark, I have followed your advice and put in Option Explicit, though I am not sure what it does for me.  Thanks to you too.
harfang, I don't understand the difference between Public and Private, but I can see the logic behind your approach and will implement it next time.  Thanks also to you.
Thank you for your feedback; it was nice of you to take the time to answer each of us in turn.

The difference is that Public functions can be called from outside the module they reside in, even from the form's event properties, while Private functions can not.

Success with your project!
(°v°)
Avatar of msmerry

ASKER

HArfang, thanks for the explanation, much appreciated
Aileen