Visible/Invisible textboxes on a form - done by record

[Pele]

I have a continous form in Access (Frm_Update Rate tbl_Main) linked to a
table called LABOR RATE TBL. The form has (among others) 5 textboxes called
HOURLY, MONTHLY, VACATION, [OTHER WEEKS], [LABOR RATE CODE]. The [Labor Rate
Code] textbox is invisible on the form.

When the continous form loads, it displays all the multiple records in the
table.

For EACH RECORD, I want the form to make the 3 textboxes called Hourly,
Vacation, [Other weeks] invisible if [Labor rate Code[ is 2 or 7. That is,
only the Monthly textbox is visible.

Also, for EACH record, if [Labor rate Code[ is NOT 2 or 7, then I want the
Hourly, Vacation and [Other Weeks] textboxes to be visible while the Monthly
textbox is now invisible.

I guess the question is, in Access, how can you programmatically control the
properties of controls on a form at the record level.

Toks

 

[Steve Schapel]

Toks,

You can't. Continuous view froms don't work like that.

You could use Conditional Formatting. You can't control the Visible
property of the textboxes this way, but you can mimic it by setting the
BackColor and the ForeColor of the textbox to the same as the BackColor
of the form section, so it seems to be invisible.

 

[Pele]

Steve,

Thanks for the advice...Your suggestion will work quite fine.

Can you help me with the code, I am not savvy in VB. Thanks.

Toks,

You can't. Continuous view froms don't work like that.

You could use Conditional Formatting. You can't control the Visible
property of the textboxes this way, but you can mimic it by setting the
BackColor and the ForeColor of the textbox to the same as the BackColor
of the form section, so it seems to be invisible.

--
Steve Schapel, Microsoft Access MVP

I have a continous form in Access (Frm_Update Rate tbl_Main) linked to a
table called LABOR RATE TBL. The form has (among others) 5 textboxes called
HOURLY, MONTHLY, VACATION, [OTHER WEEKS], [LABOR RATE CODE]. The [Labor Rate
Code] textbox is invisible on the form.

When the continous form loads, it displays all the multiple records in the
table.

For EACH RECORD, I want the form to make the 3 textboxes called Hourly,
Vacation, [Other weeks] invisible if [Labor rate Code[ is 2 or 7. That is,
only the Monthly textbox is visible.

Also, for EACH record, if [Labor rate Code[ is NOT 2 or 7, then I want the
Hourly, Vacation and [Other Weeks] textboxes to be visible while the Monthly
textbox is now invisible.

I guess the question is, in Access, how can you programmatically control the
properties of controls on a form at the record level.

Toks

 

[Steve Schapel]

Pele,

This is not code related. In Design view of the form, select one of the
textboxes you want this "invisible" to apply to, then go to the Format
menu and select Conditional Formatting. Then you set it up like this
(for example)... Enter 'Expression Is..." and then in the Condition
box, I think it would be...
[Labor Rate Code]=2 Or [Labor Rate Code]=7
.... and then just set the background color and font color from the color
tools.

 

[Pele]

Steve,

Brilliant...It works very well. Thanks.

I'd tried to use macros with conditions but had unfortunately been changing
the property of visble to false; would the macro had worked if I'd been
changing the back color and font color. Right now, the macro just seems to
check only the first record.

Also, even though the textboxes are now darkened, the user can still type
into them (which I don't want). How can I prevent the user from typing into
the dark textboxes (when the conditions are true). Thanks a lot for your help.

Toks

Pele,

This is not code related. In Design view of the form, select one of the
textboxes you want this "invisible" to apply to, then go to the Format
menu and select Conditional Formatting. Then you set it up like this
(for example)... Enter 'Expression Is..." and then in the Condition
box, I think it would be...
[Labor Rate Code]=2 Or [Labor Rate Code]=7
.... and then just set the background color and font color from the color
tools.

--
Steve Schapel, Microsoft Access MVP

Steve,

Thanks for the advice...Your suggestion will work quite fine.

Can you help me with the code, I am not savvy in VB. Thanks.

 

[Steve Schapel]

No, the macro approach would not work, it would make the change for all
records at the same time.

To prevent the user having access to the "hidden" controls is a bit
awkward. If you set the Tab Stop property to No, then the control will
not receive the focus unless the user clicks into it, which may prevent
most of the problem. You can set the Enabled property to No and the
Locked property to Yes, but once again this would apply to all records
at the same time, which I think in your situation would not work, am I
right? Therefore, you might need to use a VBA procedure on the Current
event of the form, to toggle the Enabled and Locked properties of the
controls, according to the value of the Labor Rate Code, something like
this...
Me.Hourly.Locked = ([Labor Rate Code]=2 Or [Labor Rate Code]=7)
Me.Hourly.Enabled = ([Labor Rate Code]<>2 And [Labor Rate Code]<>7)
etc...

 

[SteveS]

No, the macro approach would not work, it would make the change for all
records at the same time.

To prevent the user having access to the "hidden" controls is a bit
awkward. If you set the Tab Stop property to No, then the control will
not receive the focus unless the user clicks into it, which may prevent
most of the problem. You can set the Enabled property to No and the
Locked property to Yes, but once again this would apply to all records
at the same time, which I think in your situation would not work, am I
right? Therefore, you might need to use a VBA procedure on the Current
event of the form, to toggle the Enabled and Locked properties of the
controls, according to the value of the Labor Rate Code, something like
this...
Me.Hourly.Locked = ([Labor Rate Code]=2 Or [Labor Rate Code]=7)
Me.Hourly.Enabled = ([Labor Rate Code]<>2 And [Labor Rate Code]<>7)
etc...

PMFJI,

Using conditional formatting, you can set the locked property. Unfortunately,
the fore/back colors have no effect when used at the same time as the locked
format.

If you can live with the users seeing the field, but are unable to edit it, try
setting the locked property using conditional formatting.

 

[Steve Schapel]

Using conditional formatting, you can set the locked property.

Sorry, Steve, this is not correct. I think you are thinking of the
Enabled property. A textbox's Locked property is not exposed via CF.

 

[SteveS]

Sorry, Steve, this is not correct. I think you are thinking of the
Enabled property. A textbox's Locked property is not exposed via CF.

I knew that... :^(

I think my brain was locked and not enabled <g>. I read it three times before I
hit the send button. I try four times...

Thanks for the correction Steve.

 

[Pele]

Steve,

I am assuming that the conditional formatting will not help.

Anyway, I did your initial recommendation and I am getting a runtime error
2465 "Microsoft Access can't find the field forms referred to in your
expression"

Below is the code I wrote. Note that I have enclosed my text box names in
parenthesis since the names have spaces in them.

Private Sub Form_Current()
'prevent data entry into hourly rate, Vacation weeks and Other Weeks
Me.[hourly rate].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Vacation weeks].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Other weeks].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Monthly rate].Enabled = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
'prevent data entry into Monthly Rate
Me.[hourly rate].Enabled = ([labor_type_cd] <> 2 Or [labor_type_cd] <> 7)
Me.[Vacation weeks].Enabled = ([labor_type_cd] <> 2 Or [labor_type_cd] <> 7)
Me.[Other weeks].Enabled = ([labor_type_cd] <> 2 Or [labor_type_cd] <> 7)
Me.[Monthly rate].Locked = ([labor_type_cd] <> 2 Or [labor_type_cd] <> 7)
'prevent data entry to following fields if Manpower employee
Me.[Vacation weeks].Locked = ([labor_type_cd] = 5)
Me.[Other weeks].Locked = ([labor_type_cd] = 5)
Me.[Monthly rate].Locked = ([labor_type_cd] = 5)
End Sub

 

[Steve Schapel]

Pele,

I can't immediately see what might be causing the error. There are,
however, some problems with the code. In your earlier posts you
referred to [Labor Rate Code]. Is this actually called [labor_type_cd]?
With the _s in the field name? Ok, as for the controls that you are
trying to hide or otherwise, I would put a ! instead of a . if you are
using the []s. If you type Me. in the VB editor, and then the first
letter of the control, the 'Intellisense' should find it for you. If
there is a space in the field name, it is automatically replaced by a _
so you would get Me.Monthly_rate etc. Try it like that, or else
Me![Monthly rate].

Also, you need to be setting Enabled to Yes and Locked to No, or Enbled
to No and Locked to Yes. So for each control, the code for the enabled
and locked properties is opposite. But note also that...
([labor_type_cd] <> 2 Or [labor_type_cd] <> 7)
.... isn't correct, this will always evaluate to True. You need And
instead of Or.

So, try tidying it up, something along these lines...
Me.Hourly_rate.Locked = (Me.labor_type_cd = 2 Or Me.labor_type_cd = 7)
Me.Hourly_rate.Enabled = (Me.labor_type_cd <> 2 And Me.labor_type_cd <> 7)
... etc.

If you still get the error, try removing (commenting out) some of the
code, so the Enabled and Locked toggling only applies to one of the
fields (just to simplify it for troubleshooting purposes).

--
Steve Schapel, Microsoft Access MVP

Steve,

I am assuming that the conditional formatting will not help.

Anyway, I did your initial recommendation and I am getting a runtime error
2465 "Microsoft Access can't find the field forms referred to in your
expression"

Below is the code I wrote. Note that I have enclosed my text box names in
parenthesis since the names have spaces in them.

Private Sub Form_Current()
'prevent data entry into hourly rate, Vacation weeks and Other Weeks
Me.[hourly rate].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Vacation weeks].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Other weeks].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Monthly rate].Enabled = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
'prevent data entry into Monthly Rate
Me.[hourly rate].Enabled = ([labor_type_cd] <> 2 Or [labor_type_cd] <> 7)
Me.[Vacation weeks].Enabled = ([labor_type_cd] <> 2 Or [labor_type_cd] <> 7)
Me.[Other weeks].Enabled = ([labor_type_cd] <> 2 Or [labor_type_cd] <> 7)
Me.[Monthly rate].Locked = ([labor_type_cd] <> 2 Or [labor_type_cd] <> 7)
'prevent data entry to following fields if Manpower employee
Me.[Vacation weeks].Locked = ([labor_type_cd] = 5)
Me.[Other weeks].Locked = ([labor_type_cd] = 5)
Me.[Monthly rate].Locked = ([labor_type_cd] = 5)
End Sub

 

[Steve Schapel]

Pele,

I have just had another thought... Maybe it is something incorrect in
the setup of the Conditional Formatting that is causing the error, and
not something to do with the code we are looking at?

 

[Pele]

Hello Steve,

Please let me know what your new thoughts are regarding this problem.

By the way, the way you've written your code (see below). does not give me
the opportunity to specify whether the textbox's Locked Property is True or
False (same for the Enabled property).

By the way, the Labor_type_cd is correct. I'd mistakenly refered to it as
Labor Rate Code.



Private Sub Form_Current()
'prevent data entry into hourly rate, Vacation weeks and Other Weeks
Me.[hourly rate].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Vacation weeks].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Other weeks].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Monthly rate].Enabled = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)

 

