run time error '2465' / Form issue

[Guest]

Hello

Anybody available??? Please help

Below is what I have in the Form, in the PYE field, AfterUpdate event procedure. But it is not working...

Private Sub PYE_AfterUpdate(

Me![5500 Due] = DateAdd("m", 7, Me![PYE]
Me![PBGC Due #2] = DateAdd("m", 10, Me![PYE] + 15
Me![SAR] = DateAdd("m", 2, Me![5500 Due]

If Me![Plan Participants] >= 500 The
Me![PBGC Due #1] = DateAdd("m", 2, Me![PYE]
Els
Me![PBGC Due #1] = Nul
End I

End Su

The problem with the expression is with the If statement as the Plan Participants is not a field linked directly to the Form; instead it is a field from other Table called Plans. I cannot bring that field to the Form because there is no direct relationship so to use Me! is not probably right. Should I use the Dim record as...expression? Is there a solution to this issue? With the above expression, I am getting a run time error '2465'. Microsoft Access can't find the field 'Plan Participants' referred to in your expression

BIG THANKS for anyone who can help

Regards

Natali

 

[Graham Mandeno]

Hi Natalia

"Me!" refers explicitly to controls and fields of the form where the code is
executing.

If [Plan Participants] is in another table, then you must look up its value
before you can use it in your calculations. The easiest (though perhaps not
the most efficient) way of doing this is with DLookup:

If DLookup("[Plan Participants]", "Name of other table", _
"criteria string to select the desired record") >= 500 Then

If there is only one record in the other table (unlikely) then you can leave
the criteria string blank, otherwise you must use it to select the
appropriate record.

--
Good Luck!

Graham Mandeno [Access MVP]
New Zealand - Home of Lord of the Rings

Hello,

Anybody available??? Please help!

Below is what I have in the Form, in the PYE field, AfterUpdate event

procedure. But it is not working....

Private Sub PYE_AfterUpdate()


Me![5500 Due] = DateAdd("m", 7, Me![PYE])
Me![PBGC Due #2] = DateAdd("m", 10, Me![PYE] + 15)
Me![SAR] = DateAdd("m", 2, Me![5500 Due])


If Me![Plan Participants] >= 500 Then
Me![PBGC Due #1] = DateAdd("m", 2, Me![PYE])
Else
Me![PBGC Due #1] = Null
End If

End Sub

The problem with the expression is with the If statement as the Plan

Participants is not a field linked directly to the Form; instead it is a
field from other Table called Plans. I cannot bring that field to the Form
because there is no direct relationship so to use Me! is not probably right.
Should I use the Dim record as...expression? Is there a solution to this
issue? With the above expression, I am getting a run time error '2465'.
Microsoft Access can't find the field 'Plan Participants' referred to in
your expression.

 

[Guest]

Graha

Thank you. It is working great! You are the best

Regards

Natalia