Reading a field when field is locked?

  • Thread starter Thread starter Lloyd
  • Start date Start date
L

Lloyd

When a user opens up a form to view it, I have the form and all subforms
locked using Allen Browne's method at http://www.allenbrowne.com/ser-56.html
which works great!

I ran into one problem however in my code that runs on the forms current
event. My code checks to see if a field is null or not on one of the
subforms. If there is data in the field it works fine, but when there is no
record in the subform, the first part of the code runs, as if it found data
in the MainDR field yet the form is locked and no data is there since no
record has yet been entered. Is there another way I can test to see if there
is data in the MainDR field

If Not IsNull(Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]) Then
'MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
' MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If
 
"" <> Null
IsNull() is an unreliable method to getting the reality, due to Access
way(s) of determining if it is really Null. So, tack on a couple of
doublequotes, and check the len, thus eliminating the nulls altogether.

e.g. IF Len([StringVal] & "") > 0 Then ...
 
Thank you for your help. I tried using Len as you suggested (I hadnt
considered it) and was hopefull, but it is not working. I get an error that
says error 2465, cant find the field | refered to in your expression. Any
suggestions?

If Len([Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]] & "") > 0 Then

MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If

S.Clark said:
"" <> Null
IsNull() is an unreliable method to getting the reality, due to Access
way(s) of determining if it is really Null. So, tack on a couple of
doublequotes, and check the len, thus eliminating the nulls altogether.

e.g. IF Len([StringVal] & "") > 0 Then ...



Lloyd said:
When a user opens up a form to view it, I have the form and all subforms
locked using Allen Browne's method at http://www.allenbrowne.com/ser-56.html
which works great!

I ran into one problem however in my code that runs on the forms current
event. My code checks to see if a field is null or not on one of the
subforms. If there is data in the field it works fine, but when there is no
record in the subform, the first part of the code runs, as if it found data
in the MainDR field yet the form is locked and no data is there since no
record has yet been entered. Is there another way I can test to see if there
is data in the MainDR field

If Not IsNull(Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]) Then
'MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
' MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If
Click to expand...
Click to expand...
 
Lloyd said:
Thank you for your help. I tried using Len as you suggested (I hadnt
considered it) and was hopefull, but it is not working. I get an error
that
says error 2465, cant find the field | refered to in your expression. Any
suggestions?

If Len([Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]] & "") > 0 Then

MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If

S.Clark said:
"" <> Null
IsNull() is an unreliable method to getting the reality, due to Access
way(s) of determining if it is really Null. So, tack on a couple of
doublequotes, and check the len, thus eliminating the nulls altogether.

e.g. IF Len([StringVal] & "") > 0 Then ...



Lloyd said:
When a user opens up a form to view it, I have the form and all
subforms
locked using Allen Browne's method at
http://www.allenbrowne.com/ser-56.html
which works great!

I ran into one problem however in my code that runs on the forms
current
event. My code checks to see if a field is null or not on one of the
subforms. If there is data in the field it works fine, but when there
is no
record in the subform, the first part of the code runs, as if it found
data
in the MainDR field yet the form is locked and no data is there since
no
record has yet been entered. Is there another way I can test to see if
there
is data in the MainDR field

If Not IsNull(Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]) Then
'MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
' MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If
Click to expand...
Click to expand...
Click to expand...
[Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]]
Click to expand...
The above expression returns a reference to the subform control on the main
form. To refer to controls on a subform you must add .Form, like this:

[Forms!frmcaseupdate!subfrmRelatedDRs.Form![MainDR]]
 
Stewart,

I thought my way checking the subform may be wrong, I'm still learning. But
I tried your suggestion and it still gives the same error 2465, cant find the
field | referred to in your expression.

If Len([Forms!frmcaseupdate!subfrmRelatedDRs.Form![MainDR]] & "") > 0 Then


Stuart McCall said:
Lloyd said:
Thank you for your help. I tried using Len as you suggested (I hadnt
considered it) and was hopefull, but it is not working. I get an error
that
says error 2465, cant find the field | refered to in your expression. Any
suggestions?

