Lock/Unlock check boxes

[MsMickey0219]

Hi, I need help. I am getting my feet wet in the world of syntex. I am working with an access dbase that includes tables, queries, forms, etc...

My form (linked to a table) has multiple unlocked check boxes that I need lock based on the criteria in a field called MCC Number (this is a 7 digit number; the field is characterized as text) Example: I need the check box bound to field "Hospital" to be locked when MCC Number is "0000070"

Would I add the criteria to each check box changing the information according to the field or can the logic be added one place and applied to the whole form?

I hope that you can understand my problem and help me work this out.
Thanks,
Lisa

 

[CEH]

I'm not sure if this will work with what you are trying to do...but to apply the same critera, code, action... to multiple controls you can use the "Tag" property.
Something like....

Private Sub cmdVisible_Click()
Dim ctl As Control
For Each ctl In Me
If ctl.Tag = "*" Then
ctl.Visible = True
End If
Next

When this is executed every control that has a "*" in the tag property would become visible.
In your situation probably ctl.Locked would be the correct syntax.. (might need to check that )

 

[MsMickey0219]

Still confused. Should this work on the Tag line of the check box or the complete form. Sorry I am so new to this I am still learning.

 

[CEH]

Well your form does have a "Tag" property, but you don't want it there for this one.... If you put your form into design view, right click on a textbox....combobox.. whatever control you want and select "Properties" on the "Other" tab of properties, at the bottom of the list, you will see "Tag". Type an Asterisk,(or anything you want) into the space to the right.
So now as the code says....
I think this code works best placed in the After Update event of the form.


Dim ctl As Control 'basically tells Access "ctl" means "Controls"
For Each ctl In Me 'For every control in the form
If ctl.Tag = "*" Then 'If that control(textbox,combo,whatever) has an Asterisk in the Tag property then...........

ctl.Visible = True 'THAT control with the "Asterisk" is visible
End If
Next 'Move on, and look at the next control

Depending on what is being done with your tagged controls it is sometimes necessary to also put the same code in the OnCurrent event of the form.

 

[MsMickey0219]

I think I understand. I will try it.
Thanks alot for your help.
Lisa

 

[MsMickey0219]

Ok, I think I need to share more information about my database; because I can not get this to work.

On my form I have a field called MCC Number that contains the following: 0000004, 0000027, 0000028, 0000029, 0000070 and 0000081. This field is a text field because I will not calculate the number.

Then I have mulitple fields as check boxes on my form named the following: Hospital, Prenatal, Cardiology, General Dental, Supported Housing, etc... My check boxes are not locked.

What I would like to have happen is when I enter a MCC Number of 0000004 the only check boxes available are for Hospital, Prenatal, and Cardiology; and check boxes for General Dental and Supported Housing to be locked to where you can not put a check in the box. So the MCC Number drives whether the check box is locked.

I hope this makes since. Please help if you can.
Lisa

 

[Brianwarnock]

I would use a SElect case construct in, I think, the before update event of the MCC Number field, BTW I would avoid spaces in fields they can lead to syntax problems use
MCC_Number

I think it might simplify the code if on entry to the code all checkboxes were locked and then unlock those that need to be in the case statement.

Brian

 

[Brianwarnock]

Having never done this just gave it a try and got an error, hmm back to the drawing board.

Brian

 

[Brianwarnock]

Ok change the status of all checkboxes to locked, then as before use select case to unlock them as required in the MCC before update event.

Brian

 

[MsMickey0219]

I was wondering if I should start with them locked. I have no problem changing them to locked and then having the syntax unlock the check box. Any help would be great.
Thanks,
Lisa

 

[Brianwarnock]

Our last posts crossed.

Brian

 

[Brianwarnock]

I was wondering if it might be less confusing to users to make the checkboxes visible/nonvisible rather than unlocked/locked ? Just a thought.

Brian

 

[MsMickey0219]

That sounds good to me. How would I make them appear/disappear? I am learning syntax so any help would be great.

 

[Brianwarnock]

Set all the visible status to No in the properties.
In the before update event of MCC_Number code

Select Case Me.MCC_Number
Case "0000004" ' I assume that this is a text field as it has leading 0
Me.Hospital.Visible = True
etc

Case

.
.
.
End Select


Brian

 

[MsMickey0219]

To make sure I understand:

I would add to the before update event of th MCC_Number field

Select Case Me.MCC_Number
Case "0000004"
Me.Hospital.Visible = True
Me.Prenatal.Visible = True
Me.Cardiology.Visible = True

Case
.
.
.
End Select

Select Case Me.MCC_Number
Case "0000070"
Me.General_Dental.Visible = True

Case

.
.
.

End Select

**Do I have the above written correctly? Also, why would I put the syntax on the Before Update and not After Update? The field is blank when I am entering a new record in the form.
Thanks,
Lisa

 

[CEH]

I would use the "enabled" property But a matter of preference I guess. Whichever you use, Select case, or If....Then.... remember you also need the code on the OnCurrent event... And if you use a Select Case... Be sure and include for the last..... "Case Else" then something to do if someone types a number not in your other Select case list.

 

[Brianwarnock]

Sorry had to go out. Chris is correct about having to have code in the Form current event, it will be similar but not nescessarily the same. Setting all checkboxes to non visible in properties does not achieve what we want, its surprising how one thinks more clearly as you stroll along ,so you have to decide if you want to set the visible=True/False for each Case in the Select , in which case the code is the same or whether to make all nonvisible in the Form current event and use the Select Case construct to make visible those required.

By now you may have looked at help on Select Case and realised that your code was not correct.

Select Case Me.MCC_Number
Case "0000004"
Me.Hospital.Visible = True
Me.Prenatal.Visible = True
Me.Cardiology.Visible = True

Case "0000070"
Me.General_Dental.Visible = True

End Select

Brian

 

[MsMickey0219]

I am not having any luck getting this to work.
I have added the syntax to the Form "OnCurrent".
I have tried the MCC_Number field in the "Before Update" and "After Update"

Any suggestions?

 

[MsMickey0219]

I have attached a word doc of a print screen of the test sytax I put in and the error I received. Can you help?

View attachment ErrorMessage.doc

 

[boblarson]

It isn't "IsVisible", you use .Visible = True or .Visible = False