[Steve Schapel]

Pele,

Hopefully you have now got this one sorted out. On reading this last
post of yours, it appears that you didn't see my previous reply. If you
still need any help with it, please post back with the code you are
currently using.

 

[Pele]

Steve,

Firstly, "Thank You" very much for all your help. I wanted to tell you that
I tried the conditional formatting using enable and it worked i.e. each
record is treated differently. The only problem though is if the first record
is not affected by a condition, the background and font colour are less dark
for subsequent record when the condition is met i.e. a User could see the
default data entry in the underlying table. SInce they can't change it
though, your proposed solution works fine.

Also, as per my question below regarding your code.....FYI......I had tried
to use code as initially suggested, but I couldn't get it to work even though
it appeared like it ran. Below is the final code I had put together. For my
learnings, can you tell me why this did not work.


Pele

Private Sub Form_Current()
Dim thisform As Form
Set thisform = Forms![frm_update rate tbl_main]![frm_update rate table].Form
If (Forms![frm_update rate tbl_main]![frm_update rate
table].Form.[labor_type_cd] = 2 Or Forms![frm_update rate
tbl_main]![frm_update rate table].Form.[labor_type_cd] = 7) Then

'prevent data entry into hourly rate, Vacation weeks and Other Weeks

Forms![frm_update rate tbl_main]![frm_update rate table].Form.[hourly
rate].Locked = "true"
Forms![frm_update rate tbl_main]![frm_update rate table].Form.[Vacation
weeks].Locked = "true"
Forms![frm_update rate tbl_main]![frm_update rate table].Form.[Other
weeks].Locked = "true"
Forms![frm_update rate tbl_main]![frm_update rate table].Form.[Monthly
rate].Enabled = "true"
End If

