Solved Form data enable/disabled

[smtazulislam]

Oh hu hu, I am stupid i forget to put this event.
Private Sub Form_Current()
Call cboStatus_Change
End Sub
arnelgp code is work, Thank you.

 

[smtazulislam]

Can advice please,
have any others method or clone set code, I means I tried to 89 fields are insert in the EnableData() below.
Including 8 Form have 235 fields available.

Private Sub EnableData()
    txtEmployeeID.Enabled = True
    txtEmployeeName.Enabled = True
    alltxt.......=true
End Sub

Private Sub DisabledData()
    txtEmployeeID.Enabled = False
    txtEmployeeName.Enabled = False
    alltxt.....=false
End Sub

So, please any advice about this issue OR I should chosen this code only.

 

[Pat Hartman]

Rather than writing code to group the status', I would add an additional column to the status table and use "enable" as the value for one of the groups and "disable" as the value for the other group. That way, if you need to add more status codes, you don't need to modify your code, you just add a new record to the status table.

 

[smtazulislam]

Rather than writing code to group the status', I would add an additional column to the status table and use "enable" as the value for one of the groups and "disable" as the value for the other group. That way, if you need to add more status codes, you don't need to modify your code, you just add a new record to the status table.

tblStatus now I have
Status -Short Text
StatusID - AutoNumbers

Now you suggest me to add a column
Status - Short Text
Group - Short text , In this recorded CboGroup - Enable and Disable
StatusID - AutoNumbers

And I query to separate GroupEnable and GroupDisable; attached,

Where I put code or How ? Please suggest and help...?
And I have attached Form.

 

[The_Doc_Man]

But when form reopen then all data is enable again.

The problem stems from the idea that the form was originally saved in design mode with all of those data controls enabled. When you reopen the form you reopen the saved "all controls enabled" version of it. And you DO NOT want to try to save the form by switching into design mode from VBA because that will turn off controls for employees who are still with your company.

The "conditional formatting" method will work for you. OR if you are basing everything off a particular value in a particular control, you can put some kind of code in the Form_Current event, which fires (for bound forms) every time you navigate to a new record OR when the form opens after having been closed. The fact that you have to do this for 8 forms simply means you have a bit of tedious work ahead of you.

Isladogs mentioned an idea to use a value stored in the .TAG property of each control, which would allow you to use a "For Each control In Me.Controls" type of loop that visits each control and does something special for the controls tagged in some appropriate way.

June7 has offered a way to detect the specific status. Her idea shows you one way to make the decision. Isladogs showed you some ideas on implementing the decision.

Can you tell us why that is not enough? We are all volunteers here, willing to help but we need to understand why what we try isn't working for you. Otherwise we waste our time AND YOURS by taking shots in the dark. Help us to help you.

 

[smtazulislam]

We waste our time AND YOURS by taking shots in the dark. Help us to help you.

Apologies for late reply. Corona Virus effected in our business and shut down the all of the work up to 3 months, now we shifty start work..

Isladogs showed the example is nice, I like it. but my looking not like that. I need to permanently disable when cboStatus selected. and when I reopen the form then again enable. So that I ignored it. Apologies for it.

June7 & arnelgp give actually perfect code. it was absolutely nice to worked. But arnelgp gave code, I am forget to call Form_Current(). At last I find and take my problem solved. When I put in my database. after that her (June7 ) advise also "If "all" means all 8 forms then need code behind each form.". I am really confused to put each form (8 forms) into 622 fields name copy paste again.

I again reply and request to experts for give some advice short solution, Then Pat Hartman reply and his advice to make it now look and add one column again, tried to do make group query enable/disable.

This is the last performance in my database. And I dont know how to apply this conditional format (enable/disable) into 8 forms. and how work it ?

 

[smtazulislam]

Hello June7, I also tried to your advice, See my FORM frmEmployee, it was put into button New Employee . I am really confused to copy&Paste 8 forms field. Apologies.

Hello isladogs I appreciate your example, At last I agreed that it was best for me and I tried to insert code onto frmEmployeeEdit But get some problem. Error 438 and disable all field & tabs. User should visit tabs.


Note: no need any field hide.

 

[isladogs]

I've had a quick look at frmEmployeeEdit but haven't read all posts in this thread carefully
As far as I can see, the only place EnableControls is used is in this code which is called in the Form_Current event.

Private Sub cboStatus_Change()
   EnableControls False, "A"

End Sub

I suggest moving that code to cboStatus_AfterUpdate and removing the Call cboStatus_Change line from Form_Current as its unnecessary and may be causing issues

