Locking or Unlocking a form control based on input.

[AOMCprog]

Hello,

New to the forum. What I am trying to do is this: On my form I have a combo box where a person's name is selected. As a result of person selected in this combo box, the "level of access" associated with the person selected comes up in another combo box that is locked and cannot be accessed by the user. According to the value that appears in the locked combo box (high or low), I want to either lock (low access level) a text box control, or unlock (high access level) the same text box control. Help? Oh, and I am using Access 2000. Thanks in advance.

Don

 

[vbaInet]

Welcome to the forum.

In the After Update event of the level combo box, how many columns are there? Is the first column the ID of the level?

 

[AOMCprog]

Thanks for your reply, VbaInet. Not exactly sure what you are asking, but, the properties for the locked combo box that displays the level of access is as follows:

Row Source Type: Table/Query
Row Source: Name of my Query
Column Count: 2
Bound Column: 1

Hope that helps.

 

[vbaInet]

Actually that was an incomplete sentence

So in the After Update event of the level of access combo, you can do something like:

txtbox1.Locked = (Combobox.value = put id of low access level here)

You would also need the same code in the On Current event of your form

 

[AOMCprog]

VbaInet,

I set the After Update event of the level of access combo box and the On Current event of my form to: mytxtbox.Locked = Mylevelcombobox.Value = "Low Level",
and it doesn't work. Am I doing something wrong?

Don

 

[vbaInet]

In my post I mentioned to the ID of Low Level, not the value. The ID I would imagine is numeric. Also I put those brackets for a reason.

 

[AOMCprog]

Okay, how do I find out the ID number? There are only two possibilities, low and high.

 

[vbaInet]

How did you create your table? Did you create an autonumber field to be the Primary key of that table that holds the levels?

Look in the Row Source it identifies the table/query used. Open it up and the first column is the ID

 

[AOMCprog]

The table where I keep the access level info only has two fields: Person's Name and level of access. The primary key is Person's Name, since I don't want anybody listed in this table more than once.

 

[vbaInet]

So you manually input Low Level/High Level? These two values aren't stored in a separate table?

 

[AOMCprog]

Yes, data with each person's name and access level are stored in one table that I keyed in manually, and the data being input on the form goes into a second table. There is one field in the second table that I don't want people with a low level of access to be able to input to.

 

[vbaInet]

That's not very good design. If there's a typo in Low level within one of the records, let's say it was "l ow level", then the code wouldn't work.

On the On Current event, put this:
msgbox Mylevelcombobox.Value

And tell me the exact values that pops up as you move through the records.

 

[AOMCprog]

The level of access table will be maintained by me and will only change if the pharmacy adds a new pharmacist or technician (not common). Okay, the msgbox is giving me the name of the individual (primary key) in the level of access table. I need to have it be the value that is being displayed in the locked combo box (only two values - low or high), and have the text field control be locked or unlocked based on that.

 

[vbaInet]

That's what I've been asking for in the past three posts. You want to use the ID not the level. As mentioned earlier, if there's a typo (even if it's you managing it it's still inevitable) then it just wouldn't work. Your combo box is returning the ID, not the level. Use the ID and it will work.

 

[AOMCprog]

My locked combo box is DISPLAYING the level, which is what I want. I can't very well be locking or unlocking the text field control by listing all 20 some possible people that are in my access level table.

 

[vbaInet]

The ID field is there in the combo box but it is hidden, that's why you can't see it. Go to the property sheet of your combo box, under the DATA tab look in the ROW SOURCE property of your combo box. Click the elipsis button that appears when you put the cursor inside that row source property.

What I've been explaining to you is how it's done.

 

[AOMCprog]

Clicking the elipsis button that appears when I put the cursor inside the row source property of my locked combo button just brings up the design view of my query, which does not have an ID field in it. Hey, appreciate you taking the time to help me with this. It must be late in the UK, and I'm heading home myself. I will start once again on this when I'm fresh in the morning. Thanks once again.

Don

 

[vbaInet]

No problemo! I think it would be quicker if I could see a stripped down version of your db then I could advise what your next steps would be. In the meantime,

mytxtbox.Locked = (Mylevelcombobox.Value = "Low Level")

With the brackets in the On Current event of the form, for starters.

 

[AOMCprog]

Okay, here is what is happening. The msgbox Mylevelcombobox.Value in the On Current event portion of my form only works when the form initially comes up, and value of the combo box is the person's name. That is because the level combo box is bound to the person's name but set to display the level. When I put the msgbox command in the After Update and On Change event portion of the Mylevelcombobox control, it also does not trigger (because I never enter that control?), but the same msgbox command in the After Update portion of the combo box where the person's name is selected does trigger the msgbox, but of course, the value of the level combo box is the person's name and not the person's level. Is there a way where I can obtain the person's level by looking at the person's level in the current query record instead of the value of the control? Thanks.

Don

 

[vbaInet]

Like this:

mytxtbox.Locked = (Me![FieldNameOfLevel] = "Low Level")