If (Forms![frm_update rate tbl_main]![frm_update rate
table].Form.[labor_type_cd] = 1 Or Forms![frm_update rate
tbl_main]![frm_update rate table].Form.[labor_type_cd] = 3 Or
Forms![frm_update rate tbl_main]![frm_update rate table].Form.[labor_type_cd]
= 6) Then

'prevent data entry into Monthly Rate

Forms![frm_update rate tbl_main]![frm_update rate table].Form.[hourly
rate].Enabled = "true"
Forms![frm_update rate tbl_main]![frm_update rate table].Form.[Vacation
weeks].Enabled = "true"
Forms![frm_update rate tbl_main]![frm_update rate table].Form.[Other
weeks].Enabled = "true"
Forms![frm_update rate tbl_main]![frm_update rate table].Form.[Monthly
rate].Locked = "true"
End If

If (Forms![frm_update rate tbl_main]![frm_update rate
table].Form.[labor_type_cd] = 5 Or Forms![frm_update rate
tbl_main]![frm_update rate table].Form.[labor_type_cd] = 4) Then

'prevent data entry to following fields if Manpower employee

Forms![frm_update rate tbl_main]![frm_update rate table].Form.[Vacation
weeks].Locked = "true"
Forms![frm_update rate tbl_main]![frm_update rate table].Form.[Other
weeks].Locked = "true"
Forms![frm_update rate tbl_main]![frm_update rate table].Form.[Monthly
rate].Locked = "true"
End If
End Sub

