subform textbox value causing invalid use of null error message

  • Thread starter Thread starter Peter S.
  • Start date Start date
P

Peter S.

I am trying to retrieve a the contents of the value field for a textbox and
it's been driving me crazy! I have tried every combination imaginable but
can't seem to get the magic incantation of "." and or "!". I tried
me.controlname.value and that gave me an error. I saw alot of newgroups
responses on form!xxxxx but can't seem to get that to work. Any help in
pointing me in the right direction would be greatly appreciated!
 
I am a bit confused by you post in that the subject refers to invalid use of
null error message and then you go on to the correct use of . and !. So which
one is the real problem? Perhaps if you fix the null value error you will
find you are correct with the other code.

The "invalid use of null" error is usually because you are trying to
reference a null value where Access does not like it. Try using the Nz
function on the values to convert nulls to either a zero length string or
numeric zero value.
Example:-
Nz(Me.MyTextBox,"") this would convert a null value in MyTextBox to a zero
length string instead of a null. If the value of MyTextBox is not a null but
an actual string value then it does not change it.

Nz(Me.MyTextBox,0) This converts a null value in MyTextBox to zero but no
change if it is an actual value.

Lookup Nz function in Help for more info on the function.
 
The textbox should have a value (actually a date) and I always get invalid
use of null for the textbox value field no matter what I do. I tried putting
in the check after I do a requery in the subforms "form_current" event to no
avail. When the form displays it has the date value in the textbox but the
problem is I can't seem to get at the textbox value in the subform during the
time the subform initializes. I tried me.mycontrol.value and that gives me
the error invalid use of null error. BTW the subform is accessing a different
table than the parent form if that makes a difference????
 
Could you post the code you are having the problem with. Include the
procedure name so that I know what event is trigering the code.
 
Here is a snippedt of the code below, I am trying to see if a date appears in
the textbox (populated from the database) and if not then I change the color
of the label to red. I tried it a couple of ways for debugging purposes.
Storing the value in txtDate first, and also in an if statement. The funny
thing is that the field does eventually get data and I see it when the UI
shows up. It's almost like the textbox is out of scope???? Of is it how I am
calling the textbox? BTW, I am able to set adjoining labels to different
colors so there are some controls that I can manipulate. I also was able to
pull the name field of the textbox by using me.caqhattestexpdatectrl.name....

Option Compare Database
Dim lngRed As Long, lngYellow As Long, lngWhite As Long

Public Sub setFlagColorOnDocumentNames()

red = RGB(255, 0, 0)
Yellow = RGB(255, 255, 0)
White = RGB(255, 255, 255)
Dim dbDate As String
Dim txtDate As String
txtDate = Me.caqhAttestExpDateCtrl.Value <- Offending line
dbDate = Me![caqhAttestExpDate]
If Form_frmCAQH.caqhAttestExpDateCtrl.Value = Null Then <- also a problem
Form_frmCAQH.caqhAttestExpDate_Label.ForeColor = red
' DEBUG Form_frmCAQH.caqhAttestExpDate_Label.ForeColor = red

End Sub
Private Sub Form_Current()

Me.setFlagColorOnDocumentNames

End Sub

Private Sub Form_Open(Cancel As Integer)

Form_frmProviders.CAQH_subform.Form.RecordSource = "SELECT * FROM tblCAQH
where PROVID = " + Form_frmTracking!cboProvider
Form_frmProviders.CAQH_subform.Form.Requery

End Sub
 
I think I finally figured out what was going on. When I am hitting the
textbox the UI hasn't fully completed loading. It appears that all the events
for subform can be hit 5+ times, something I did not know. So putting in the
NZ check, get's you past these cycles till eventually it gets populated (last
2 cycles at the end).

Peter S. said:
Here is a snippedt of the code below, I am trying to see if a date appears in
the textbox (populated from the database) and if not then I change the color
of the label to red. I tried it a couple of ways for debugging purposes.
Storing the value in txtDate first, and also in an if statement. The funny
thing is that the field does eventually get data and I see it when the UI
shows up. It's almost like the textbox is out of scope???? Of is it how I am
calling the textbox? BTW, I am able to set adjoining labels to different
colors so there are some controls that I can manipulate. I also was able to
pull the name field of the textbox by using me.caqhattestexpdatectrl.name....

Option Compare Database
Dim lngRed As Long, lngYellow As Long, lngWhite As Long

Public Sub setFlagColorOnDocumentNames()

red = RGB(255, 0, 0)
Yellow = RGB(255, 255, 0)
White = RGB(255, 255, 255)
Dim dbDate As String
Dim txtDate As String
txtDate = Me.caqhAttestExpDateCtrl.Value <- Offending line
dbDate = Me![caqhAttestExpDate]
If Form_frmCAQH.caqhAttestExpDateCtrl.Value = Null Then <- also a problem
Form_frmCAQH.caqhAttestExpDate_Label.ForeColor = red
' DEBUG Form_frmCAQH.caqhAttestExpDate_Label.ForeColor = red