If Len([Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]] & "") > 0 Then

MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If

S.Clark said:
"" <> Null
IsNull() is an unreliable method to getting the reality, due to Access
way(s) of determining if it is really Null. So, tack on a couple of
doublequotes, and check the len, thus eliminating the nulls altogether.

e.g. IF Len([StringVal] & "") > 0 Then ...



:

When a user opens up a form to view it, I have the form and all
subforms
locked using Allen Browne's method at
http://www.allenbrowne.com/ser-56.html
which works great!

I ran into one problem however in my code that runs on the forms
current
event. My code checks to see if a field is null or not on one of the
subforms. If there is data in the field it works fine, but when there
is no
record in the subform, the first part of the code runs, as if it found
data
in the MainDR field yet the form is locked and no data is there since
no
record has yet been entered. Is there another way I can test to see if
there
is data in the MainDR field

If Not IsNull(Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]) Then
'MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
' MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If
Click to expand...
Click to expand...
[Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]]
Click to expand...
The above expression returns a reference to the subform control on the main
form. To refer to controls on a subform you must add .Form, like this:

[Forms!frmcaseupdate!subfrmRelatedDRs.Form![MainDR]]
Click to expand...
 
Lloyd said:
Stewart,

I thought my way checking the subform may be wrong, I'm still learning.
But
I tried your suggestion and it still gives the same error 2465, cant find
the
field | referred to in your expression.

If Len([Forms!frmcaseupdate!subfrmRelatedDRs.Form![MainDR]] & "") > 0 Then
Click to expand...
<snip>

In that case you need to check carefully that:

a) there is in fact a field in the subform's recordset called MainDR
b) there is a control on the subform which has MainDR as its controlsource
property
 
Stuart,

I double checked and the field is on the form and its controlsource is set
to MainDR. I even changed the code to point to another field "AssociatedDR"
which is on the form, and I get the same error. If I remove the code that
locks the form/subform, it works fine. I tried unlocking the field as shown
below before the code runs. I removed the first set of brackets from the
second line of code that contains the Len function and now I get another
error #2427 "You entered an expression that has no value"....Not sure if this
error is better or not?

Forms!frmcaseupdate!subfrmRelatedDRs.Form![AssociatedDR].Locked = False

If Len(Forms!frmcaseupdate!subfrmRelatedDRs.Form![AssociatedDR] & "") > 0 Then
Stuart McCall said:
Lloyd said:
Stewart,

I thought my way checking the subform may be wrong, I'm still learning.
But
I tried your suggestion and it still gives the same error 2465, cant find
the
field | referred to in your expression.

If Len([Forms!frmcaseupdate!subfrmRelatedDRs.Form![MainDR]] & "") > 0 Then
Click to expand...
<snip>

In that case you need to check carefully that:

a) there is in fact a field in the subform's recordset called MainDR
b) there is a control on the subform which has MainDR as its controlsource
property
Click to expand...
 
Your Square brackets were out of whack, too.

Unless you have special characters(space, hypen, ampersand, etc) that would
require you to use the brackets, avoid them.

Lloyd said:
Thank you for your help. I tried using Len as you suggested (I hadnt
considered it) and was hopefull, but it is not working. I get an error that
says error 2465, cant find the field | refered to in your expression. Any
suggestions?

If Len([Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]] & "") > 0 Then

MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If

S.Clark said:
"" <> Null
IsNull() is an unreliable method to getting the reality, due to Access
way(s) of determining if it is really Null. So, tack on a couple of
doublequotes, and check the len, thus eliminating the nulls altogether.

e.g. IF Len([StringVal] & "") > 0 Then ...



Lloyd said:
When a user opens up a form to view it, I have the form and all subforms
locked using Allen Browne's method at http://www.allenbrowne.com/ser-56.html
which works great!

I ran into one problem however in my code that runs on the forms current
event. My code checks to see if a field is null or not on one of the
subforms. If there is data in the field it works fine, but when there is no
record in the subform, the first part of the code runs, as if it found data
in the MainDR field yet the form is locked and no data is there since no
record has yet been entered. Is there another way I can test to see if there
is data in the MainDR field

