VBA Code Help

[Guest]

In a form that is used to enter data on a daily, weekly, or monthly basis I
have a couple of cells that only will need to be unlocked if the day is
Friday. This is the code I have come up with:

If Text28.Text = "Friday" Then
Belt_Tension.Locked = False
Belt_Tension.BackColor = vbWhite
Belt_Tension.ForeColor = vbBlack
End If

Text28 is a box that is linked to the "DATE" field and formatted to display
the day of the week, my question is this: Where should this code go so that
the cell will unlock if the day of the week is Friday?

Thanks!

 

[Douglas J Steele]

Assuming it's a simple form (as opposed to the continuous form or data
sheet), you can put it on the form's Current event.

It's a good idea to put an Else clause there, so that Belt_Tension will be
set correctly if it's not Friday. (didn't know what you wanted the colours
to be)

If Text28.Text = "Friday" Then
Belt_Tension.Locked = False
Belt_Tension.BackColor = vbWhite
Belt_Tension.ForeColor = vbBlack
Else
Belt_Tension.Locked = True
Belt_Tension.BackColor = vbBlack
Belt_Tension.ForeColor = vbWhite
End If

 

[Guest]

I assume then that if I get an error that says:

RunTime Error 2185

You can't reference a property or method for a control unless the control
has the focus.

That I do not just have a simple form.

 

[Douglas J Steele]

Try putting Belt_Tension.SetFocus before you change the properties.

 

[Guest]

That sort of helped, but what I am looking for it to do is to change the
prpoerties after the date has been enetered. After the date is entered into
the "Date" field, text28 updates to the corresponding day. So when that field
changes, if it changes to Friday, then the Belt Tension (and other fields)
should allow data entry. Otherwise it should remain grayed out.

I tried using the OnChange property of the Text28 but that didn't work either.

 

[George Nicholson]

I think that error is due to you using the .Text property. If I remember
correctly, the Text property is only available if that specific control has
the focus. So: either use Text28.SetFocus before Text28.Text or use
Text28.Value (which does not require focus).


HTH,

 

[Douglas J Steele]

Good catch, George. You're absolutely correct.

 

[Guest]

I feel so stupid, I am actually going to school to be a programmer and
graduate in 2 weeks, but this seems to be beyond me. If I put Text28.Value =
"Friday" it does not work, I think because the Value is not Friday, but the
date value, is there VBA code for Convert.ToString?

That way I can just change the value to a string and then do the comparison.

Sorry guys I feel STOOPUD!

 

[Douglas J Steele]

If you've got a date, you can convert it to Friday using Format(Text28,
"dddd"). Format(Text28, "ddd") will return Fri.

My recommendation, though, would be NOT to do that. In the event that your
user has a non-English operating system, you'll run into problems.

Better would be:

If Weekday(Text28) = vbFriday Then

 

[Ron Hinds]

Put it in either the Text28_AfterUpdate event or the Text28_Exit (my
preference) event.

 

[Guest]

I actually have TWO date fields tied to the same data source, one displays
the date in American format (mm/dd/yyyy) the other is formatted to show the
DOW (dddd).

I have spent the whole day on this but it is still not working the way I
want it to. The code works if I enter the date, scroll through all the
fields, and come back. All three fields I have grayed out are available and
unlocked, but I want them to unlock as soon as I enter a date with a Friday
day.


Now this code:

Private Sub Form_Current()

If Weekday([Text28]) = vbFriday Then
Belt_Tension.Locked = False
Belt_Tension.BackColor = vbWhite
Belt_Tension.ForeColor = vbBlack

Else
Belt_Tension.Locked = True
Belt_Tension.BackColor = 9868950
Belt_Tension.ForeColor = 9868950


End If

End Sub

Gives me an invalid use of Null error

 

[Guest]

PERFECT that is just what I wanted!

Put it in either the Text28_AfterUpdate event or the Text28_Exit (my
preference) event.

 

[Ron Hinds]

Move your code to the Text28_Exit event. You might also want to throw in a
check to see if Text28 is nothing:

Private Sub Text28_Exit(Cancel As Integer)
If Len([Text28]) < 1 Then
If Weekday([Text28]) = vbFriday Then
Belt_Tension.Locked = False
Belt_Tension.BackColor = vbWhite
Belt_Tension.ForeColor = vbBlack
Else
Belt_Tension.Locked = True
Belt_Tension.BackColor = 9868950
Belt_Tension.ForeColor = 9868950
End If
End If
End Sub

I actually have TWO date fields tied to the same data source, one displays
the date in American format (mm/dd/yyyy) the other is formatted to show the
DOW (dddd).

I have spent the whole day on this but it is still not working the way I
want it to. The code works if I enter the date, scroll through all the
fields, and come back. All three fields I have grayed out are available and
unlocked, but I want them to unlock as soon as I enter a date with a Friday
day.


Now this code:

Private Sub Form_Current()

If Weekday([Text28]) = vbFriday Then
Belt_Tension.Locked = False
Belt_Tension.BackColor = vbWhite
Belt_Tension.ForeColor = vbBlack

Else
Belt_Tension.Locked = True
Belt_Tension.BackColor = 9868950
Belt_Tension.ForeColor = 9868950


End If

End Sub

Gives me an invalid use of Null error

If you've got a date, you can convert it to Friday using Format(Text28,
"dddd"). Format(Text28, "ddd") will return Fri.

My recommendation, though, would be NOT to do that. In the event that your
user has a non-English operating system, you'll run into problems.

Better would be:

If Weekday(Text28) = vbFriday Then


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Text28.Value
= but
the or
data Belt_Tension
will the
day formatted
to code
go