Access 2003 Need to make command buttons enable / disable based on other controls

[SummerSolstice]

Hi everyone. Sorry this may get a bit long here trying to explain but I'm working on building a database that will be used for tracking enrollments & terminations in some classes that we offer at work for clients. There are two main tables TblClientDemogr aphics and TblAnnualEnroll ment. They are linked as a "one to many" based on an unique ID number for each client. What I'm currently working on is a bound single form used to edit the annual enrollment information for a particular client.

Where I'm stuck is at the point that there are checkboxes for "Enrollment " and "Terminated " from the program. I want to restrict these two entries so that the providers can not check the terminated unless the client is enrolled, and also restrict so that the enrolled entry can't be changed to a null value if terminated is checked (basically can't be terminated unless they are enrolled)

I created the checkboxes as not visible and then created textboxes with transparent cmd boxes over them so I could use conditional formatting but I'm missing something.

Controls are: [Terminated] & [Enrolled] are the hidden checkbox controls & the bound fields to the table; [ChkEnrollTxt] & [ChkTermTxt] are the textboxes that are unbound and set up with control sources as =IIf([Terminated],Chr(82),"") and the same for Enrolled; and then I have [EnrollTransCmd] & [TermTransCmd] for the two transparent command buttons that are over the top of the two text boxes.

Code that I have currently trying to make this work:

Private Sub EnrollTransCmd_AfterUpdate()

    If Me.Enrolled = -1 Then
    Me.TermTransCmd.Enabled = True
    ElseIf Me.Enrolled = 0 Then
    Me.TermTransCmd.Enabled = False
    End If
    
End Sub

Private Sub EnrollTransCmd_Click()

    Me!Enrolled = Not Me!Enrolled
        
End Sub

Private Sub Form_Current()

    If Me.Enrolled = -1 Then
    Me.TermTransCmd.Enabled = True
    ElseIf Me.Enrolled = 0 Then
    Me.TermTransCmd.Enabled = False
    End If

End Sub

Private Sub TermTransCmd_AfterUpdate()

    If Me.Terminated = 0 Then
    Me.EnrollTransCmd.Enabled = True
    ElseIf Terminated = -1 Then
    Me.EnrollTransCmd.Enabled = False
    End If

End Sub

Private Sub TermTransCmd_Click()
    
    Me!Terminated = Not Me!Terminated
     
End Sub

_______________ _______________ __________

With this code the TermTransCmd button is disabled when you go to the edit form, when you click the Enroll it enables the Terminated but when you click terminated it doesn't disable the enroll so the entry person can remove the enrollment without clearing the terminated field. Any ideas? I'm wondering if it's something to do with the On Update but when, for example you actually click both the enroll and then terminated and then remove both of them the terminated becomes disabled again. I tried to put the enabled=True or false statements on the actual checkboxes (hidden) on the after update also but that didn't work either.

I'm stumped. Thanks

[TheSmileyCoder]

What I do in such cases is to move the logic to a seperate procedure. It makes it easier to maintain, and then call that procedure from each event as I need it (The afterupdates, as well as the current).

Private Sub setButtons()
'Check for Enrollment button
   'Enabled if Terminated is False OR if Terminated is Null
   Me.EnrollTransCmd.Enabled=Not Me.Terminated OR isNull(Me.Terminated)

'Check for Terminated button
   'Enabled if Enrolled is true
   Me.TermTransCmd=Me.Enrolled
End Sub

[SummerSolstice]

Sorry about the Code tags, first post here.

I'm a little lost yet (still learning especially the VBA part of this world). So does the set Buttons procedure go on the main form? and then how do you call for the procedure on the After Updates etc?

[TheSmileyCoder]

This specific bit of code is specific to the form, and as such should be placed in the form's code module. Then in your form's current event, and in the click event of the two buttons:

Private Sub Form_Current() 
  call setButtons
End Sub

And

Private Sub TermTransCmd_Click() 
  Call setButtons
End Sub

Private Sub EnrollTransCmd_Click() 
  Call setButtons
End Sub

One thing to remember is that the AfterUpdate event of controls will not be activated when the value is set by code.

[SummerSolstice]

I tried to enter the code but I keep getting a Run-Time Error '438': Object doesn't support this property or method and when I Debug it jumps to the Me.TermTransCmd =Me.Enrolled (Line 8 of Post #2 above). Any idea what the problem might be?

[TheSmileyCoder]

It would seem I forgot to add a .Enabled when I wrote that line of code.