Changed Locked property to yes via code

[NigelB]

I have 4 fields on a subform - QtyYear, QtyAmount, QtyDate & QtyLoc. If
QtyYear is not equal to the current year then I need locked set to Yes for
all 4 fields. If it is equal then I need it left as No. I only want the user
to be able to enter/change this years data.
I tried the expression builder for "event before update" but was not able to
complete it.
Help would be appreciated, thanks

 

[kryszystof via AccessMonster.com]

Pretty Simple - you set up your sub, and inside:

if qtyYear.value = "year value" then
qtyYear.locked = True
qtyAmount.locked = True
qtyDate.locked = True
qtyLoc.locked = True
else
same as above.locked = False


this will look at the year, and if it is the current year the FIELDS WILL BE
LOCKED. From your post you said you wanted the user to change this year's
data, but first you said if the years matched, set locked to Yes. These
statements conflict each other. if you want to change current data, change
true to false and vise versa.
HTH

~K

 

[Marshall Barton]

I have 4 fields on a subform - QtyYear, QtyAmount, QtyDate & QtyLoc. If
QtyYear is not equal to the current year then I need locked set to Yes for
all 4 fields. If it is equal then I need it left as No. I only want the user
to be able to enter/change this years data.
I tried the expression builder for "event before update" but was not able to
complete it.

Typically this is done in the subform's Current event:

Dim bolPastYear As Boolean
bolPastYear = (QtyYear = Year(Date))
Me.QtyYear.Locked = bolPastYear
Me.QtyAmount.Locked = bolPastYear
Me.QtyDate.Locked = bolPastYear
Me.QtyLoc.Locked = bolPastYear

But what are you going to do on Jan 1 when you can not
change the record you entered the day before?

 

[NigelB]

Thanks. I'm getting a message:
Access can't find the macro 'Dim bolPastYear As Boolean ....
Macro doesn't exist or has not been saved
What should I do to correct ?

Also I'm a little confused on the line:
bolPastYear = (QtyYear = Year(Date))
Isn't this checking QtyYear to be = current year ? Or am I misunderstanding
? I need previous years locked but not the current year.
Jan1 should not be a problem.
Thanks again, Nigel

 

[Marshall Barton]

That message usually means that you tried to put the code in
the OnCurrent property instead of in the Current event
procedure. The OnCurrent property is supposed to contain
[Event Procedure] and you can easily get to the event
procedure by clicking on the [...] button in the right
margin of the property sheet.

You're correct, the line should be
bolPastYear = (QtyYear< Year(Date))

 

[NigelB]

Thanks again for your help. One small issue. I'm getting a compile error
method or data member not found
Me.QtyLoc.Locked = bolPastYear
If I take that line out everything is okay. QtyLoc is a combo box with
LimitToList set to Yes so that the user can only pick from the list of
locations. But I don't want previous years location data changed. Does the
code not like the combo box ?
Again thanks for assisting. Nigel

That message usually means that you tried to put the code in
the OnCurrent property instead of in the Current event
procedure. The OnCurrent property is supposed to contain
[Event Procedure] and you can easily get to the event
procedure by clicking on the [...] button in the right
margin of the property sheet.

You're correct, the line should be
bolPastYear = (QtyYear< Year(Date))
--
Marsh
MVP [MS Access]

Thanks. I'm getting a message:
Access can't find the macro 'Dim bolPastYear As Boolean ....
Macro doesn't exist or has not been saved
What should I do to correct ?

Also I'm a little confused on the line:
bolPastYear = (QtyYear = Year(Date))
Isn't this checking QtyYear to be = current year ? Or am I misunderstanding
? I need previous years locked but not the current year.
Jan1 should not be a problem.

 

[Wayne Morgan]

A combo box can be locked. It can even be locked if it has the focus,
although that would be a runtime error, not a compile error. Right off-hand,
I would suspect a spelling error so the compiler doesn't recognize the name.
Another possibility would be if QtyLoc is on a different form, such as a
parent form or subform when the code is on the other.

 

[NigelB]

Thanks for helping. Its all on the same form. I did a right click to get the
properties of the combo box & its control source says QtyLoc.

 

[Wayne Morgan]

Its Control Source is QtyLoc, but what is its Name property (Other tab)?

 

[NigelB]

Sorry, your right, it was called Combo10, I changed it to QtyLoc. Thanks.
But I still have an issue. This successfully prevents the user from
modifying the previous years data, but when I click in the QtyYear field of
the new record to add this year data, I get run time error '94' invalid use
of null.

 

[Wayne Morgan]

Open the query that is the form's Record Source and go to a new record in
the query. Do you get the same error? If so, post the SQL view of the query
in a reply.

 

[NigelB]

I have no problem in the query. Thanks.

Open the query that is the form's Record Source and go to a new record in
the query. Do you get the same error? If so, post the SQL view of the query
in a reply.

 

[NigelB]

When I click in the QtyYear field of the new record to add this years data, I
get run time error '94' invalid use of null. I believe this is true because
the code below runs & now QtyYear is null because its the new record, so you
get the error 94 on line bolPastYear = (QtyYear < Year(Date)). So how do I
not perform this code on the new record entry ? Thanks


Dim bolPastYear As Boolean
bolPastYear = (QtyYear < Year(Date))
Me.QtyYear.Locked = bolPastYear
Me.QtyAmount.Locked = bolPastYear
Me.QtyDate.Locked = bolPastYear
Me.QtyLoc.Locked = bolPastYear

 

[Wayne Morgan]

Yes, that could cause a problem. There are a couple of ways to resolve this.

1) Wrap the code in an If statement:

If Me.NewRecord Then
Me.QtyYear.Locked = False
Me.QtyAmount.Locked = False
Me.QtyDate.Locked = False
Me.QtyLoc.Locked = False
Else
bolPastYear = (QtyYear < Year(Date))
Me.QtyYear.Locked = bolPastYear
Me.QtyAmount.Locked = bolPastYear
Me.QtyDate.Locked = bolPastYear
Me.QtyLoc.Locked = bolPastYear
End If

2) Use the Nz() function for with Date.

bolPastYear = (Nz(QtyYear, Year(Date)) < Year(Date))

 

[NigelB]

Thanks for working with me, most appreciated. I used the "If" which worked
great as I have no knowledge of the Nz function. Pardon my ignorance but why
in the code is it Me.NewRecord, Me.QtyYear - what is the Me. for?

 

[Wayne Morgan]

"Me" is just shorthand for the current module, if the module is part of a
form or report, it includes the form or report. It's just shorter than
writing Forms!frmFormName!ctlControlName. If you are referring to a form
other than the one the code is attached to, you have to use the full name.
While you can get by without the Me at all, it makes it easier to read (you
know what is being referred to) and sometimes there is a problem with
duplicate names if you don't specify. Another reason for doing it is
laziness. Typing "Me." is usually shorter than the control name and once you
type the "." you get intellisense to help type the control name. This
reduces typing and spelling errors.

 

[NigelB]

Thankyou for all your help. Nigel

"Me" is just shorthand for the current module, if the module is part of a
form or report, it includes the form or report. It's just shorter than
writing Forms!frmFormName!ctlControlName. If you are referring to a form
other than the one the code is attached to, you have to use the full name.
While you can get by without the Me at all, it makes it easier to read (you
know what is being referred to) and sometimes there is a problem with
duplicate names if you don't specify. Another reason for doing it is
laziness. Typing "Me." is usually shorter than the control name and once you
type the "." you get intellisense to help type the control name. This
reduces typing and spelling errors.