The next problem is that none of your controls seem to be tagged 'A' so the code won't work as planned.
In the property sheet, enter that tag value for all the controls you want to disable when the status is changed.
I would also recommend giving any other controls you don't want to disable some other tag value e.g. Z

If you only want this action to apply for certain cboStatus values then you need to reinstate your If cboStatus = ....code in that event.

Try all of that and let me know what happens

 

[smtazulislam]

at frmEmployeeEdit
As far as I can see, the only place EnableControls is used is in this code which is called in the Form_Current event.

Private Sub cboStatus_Change()
   EnableControls False, "A"

End Sub

I suggest moving that code to cboStatus_AfterUpdate and removing the Call cboStatus_Change line from Form_Current as its unnecessary and may be causing issues

Now my Module is LIKE that

Public Sub EnableControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to locked or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
       
        Case acLabel, _
             acImage, _
             acLine, _
             acRectangle
             'acPageBreak
            'no code here - these can't be disabled
        Case Else
            If ctrl.Tag = Tg1 Or _
               ctrl.Tag = Tg2 Or _
               ctrl.Tag = Tg3 Or _
               ctrl.Tag = Tg4 Or _
               ctrl.Tag = Tg5 Or _
               ctrl.Tag = Tg6 Then ctrl.Enabled = State
        End Select
       
    Next ctrl

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in EnableControls procedure: " & Err.Description
    Resume Exit_Handler
   
End Sub

And I put as your suggest to coding into cboStatus_Change.

Private Sub cboStatus_Change()
On Error GoTo Err_Handler
   'Emable controls fields
   EnableControls False, "A"
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Exit_Handler
End Sub

But if I dont call the FORM Form_Current then form reopen is enable. So I call like that:

Private Sub Form_Current()
On Error GoTo Err_Form_Current

    Call cboStatus_Change

Exit_Form_Current:
    Exit Sub

Err_Form_Current:
    MsgBox Err.Description
    Resume Exit_Form_Current

End Sub

But still form message "Error 438 , in EnableControls procedure: Object doesn't support this property or method. "

 

[isladogs]

All you've done to the module code is add line breaks. Fine if you prefer that but functionally identical to the original code.

If you want the controls disabled on form_load then add that code line to the Form_Load event.
Have you tagged the controls you want to disable and those you don't.
Until you do that, nothing will happen when the code is run and you may continue to get that error

 

[smtazulislam]

All you've done to the module code is add line breaks. Fine if you prefer that but functionally identical to the original code.

Thanks for gave time to reply. In the my form have original code copy as same here vertical don't showed so I put Breaks line. Apologies for it.
as per your suggest I put On_Load event....
same message Error 438 , in EnableControls procedure: Object doesn't support this property or method. "
it's last edit.....

 

[isladogs]

You haven't answered my question. Have you assigned tag values to each of the controls?

 

[smtazulislam]

Well, How to tag control I dont know. if you means, I tag others form. No, I dont apply any others form right now.

 

[isladogs]

Perhaps you need to look at my example app again!
The tag property is at the bottom of the Other tab on the property sheet.
Select one of more controls and enter the tag value you want to use e.g. A

The actual tag value is unimportant but whatever you use should then be used in your EnableControls code

 

[smtazulislam]

Yes, tag "A" set in all fields in the form without CboStatus, but same happen.... Error 438

 

[isladogs]

OK. Please upload the latest version and I'll take a look but it won't be till tomorrow as its getting late here in the UK.

 

[smtazulislam]

OK. Please upload the latest version and I'll take a look but it won't be till tomorrow as its getting late here in the UK.

Thank you so much for gave times and many advised into this issued. I appreciate.
And apologies for Inability to solve this issue... Thanks again

 

[isladogs]

I've spent some time looking at this tonight after all
First of all your project doesn't compile. You need to fix the compile errors

I added a Debug line to EnableControls to try & identify what triggers error 438

Public Sub EnableControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to locked or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
       Debug.Print ctrl.ControlType, ctrl.Name, ctrl.Tag 
        Select Case ctrl.ControlType
         ....

When I ran the code it stopped at a 'phantom' control EmptyCell1291 which isn't listed in the Property sheet.
You also have two other controls called EmptyCell1116 & EmptyCell1213. These also don't exist
As a result the code fails as it can't find those controls so can't enable or disable them

There may be other issues as well
Anyway, this is I believe why it fails. Your form is basically corrupt and needs replacing/re-creating
It looks to me like you have used an MS template form which are notoriously hard to modify

Sorry but I am unable to devote the time needed to rebuild your form for you
Good luck

 

[smtazulislam]

I thing, we waste time, It is not work in the combo box. it was only button.