If Not IsNull(Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]) Then
'MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
' MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If
Click to expand...
Click to expand...
Click to expand...
 
thank you for your help, I removed the brackets, which has changed the error.
Now I get an error #2427, "you entered an expression tha thas no value"

If Len(Forms!frmcaseupdate!subfrmRelatedDRs.Form!AssociatedDR & "") > 0 Then

S.Clark said:
Your Square brackets were out of whack, too.

Unless you have special characters(space, hypen, ampersand, etc) that would
require you to use the brackets, avoid them.

Lloyd said:
Thank you for your help. I tried using Len as you suggested (I hadnt
considered it) and was hopefull, but it is not working. I get an error that
says error 2465, cant find the field | refered to in your expression. Any
suggestions?

If Len([Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]] & "") > 0 Then

MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If

S.Clark said:
"" <> Null
IsNull() is an unreliable method to getting the reality, due to Access
way(s) of determining if it is really Null. So, tack on a couple of
doublequotes, and check the len, thus eliminating the nulls altogether.

e.g. IF Len([StringVal] & "") > 0 Then ...



:

When a user opens up a form to view it, I have the form and all subforms
locked using Allen Browne's method at http://www.allenbrowne.com/ser-56.html
which works great!

I ran into one problem however in my code that runs on the forms current
event. My code checks to see if a field is null or not on one of the
subforms. If there is data in the field it works fine, but when there is no
record in the subform, the first part of the code runs, as if it found data
in the MainDR field yet the form is locked and no data is there since no
record has yet been entered. Is there another way I can test to see if there
is data in the MainDR field

If Not IsNull(Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]) Then
'MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
' MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If
Click to expand...
Click to expand...
Click to expand...
 
Like Stuart said... check for name missssspellllings.
If nothing else, set a breakpoint on that line of code, and try to return a
value in the Immediate Window (Ctrl+G) for it by using:

? Forms!frmcaseupdate!subfrmRelatedDRs.Form!AssociatedDR

Lloyd said:
thank you for your help, I removed the brackets, which has changed the error.
Now I get an error #2427, "you entered an expression tha thas no value"

If Len(Forms!frmcaseupdate!subfrmRelatedDRs.Form!AssociatedDR & "") > 0 Then

S.Clark said:
Your Square brackets were out of whack, too.

Unless you have special characters(space, hypen, ampersand, etc) that would
require you to use the brackets, avoid them.

Lloyd said:
Thank you for your help. I tried using Len as you suggested (I hadnt
considered it) and was hopefull, but it is not working. I get an error that
says error 2465, cant find the field | refered to in your expression. Any
suggestions?

If Len([Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]] & "") > 0 Then

MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If

:

"" <> Null
IsNull() is an unreliable method to getting the reality, due to Access
way(s) of determining if it is really Null. So, tack on a couple of
doublequotes, and check the len, thus eliminating the nulls altogether.

e.g. IF Len([StringVal] & "") > 0 Then ...



:

When a user opens up a form to view it, I have the form and all subforms
locked using Allen Browne's method at http://www.allenbrowne.com/ser-56.html
which works great!

I ran into one problem however in my code that runs on the forms current
event. My code checks to see if a field is null or not on one of the
subforms. If there is data in the field it works fine, but when there is no
record in the subform, the first part of the code runs, as if it found data
in the MainDR field yet the form is locked and no data is there since no
record has yet been entered. Is there another way I can test to see if there
is data in the MainDR field

If Not IsNull(Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]) Then
'MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
' MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If
Click to expand...
Click to expand...
Click to expand...
 
You might try referring to the actual collections instead of the Expression
Service:


If
Len(Forms("frmCaseUpdate").Controls("subfrmRelatedDRs").Form.Controls("AssociatedDR").Value & "") > 0 Then

Sometimes I have better luck this way, for whatever reason.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



Lloyd said:
thank you for your help, I removed the brackets, which has changed the error.
Now I get an error #2427, "you entered an expression tha thas no value"

If Len(Forms!frmcaseupdate!subfrmRelatedDRs.Form!AssociatedDR & "") > 0 Then

