Unlocking fields based on value of another field

[Renraf]

Yet another question here. (Please let me know if these are in the wrong
Discussion Group, by the way. "Programming" sounded pretty close to me, but I
could be wrong). Based on the entry in a lookup field, I want to enable other
fields in the record/form. Here's the structure:

PrimaryTaskLogs:
*Category (lookup field)
-NOT TICKET RELATED-
-Customer Install
-Customer Repair
-etc.
*PullReviewOrder (number of minutes)
*ManageMaterials (number of minutes)
*PerformTask (number of mintues)
*etc.

Category is going to default to "NOT TICKET RELATED," and each of the number
fields (PullReviewOrder, etc) will default to 0 and be locked for editing. If
the user selects any other Category, then the other fields will unlock for
editing.

Anyone know how to code this in VisualBasic (and VBScript, if possible)?

I appreciate everyone's time. My other two questions I just assumed would be
included in Access's validation and default value options, since that seems
to me to be the whole point of related tables (treating the related data like
one large set), so I was surprised to find out I could not do what I needed
without knowing some VB.

 

[Marshall Barton]

Yet another question here. (Please let me know if these are in the wrong
Discussion Group, by the way. "Programming" sounded pretty close to me, but I
could be wrong). Based on the entry in a lookup field, I want to enable other
fields in the record/form. Here's the structure:

PrimaryTaskLogs:
*Category (lookup field)
-NOT TICKET RELATED-
-Customer Install
-Customer Repair
-etc.
*PullReviewOrder (number of minutes)
*ManageMaterials (number of minutes)
*PerformTask (number of mintues)
*etc.

Category is going to default to "NOT TICKET RELATED," and each of the number
fields (PullReviewOrder, etc) will default to 0 and be locked for editing. If
the user selects any other Category, then the other fields will unlock for
editing.

Anyone know how to code this in VisualBasic (and VBScript, if possible)?

Use VBA in the lookup combo box's AfterUpdate event:

If Me.thecombobox = "NOT TICKET RELATED" Then
Me.PullReviewOrdertextbox.Locked = True
Me.ManageMaterialstextbox.Locked = True
. . .
End If

 

[Renraf]

Marshall,

Thanks for the reply. I adapted your code a bit to fit my needs, but I am
still having a problem:

If Me.Category <> "-NOT TICKET DRIVEN-" Then
Me.PullReviewOrder.Enabled = True
Me.ManageMaterials.Enabled = True
. . .
Else: Me.PullReviewOrder.Enabled = False
Me.ManageMaterials.Enabled = False
. . .
End If

I placed the code in the AfterUpdate event, but this means if a user opens
an old record that already has Category = "-NOT TICKET DRIVEN-", the boxes
will not enable/disable appropriately. Where else do I need to put this to
ensure the check executes whenever the form opens a record as well? I tried a
couple of places (On Load, On Open, Got Focus), but apparently I did not find
the one I needed.

Thank you.

 

[Marshall Barton]

The other place is the form's Current event.

BTW, if you are so inclined, you can do that in fewer lines
of code without using an If - Else

Me.PullReviewOrder.Enabled = Me.Category <> "-NOT TICKET
DRIVEN-"
Me.ManageMaterials.Enabled = Me.Category <> "-NOT TICKET
DRIVEN-"
. . .
Or, with a condition that long, it may make more sense this
way:

Dim bolMptTocket As Boolean
bolMptTocket = (Me.Category <> "-NOT TICKET DRIVEN-")
Me.PullReviewOrder.Enabled = bolMptTocket
Me.ManageMaterials.Enabled = bolMptTocket
. . .

 

[Renraf]

Thanks. Do I have to define the variable in each event where I use the code,
or can I define it somewhere once for the entire form, and then have it
update during the events where I'm using it?

Also, can't believe this didn't occur to me: Me.PullReviewOrder.Enabled =
Me.Category <> "-NOT TICKET DRIVEN-"

