Change Vaule of main form field based on subform field

  • Thread starter smcgrath via AccessMonster.com
  • Start date
S

smcgrath via AccessMonster.com

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?
 
S

SusanV

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
 
S

smcgrath via AccessMonster.com

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
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?
Click to expand...
 
S

SusanV

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

smcgrath via AccessMonster.com said:
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
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?
Click to expand...
Click to expand...
 
S

SusanV

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


--
hth,
SusanV



smcgrath via AccessMonster.com said:
The first line
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:
Click to expand...
[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?
Click to expand...
Click to expand...
Click to expand...
 
S

smcgrath via AccessMonster.com

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
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
The first line
Click to expand...
[quoted text clipped - 6 lines]
Click to expand...
 
S

SusanV

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

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


smcgrath via AccessMonster.com said:
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
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
The first line
Click to expand...
[quoted text clipped - 6 lines]
To
Whom field of the subform?
Click to expand...
Click to expand...
Click to expand...
 
Top