Change Vaule of main form field based on subform field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smcgrath - 21 Mar 2007 19:09 GMT

I have a datsheet main form for mortgage folders with a field called File
Location which defaults to "Basement Vault" at the table level. I have a
datasheet subform called fileoutsubfrm which contains fields SentDate, SentBy,
Sent To Whom (Cmbobox) and ReturnedDate. The master/child link is the
ACCTNBR field. I am attempting to have the file location field update to the
value of the Sent to Whom from the subform if the Sent to Whom is not null
and the ReturnedDate is Null.

This is my attempt at coding the On Current event of the main form - I am
getting an invalid reference to form error using this:

If IsNull(Me!ConsumerFolderOutSubfrm.Form![Sent_To_Whom])=False And IsNull
(Me!ConsumerFolderOutSubfrm.Form![ReturnedDate] = True Then
Me.FileLocation = Me!ConsumerFolderOutSubfrm.Form![Sent_To_Whom]
Else
Me.FileLocation = Me.FileLocation
End If

I verified that the name of the subform control is ConsumerFolderOutsubfrm -
What is the proper syntax to reference the fields?
Also, should I put the same code in the after update event of the Sent To
Whom field of the subform?

Reply to this Message

SusanV - 21 Mar 2007 20:01 GMT

Instead of using If IsNull(...) = False use If Not IsNull(...)
Also if you aren't going to change anything on the Else, you can simply not
use Else at all

If this = true then
do something
End If

Signature

hth,
SusanV

>I have a datsheet main form for mortgage folders with a field called File
> Location which defaults to "Basement Vault" at the table level. I have a
[quoted text clipped - 22 lines]
> Also, should I put the same code in the after update event of the Sent To
> Whom field of the subform?

Reply to this Message

smcgrath - 22 Mar 2007 14:00 GMT

This is exactly what I have in the On Current Event of the Main Form:

If Not IsNull(Me.ConsumerFolderOutSubfrm![CboSentToWhom]) And IsNull(Me.
ConsumerFolderOutSubfrm![ReturnedDate]) Then
Me.FileLocation = Me.ConsumerFolderOutSubfrm![CboSentToWhom]
Else
Me.FileLocation = Me.FileLocation
End If

Which by the way did update the records, but every time I open the form I get
a Runtime Error 2455 - Why? Also, again, should this code be put anywhere
else?

>I have a datsheet main form for mortgage folders with a field called File
>Location which defaults to "Basement Vault" at the table level. I have a
[quoted text clipped - 18 lines]
>Also, should I put the same code in the after update event of the Sent To
>Whom field of the subform?

Reply to this Message

SusanV - 22 Mar 2007 14:59 GMT

If you click debug which line of code is high-lighted?

> This is exactly what I have in the On Current Event of the Main Form:
>
[quoted text clipped - 36 lines]
>>Also, should I put the same code in the after update event of the Sent To
>>Whom field of the subform?

Reply to this Message

smcgrath - 22 Mar 2007 15:56 GMT

The first line

>If you click debug which line of code is high-lighted?
>
[quoted text clipped - 3 lines]
>>>Also, should I put the same code in the after update event of the Sent To
>>>Whom field of the subform?

Reply to this Message

SusanV - 22 Mar 2007 16:09 GMT

You need Form! in between the subform name and the subform control so it's
Me.subformName.Form!ControlName
Your line is missing the Form! reference. You now have:

If Not IsNull(Me.ConsumerFolderOutSubfrm![CboSentToWhom]) And IsNull(Me.
ConsumerFolderOutSubfrm![ReturnedDate]) Then

It should be:

If Not IsNull(Me.ConsumerFolderOutSubfrm.Form![CboSentToWhom]) And
IsNull(Me.
ConsumerFolderOutSubfrm.Form![ReturnedDate]) Then

Signature

hth,
SusanV

> The first line
>
[quoted text clipped - 6 lines]
>>>>To
>>>>Whom field of the subform?

Reply to this Message

smcgrath - 22 Mar 2007 17:45 GMT

I am still receiving the Runtime error with the fix - however I read about
issues with the On Current event firing twice with forms and subforms but I
do not know how to trap the error and get rid of it. I don't know the code.
Any ideas?

>You need Form! in between the subform name and the subform control so it's
>Me.subformName.Form!ControlName
[quoted text clipped - 14 lines]
>>>>>To
>>>>>Whom field of the subform?

Reply to this Message

SusanV - 22 Mar 2007 18:22 GMT

Here's a great page from TechNet on error handling:

http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part
2/ch08.mspx

>I am still receiving the Runtime error with the fix - however I read about
> issues with the On Current event firing twice with forms and subforms but
[quoted text clipped - 21 lines]
>>>>>>To
>>>>>>Whom field of the subform?

Reply to this Message