Guess it's because I've only programmed a tiny bit about 5 years ago. But
still, that's pretty obvious.

The other place is the form's Current event.

BTW, if you are so inclined, you can do that in fewer lines
of code without using an If - Else

Me.PullReviewOrder.Enabled = Me.Category <> "-NOT TICKET
DRIVEN-"
Me.ManageMaterials.Enabled = Me.Category <> "-NOT TICKET
DRIVEN-"
. . .
Or, with a condition that long, it may make more sense this
way:

Dim bolMptTocket As Boolean
bolMptTocket = (Me.Category <> "-NOT TICKET DRIVEN-")
Me.PullReviewOrder.Enabled = bolMptTocket
Me.ManageMaterials.Enabled = bolMptTocket
. . .
--
Marsh
MVP [MS Access]

Thanks for the reply. I adapted your code a bit to fit my needs, but I am
still having a problem:

If Me.Category <> "-NOT TICKET DRIVEN-" Then
Me.PullReviewOrder.Enabled = True
Me.ManageMaterials.Enabled = True
. . .
Else: Me.PullReviewOrder.Enabled = False
Me.ManageMaterials.Enabled = False
. . .
End If

I placed the code in the AfterUpdate event, but this means if a user opens
an old record that already has Category = "-NOT TICKET DRIVEN-", the boxes
will not enable/disable appropriately. Where else do I need to put this to
ensure the check executes whenever the form opens a record as well? I tried a
couple of places (On Load, On Open, Got Focus), but apparently I did not find
the one I needed.

 

[Renraf]

I defined it in both places. I assume that's preferable anyway. Thank you
very much. It works beautifully.

The other place is the form's Current event.

BTW, if you are so inclined, you can do that in fewer lines
of code without using an If - Else

Me.PullReviewOrder.Enabled = Me.Category <> "-NOT TICKET
DRIVEN-"
Me.ManageMaterials.Enabled = Me.Category <> "-NOT TICKET
DRIVEN-"
. . .
Or, with a condition that long, it may make more sense this
way:

Dim bolMptTocket As Boolean
bolMptTocket = (Me.Category <> "-NOT TICKET DRIVEN-")
Me.PullReviewOrder.Enabled = bolMptTocket
Me.ManageMaterials.Enabled = bolMptTocket
. . .
--
Marsh
MVP [MS Access]

Thanks for the reply. I adapted your code a bit to fit my needs, but I am
still having a problem:

If Me.Category <> "-NOT TICKET DRIVEN-" Then
Me.PullReviewOrder.Enabled = True
Me.ManageMaterials.Enabled = True
. . .
Else: Me.PullReviewOrder.Enabled = False
Me.ManageMaterials.Enabled = False
. . .
End If

I placed the code in the AfterUpdate event, but this means if a user opens
an old record that already has Category = "-NOT TICKET DRIVEN-", the boxes
will not enable/disable appropriately. Where else do I need to put this to
ensure the check executes whenever the form opens a record as well? I tried a
couple of places (On Load, On Open, Got Focus), but apparently I did not find
the one I needed.

 

[Marshall Barton]

I assume you are asking about the declaration:
Dim bolMptTocket As Boolean
A variable's declaration should be at the smallest scope
possible to do it's job. If you declare it once (as a
Public variable in a standard module's declaration section),
your entire project can use it. BUT, if it is used in lots
of places, there is a potential debugging nightmare if it
gets used from two different places at the same time. In
general, global variables are a bad practice on par with
using GoTo statements.

For the problem we've been discussing, you would be way
better off by creating a separate procedure with all the
code and calling it from the AfterUpdate and Current event.

To take the discussion off on a good/poor practices tangent,
your use of "-NOT TICKET DRIVEN-" in code is less than
ideal. What will you have to do if/when the powers that be
decide that it should be "Ticket not used" instead? You
could be dragged off some other project to rework the code
just to change some text hidden away in some modules.