Pele,

Hopefully you have now got this one sorted out. On reading this last
post of yours, it appears that you didn't see my previous reply. If you
still need any help with it, please post back with the code you are
currently using.

--
Steve Schapel, Microsoft Access MVP

Hello Steve,

Please let me know what your new thoughts are regarding this problem.

By the way, the way you've written your code (see below). does not give me
the opportunity to specify whether the textbox's Locked Property is True or
False (same for the Enabled property).

By the way, the Labor_type_cd is correct. I'd mistakenly refered to it as
Labor Rate Code.



Private Sub Form_Current()
'prevent data entry into hourly rate, Vacation weeks and Other Weeks
Me.[hourly rate].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Vacation weeks].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Other weeks].Locked = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)
Me.[Monthly rate].Enabled = ([labor_type_cd] = 2 Or [labor_type_cd] = 7)

 

[Steve Schapel]

Pele,

The main thing wrong with the code you posted is that you have ""s
around the word "true", which is not correct, as it refers to a text
string "true" rather than the Boolean value True.

Another thing that is wrong is that you are not setting the values to
False again if the conditions are not met.

The code example I gave you is correct. If it didn't work, it is
because of one of these possibilities:
- you misunderstood my example
- the names of the controls you used are not correct

However, now that I see the full picture, here is another approach which
is a bit neater (if I understand you correctly)...

Private Sub Form_Current()
Select Case Me.labor_type_cd
Case 2, 7
Me.hourly_rate.Locked = True
Me.hourly_rate.Enabled = False
Me.Vacation_weeks.Locked = True
Me.Vacation_weeks.Enabled = False
Me.Other_weeks.Locked = True
Me.Other_weeks.Enabled = False
Me.Monthly_rate.Locked = False
Me.Monthly_rate.Enabled = True
Case 3, 6
Me.hourly_rate.Locked = False
Me.hourly_rate.Enabled = True
Me.Vacation_weeks.Locked = False
Me.Vacation_weeks.Enabled = True
Me.Other_weeks.Locked = False
Me.Other_weeks.Enabled = True
Me.Monthly_rate.Locked = True
Me.Monthly_rate.Enabled = False
Case 4, 5
Me.hourly_rate.Locked = False
Me.hourly_rate.Enabled = True
Me.Vacation_weeks.Locked = True
Me.Vacation_weeks.Enabled = False
Me.Other_weeks.Locked = True
Me.Other_weeks.Enabled = False
Me.Monthly_rate.Locked = True
Me.Monthly_rate.Enabled = False
End Select
End Sub

 

[Pele]

Steve,

Thanks for the comments. I tried your code and it worked BUT I had to remove
the conditional formatting for Enable.

Anyway, the form works as required now.

Pele