End Sub
Private Sub Form_Current()

Me.setFlagColorOnDocumentNames

End Sub

Private Sub Form_Open(Cancel As Integer)

Form_frmProviders.CAQH_subform.Form.RecordSource = "SELECT * FROM tblCAQH
where PROVID = " + Form_frmTracking!cboProvider
Form_frmProviders.CAQH_subform.Form.Requery

End Sub

OssieMac said:
Could you post the code you are having the problem with. Include the
procedure name so that I know what event is trigering the code.
Click to expand...
Click to expand...
 
When forms are not updating with the code when you expect them to, try
Me.Recalc. (Usually just before end sub). Not sure if it will fix your
problem but I know if using combo boxes that have queries that rely on
previous entries in a form, you need to insert it in an On Enter event to
force Access to recalculate the required values for the dropdown.

--
Regards,

OssieMac


Peter S. said:
I think I finally figured out what was going on. When I am hitting the
textbox the UI hasn't fully completed loading. It appears that all the events
for subform can be hit 5+ times, something I did not know. So putting in the
NZ check, get's you past these cycles till eventually it gets populated (last
2 cycles at the end).

Peter S. said:
Here is a snippedt of the code below, I am trying to see if a date appears in
the textbox (populated from the database) and if not then I change the color
of the label to red. I tried it a couple of ways for debugging purposes.
Storing the value in txtDate first, and also in an if statement. The funny
thing is that the field does eventually get data and I see it when the UI
shows up. It's almost like the textbox is out of scope???? Of is it how I am
calling the textbox? BTW, I am able to set adjoining labels to different
colors so there are some controls that I can manipulate. I also was able to
pull the name field of the textbox by using me.caqhattestexpdatectrl.name....

Option Compare Database
Dim lngRed As Long, lngYellow As Long, lngWhite As Long

Public Sub setFlagColorOnDocumentNames()

red = RGB(255, 0, 0)
Yellow = RGB(255, 255, 0)
White = RGB(255, 255, 255)
Dim dbDate As String
Dim txtDate As String
txtDate = Me.caqhAttestExpDateCtrl.Value <- Offending line
dbDate = Me![caqhAttestExpDate]
If Form_frmCAQH.caqhAttestExpDateCtrl.Value = Null Then <- also a problem
Form_frmCAQH.caqhAttestExpDate_Label.ForeColor = red
' DEBUG Form_frmCAQH.caqhAttestExpDate_Label.ForeColor = red

End Sub
Private Sub Form_Current()

Me.setFlagColorOnDocumentNames

End Sub

Private Sub Form_Open(Cancel As Integer)

Form_frmProviders.CAQH_subform.Form.RecordSource = "SELECT * FROM tblCAQH
where PROVID = " + Form_frmTracking!cboProvider
Form_frmProviders.CAQH_subform.Form.Requery

End Sub

OssieMac said:
Could you post the code you are having the problem with. Include the
procedure name so that I know what event is trigering the code.

--
Regards,

OssieMac


:

The textbox should have a value (actually a date) and I always get invalid
use of null for the textbox value field no matter what I do. I tried putting
in the check after I do a requery in the subforms "form_current" event to no
avail. When the form displays it has the date value in the textbox but the
problem is I can't seem to get at the textbox value in the subform during the
time the subform initializes. I tried me.mycontrol.value and that gives me
the error invalid use of null error. BTW the subform is accessing a different
table than the parent form if that makes a difference????

:

I am a bit confused by you post in that the subject refers to invalid use of
null error message and then you go on to the correct use of . and !. So which
one is the real problem? Perhaps if you fix the null value error you will
find you are correct with the other code.

The "invalid use of null" error is usually because you are trying to
reference a null value where Access does not like it. Try using the Nz
function on the values to convert nulls to either a zero length string or
numeric zero value.
Example:-
Nz(Me.MyTextBox,"") this would convert a null value in MyTextBox to a zero
length string instead of a null. If the value of MyTextBox is not a null but
an actual string value then it does not change it.

Nz(Me.MyTextBox,0) This converts a null value in MyTextBox to zero but no
change if it is an actual value.

Lookup Nz function in Help for more info on the function.

--
Regards,

OssieMac


:

I am trying to retrieve a the contents of the value field for a textbox and
it's been driving me crazy! I have tried every combination imaginable but
can't seem to get the magic incantation of "." and or "!". I tried
me.controlname.value and that gave me an error. I saw alot of newgroups
responses on form!xxxxx but can't seem to get that to work. Any help in
pointing me in the right direction would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top