S.Clark said:
Your Square brackets were out of whack, too.

Unless you have special characters(space, hypen, ampersand, etc) that would
require you to use the brackets, avoid them.

Lloyd said:
Thank you for your help. I tried using Len as you suggested (I hadnt
considered it) and was hopefull, but it is not working. I get an error that
says error 2465, cant find the field | refered to in your expression. Any
suggestions?

If Len([Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]] & "") > 0 Then

MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If

:

"" <> Null
IsNull() is an unreliable method to getting the reality, due to Access
way(s) of determining if it is really Null. So, tack on a couple of
doublequotes, and check the len, thus eliminating the nulls altogether.

e.g. IF Len([StringVal] & "") > 0 Then ...



:

When a user opens up a form to view it, I have the form and all subforms
locked using Allen Browne's method at http://www.allenbrowne.com/ser-56.html
which works great!

I ran into one problem however in my code that runs on the forms current
event. My code checks to see if a field is null or not on one of the
subforms. If there is data in the field it works fine, but when there is no
record in the subform, the first part of the code runs, as if it found data
in the MainDR field yet the form is locked and no data is there since no
record has yet been entered. Is there another way I can test to see if there
is data in the MainDR field

If Not IsNull(Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]) Then
'MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
' MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If
Click to expand...
Click to expand...
Click to expand...
 
Jack,

thanks for your assistance....I tried your suggestion and same problem, when
the record/form is locked and there is no record to read, it gives the same
error "You entered an expression that has no value". The code works fine if
there is data in the locked field, but when no record exists, it errors out.


Jack Leach said:
You might try referring to the actual collections instead of the Expression
Service:


If
Len(Forms("frmCaseUpdate").Controls("subfrmRelatedDRs").Form.Controls("AssociatedDR").Value & "") > 0 Then

Sometimes I have better luck this way, for whatever reason.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



Lloyd said:
thank you for your help, I removed the brackets, which has changed the error.
Now I get an error #2427, "you entered an expression tha thas no value"

If Len(Forms!frmcaseupdate!subfrmRelatedDRs.Form!AssociatedDR & "") > 0 Then

S.Clark said:
Your Square brackets were out of whack, too.

Unless you have special characters(space, hypen, ampersand, etc) that would
require you to use the brackets, avoid them.

:
Thank you for your help. I tried using Len as you suggested (I hadnt
considered it) and was hopefull, but it is not working. I get an error that
says error 2465, cant find the field | refered to in your expression. Any
suggestions?

If Len([Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]] & "") > 0 Then

MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If

:

"" <> Null
IsNull() is an unreliable method to getting the reality, due to Access
way(s) of determining if it is really Null. So, tack on a couple of
doublequotes, and check the len, thus eliminating the nulls altogether.

e.g. IF Len([StringVal] & "") > 0 Then ...



:

When a user opens up a form to view it, I have the form and all subforms
locked using Allen Browne's method at http://www.allenbrowne.com/ser-56.html
which works great!

I ran into one problem however in my code that runs on the forms current
event. My code checks to see if a field is null or not on one of the
subforms. If there is data in the field it works fine, but when there is no
record in the subform, the first part of the code runs, as if it found data
in the MainDR field yet the form is locked and no data is there since no
record has yet been entered. Is there another way I can test to see if there
is data in the MainDR field

If Not IsNull(Forms!frmcaseupdate!subfrmRelatedDRs![MainDR]) Then
'MsgBox "dr is not empty"
Label113.Visible = True
Label114.Visible = True

Else
' MsgBox "dr is empty"
Label113.Visible = False
Label114.Visible = False

End If
Click to expand...
Click to expand...
Click to expand...
 
Hello,

I am using access 2007, my references are:

VB for Applications
MS Access 12 Object Library
OLE Automation
MS Office 12 access database engine object
 
I was able to come up with a solution. Since refering to the locked form was
not working, I tried another route to test if a record exists or not. This
seems to be working..

If DCount("MainDR", "tblAssociatedDRs", "MainDR= '" & Me!DRNO & "'") > 0 Then
 
Back
Top