A better practice would be to have a separate table with the
category text and a category ID number. The category combo
box would bind to the ID number but display the text.
If/when someone wants different text, all that would need to
be done is to edit the text in the table (via an
administrator form) because the code would compare to the
never changing category ID.

 

[Renraf]

I figured I should probably define it as its own function/procedure, but I
don't know how to do that. I have been engaged in a lot of bad coding for
this project.

As far as the other concern, you're undoubtedly correct. However, I'm nearly
as worried that someone else would come in and change the text value from
"-NOT TICKET DRIVEN-" to "Maintenance Ticket," which would be equally
damaging. So I really don't know how to get around that. In the meantime, I
suppose I will fix my sloppiness. I used autonumbers and lookups for almost
all of my primary keys; guess I should have resisted the temptation to go the
other way on this one.

I assume you are asking about the declaration:
Dim bolMptTocket As Boolean
A variable's declaration should be at the smallest scope
possible to do it's job. If you declare it once (as a
Public variable in a standard module's declaration section),
your entire project can use it. BUT, if it is used in lots
of places, there is a potential debugging nightmare if it
gets used from two different places at the same time. In
general, global variables are a bad practice on par with
using GoTo statements.

For the problem we've been discussing, you would be way
better off by creating a separate procedure with all the
code and calling it from the AfterUpdate and Current event.

To take the discussion off on a good/poor practices tangent,
your use of "-NOT TICKET DRIVEN-" in code is less than
ideal. What will you have to do if/when the powers that be
decide that it should be "Ticket not used" instead? You
could be dragged off some other project to rework the code
just to change some text hidden away in some modules.

A better practice would be to have a separate table with the
category text and a category ID number. The category combo
box would bind to the ID number but display the text.
If/when someone wants different text, all that would need to
be done is to edit the text in the table (via an
administrator form) because the code would compare to the
never changing category ID.
--
Marsh
MVP [MS Access]

Thanks. Do I have to define the variable in each event where I use the code,
or can I define it somewhere once for the entire form, and then have it
update during the events where I'm using it?

Also, can't believe this didn't occur to me: Me.PullReviewOrder.Enabled =
Me.Category <> "-NOT TICKET DRIVEN-"

Guess it's because I've only programmed a tiny bit about 5 years ago. But
still, that's pretty obvious.

 

[Marshall Barton]

I figured I should probably define it as its own function/procedure, but I
don't know how to do that. I have been engaged in a lot of bad coding for
this project.

Just create a new procedure (in the same form's module)
using the code you were duplicating.

Private Sub CheckTicket()
Dim bolMptTocket As Boolean
bolMptTocket = (Me.Category <> "-NOT TICKET DRIVEN-")
Me.PullReviewOrder.Enabled = bolMptTocket
Me.ManageMaterials.Enabled = bolMptTocket
. . .
End Sub

Then change the two event procedures to call the procedure:

CheckTicket
or, if you prefer:
Call CheckTicket()

As far as the other concern, you're undoubtedly correct. However, I'm nearly
as worried that someone else would come in and change the text value from
"-NOT TICKET DRIVEN-" to "Maintenance Ticket," which would be equally
damaging. So I really don't know how to get around that. In the meantime, I
suppose I will fix my sloppiness. I used autonumbers and lookups for almost
all of my primary keys; guess I should have resisted the temptation to go the
other way on this one.

That kind of info should not be available, much less
editable, by ordinary users. They should not be allowed to
see any table/query's datasheet so they will not be tempted
to muck about with raw data. For non-malicious users, just
hiding the database window and standard menus/toolbars
(using the Tools - Startup,,, menu) should be sufficient.
In more severe situations, you may want to consider creating
a sepearate front end mdb that is installed on a "data
administrator" machine. Either way, if/when the powers that
be decide to change the text, the data asministrator person
(you?) can do it in half a minute without even looking at a
line of code.