Rule for response to a field

G

GraemeG

Registered User.
Local time
Today, 20:52
Joined
Jan 22, 2011
Messages
212
hello

I have the following code which helps restrict a repsonse.
The first part works. i.e if the repsonse is 2009 then it is invalid or 2011+ is valid and can move on.

however the second part where I want it to state IF the repsonse given is 20 years + the current year then it is invalid.

Code: 
Private Sub Entrance_Doors___Flats__Renew_Year__20_LE_AfterUpdate()
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] < Format(Date, "yyyy") Then
    MsgBox "Flat Entrance Doors Renew Year Invalid!"
    End If
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] = Format(Date, "yyyy") + 20 Then
    MsgBox "Flat Entrance Doors Renew Year Invalid!"
    End If
End Sub

Any help much appreciated!
 
looks like that should work, check that you dont need to convert the year entered or the year+20, you can also just use year(date)+20
 
Hi nathan thanks for repsonse,
Sorry I dont understand what you mean in terms of convert or just use (date)+20
 
CSTR, CLNG, CINT. It might be the field in your form is string, and the calculation is of number type. I would use clng on either side, see if that helps

if clng(Me.[Entrance Doors - Flats (Renew Year) 20 LE]) = clng(Format(Date, "yyyy") + 20 ) then

or if these are the only 2 criteria that you are checking for, you could handle the 2nd part in the else of the if
 
year(date)+20, you dont need to format as yyyy, it will just return the year number, i.e. 2011
 
Hi,
Thanks that now works in terms of validation - Great!!
However if that field has been entered into but is not required and then removed (i.e. left blank) it throughs up a null debug and highlights that row of code yellow.
Can I stop this if the surveyor makes a mistake?
Also due you know if you can bold or italic some of the msgbox?
 
Code: 
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] < Format(Date, "yyyy") Then
    MsgBox "Flat Entrance Doors Renew Year Invalid!"
ElseIf Me.[Entrance Doors - Flats (Renew Year) 20 LE] = Format(Date, "yyyy") + 20 Then
    MsgBox "Flat Entrance Doors Renew Year Invalid!"
Else
    'Valid Entry

End If
 
DCrake said:
Code: 
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] < Format(Date, "yyyy") Then
    MsgBox "Flat Entrance Doors Renew Year Invalid!"
ElseIf Me.[Entrance Doors - Flats (Renew Year) 20 LE] = Format(Date, "yyyy") + 20 Then
    MsgBox "Flat Entrance Doors Renew Year Invalid!"
Else
    'Valid Entry
 
End If
Click to expand...

Thanks.
However I am getting an error without an end if.
Can I not use the code further above and then somehow build in the Valid Entry?
 
If CLng(Me.[Entrance Doors - Flats (Renew Year) 20 LE] >= CLng(Format(Date, "yyyy") + 20)) Then

This part highlights yellow stating Null debug error
 
I would use

if not isnull(Me.[Entrance Doors - Flats (Renew Year) 20 LE] ) then
If CLng(Me.[Entrance Doors - Flats (Renew Year) 20 LE] >= CLng (Format(Date, "yyyy") + 20)) Then

Custom message boxes i have used in the past, just a form with the images on (easy to get hold of on goole) and then labels with for your messages, then you can make them bold italic etc.

me.imgExclamation.visible = true
me.imgInformation.visible = false
me.imgQuestion.visible = false
me.msgcaption.caption = "You have done this wrong"
me.msgcaption.font.bold = true

something like that.
 
I really appreciate all the help!
But I must be a noob, just can't get it to work.
Code: 
Private Sub Entrance_Doors___Flats__Renew_Year__20_LE_AfterUpdate()
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] < Format(Date, "yyyy") Then
    MsgBox "Renew Year Invalid: < Current Year!"
    End If
If Not IsNull(Me.[Entrance Doors - Flats (Renew Year) 20 LE]) Then
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] = Format(Date, "yyyy") + 20 Then
    MsgBox "Renew Year Invalid: Exceeds life expectancy!"
End If
End Sub
 
you need another end if before end sub.

If 1st IF (current year)

END IF

if 2nd IF (is null)
if 3rd IF 20 years +

end if
end if
 
I have added the END IF at the end but know it flags an error up no matter what repsonse is entered. But if prior to current year it flags up both messages. But atleast now it allows a repsonse to be added, then deleted without a null error!

Any ideas? Help Much Appreciated
 
Last edited:
Code: 
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] < Format(Date, "yyyy") Then
    MsgBox "Flat Entrance Doors Renew Year Invalid!"
ElseIf Me.[Entrance Doors - Flats (Renew Year) 20 LE] = Format(Date, "yyyy") + 20 Then
    MsgBox "Flat Entrance Doors Renew Year Invalid!"
Else
    'Valid Entry
 
End If

What was wrong with this code?
 
That code did not work.
I needed to add CLng in order for the year + 20 calc to work.
But when I do that i am getting a null 94 error and need to debug.

I thought the following might work but stil lget Null 94 error.

Code: 
Private Sub Entrance_Doors___Flats__Renew_Year__20_LE_AfterUpdate()
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] < Format(Date, "yyyy") Then
    MsgBox "Renew Year Invalid:  < Current Year!"
ElseIf CLng(Me.[Entrance Doors - Flats (Renew Year) 20 LE] >= CLng(Format(Date, "yyyy") + 20)) Then
    MsgBox "Renew Year Invalid:  Exceeds Life Expectancy!"
ElseIf CLng(Me.[Entrance Doors - Flats (Renew Year) 20 LE] = Null) Then
    'Valid Entry
End If
End Sub

Please see attached (sorry need to add the afterupdate event procedure on form)
 

Attachments

Last edited:
Try this


Code: 
Dim TestYear as Integer
Dim MaxYear As Integer

TestYear = Nz(Me.[Entrance Doors - Flats (Renew Year) 20 LE] ,0)

MaxYear = Year(DateAdd("yyyy",15,Date()))

Select Case TestYear
      Case < Year(Date())
      .... in the past
      Case > MaxYear
      .... too far in the future
      Case Else
      .... Ok
End Select
 
Still no luck states compile error: syntax error and debugs to the first error message .. in the past
 
It's just no good saying it does not work, give me a clue, what is not working? what is it saying? have you tried stepping through the code?
 
I have changed the code slightly and it now works.
But when a repsonse is made by mistake and removed and then try to save record, move on etc. I.e. no response it falgs up first error message < Current Year!.

Code: 
Private Sub Entrance_Doors___Flats__Renew_Year__20_LE_AfterUpdate()
Dim TestYear As Integer
Dim MaxYear As Integer
TestYear = Nz(Me.[Entrance Doors - Flats (Renew Year) 20 LE], 0)
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
End Select
End Sub
 
You must log in or register to reply here.

Similar threads

A
FILTER PRINT REPORT
Replies
2
Views
666
arnelgp
arnelgp
A
Filter Records
Replies
1
Views
825
June7
June7
N
Solved In MS Access if the VBA code is taking longer than required how do we force the code to next
Replies
14
Views
837
nector
N
A
Solved VBA AfterUpdate event
2
Replies
20
Views
2,975
Isaac
Isaac
A
Restrict the number of records entered in form
2
Replies
23
Views
2,233
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom