Rule for response to a field

[DCrake]

change to

TestYear = Trim(Me.[Entrance Doors - Flats (Renew Year) 20 LE] & "")

insert

If TestYear = "" then
   Exit sub
end if

 

[GraemeG]

Private Sub Entrance_Doors___Flats__Renew_Year__20_LE_AfterUpdate()
Dim TestYear As Integer
Dim MaxYear As Integer
TestYear = Trim(Me.[Entrance Doors - Flats (Renew Year) 20 LE] & "")
MaxYear = Year(DateAdd("yyyy", 15, Date))
Select Case TestYear
      Case Is < Year(Date)
      MsgBox "Renew Year Invalid: < Current Year!"
      Case Is > MaxYear
      MsgBox "Renew Year Invalid: Exceeds Life Expectancy!"
      Case Else
If TestYear = "" Then
   Exit Sub
End If
End Select
End Sub

If you now enter a valid year it give s type mismatch error and points towards:
If TestYear = "" Then

i think it might be easier to keep the original code that worked with 94 null error. Then maybe there is a way to lock that field when not required?

I.e.
The surveyor first uses a combo box to slect the element:
Example.
NONE
FIRE DOOR
STANDARD DOOR

So the renew year is only required when a door is selected (with the original year valid rules)
But to stop a null error i.e. surveyor would not leave blank if he selected a door.
But if they selected none. then the renew year would be locked?
Is something like that possible?

 

[DCrake]

Private Sub Entrance_Doors___Flats__Renew_Year__20_LE_AfterUpdate()
Dim TestYear As Integer
Dim MaxYear As Integer
TestYear = Trim(Me.[Entrance Doors - Flats (Renew Year) 20 LE] & "")
MaxYear = Year(DateAdd("yyyy", 15, Date))
Select Case TestYear
      Case Is < Year(Date)
      MsgBox "Renew Year Invalid: < Current Year!"
      Case Is > MaxYear
      MsgBox "Renew Year Invalid: Exceeds Life Expectancy!"
      Case Else
If TestYear = "" Then
   Exit Sub
End If
End Select
End Sub

You pulling my plonker here.

What is the name of the field you are reading? Is there brackets in the name or not.


Entrance_Doors___Flats__Renew_Year__20_LE
or

Entrance Doors - Flats (Renew Year) 20 LE

The first rule of thumb is DO NOT put spaces in fieldnames.
The Second DO NOT use anything other than letters or digits in tour names.

This makes for easier coding

 

[GraemeG]

haha sorry.
Yeah there is spaces and brackets i nthe field names.
I now know I shouldnt have done this.
But someone has said I can just rename the text box on the form to help with coding rather than changes all my field names which would effect alot of data imports.

 

[DCrake]

The name of the control can be different from the name of the control source. If the control source (Actual field name) has invalid characters you can rename the textbox to suit your needs.

such as

Control Name :TxtSampleName
Control Source :Sample name

When you perform validation checks you need to refer to the control name not the control source.

Hope that helps

 

[GraemeG]

Yeah I have changed the txt name. But code just will not work.
I think I am gonna give up and if the surveyor makes a mistake they will have to end debug!

 

[GraemeG]

Re: Rule for response to a field (RESOLVED)

Now resolved!
Code works:

Private Sub EntranceDoorsFlatsRenewYear_AfterUpdate()
If IsNull(EntranceDoorsFlatsRenewYear.Value) Then
    Me.[EntranceDoorsFlatsRenewYear] = ""
ElseIf Me.[EntranceDoorsFlatsRenewYear] < Format(Date, "yyyy") Then
    MsgBox "Renew Year Invalid:  Less than current year!"
ElseIf CLng(Me.[EntranceDoorsFlatsRenewYear] >= CLng(Format(Date, "yyyy") + 20)) Then
    MsgBox "Renew Year Invalid:  Exceeds life expectancy!"
End If
End Sub

 

[DCrake]

Good to see you have solved the issue. Now for the next one