Invalid use of Null

[chasday]

In my project I have a On Click action that opens another form. The code is:

Private Sub JobName_Click()
On Error GoTo HandleError

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]
DoCmd.Close 'Close this form
DoCmd.OpenForm stDocName, , , stLinkCriteria

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub

Pretty straight forward and it works great. I think something is corupting
the application though, because out of nowhere it will stop working and
return a "Invaild use of Null" error. I have used a msgbox to verify that the
proper criteria string is passed out of the procedure, but when the
frmProposal is opened the [Proposal ID] criteria is Null. The only way I've
found to fix it is to revert to a backup (backup early and often). I'm not
doing anything major when it breaks. Often I'm not even working on the same
form.

Ideas?

 

[Rick Brandt]

In my project I have a On Click action that opens another form. The
code is:

Private Sub JobName_Click()
On Error GoTo HandleError

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]
DoCmd.Close 'Close this form
DoCmd.OpenForm stDocName, , , stLinkCriteria

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub

Pretty straight forward and it works great. I think something is
corupting the application though, because out of nowhere it will stop
working and return a "Invaild use of Null" error. I have used a
msgbox to verify that the proper criteria string is passed out of the
procedure, but when the frmProposal is opened the [Proposal ID]
criteria is Null. The only way I've found to fix it is to revert to a
backup (backup early and often). I'm not doing anything major when it
breaks. Often I'm not even working on the same form.

Ideas?

Do the Open line before the Close line. If the close is fast enough then the
reference to "Me" will no longer be there. In the Close line you will then need
to be more specific about what to close...

DoCmd.Close acForm, Me.Name

 

[boblarson]

Try closing the form AFTER opening the new form and change the close code to
what I've shown here (leave it exactly as is):

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo 'Close this form


--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Bob Quintal]

In my project I have a On Click action that opens another form.
The code is:

Private Sub JobName_Click()
On Error GoTo HandleError

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]
DoCmd.Close 'Close this form
DoCmd.OpenForm stDocName, , , stLinkCriteria

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub

Pretty straight forward and it works great. I think something is
corupting the application though, because out of nowhere it will
stop working and return a "Invaild use of Null" error. I have
used a msgbox to verify that the proper criteria string is passed
out of the procedure, but when the frmProposal is opened the
[Proposal ID] criteria is Null. The only way I've found to fix it
is to revert to a backup (backup early and often). I'm not doing
anything major when it breaks. Often I'm not even working on the
same form.

Ideas?

Do the Open line before the Close line. If the close is fast
enough then the reference to "Me" will no longer be there. In the
Close line you will then need to be more specific about what to
close...

DoCmd.Close acForm, Me.Name

Nonsensae, the reference to Me![Proposal ID] is made prior to
issuing the close statement and the variable will stay in scope
until the completion of the event. Access has always correctly
executed code in the event which closes the form, even the code
which follows the close statement.

Is it possible that frmProposal is being opened on a a new, unsaved,
record in the calling form?

 

[chasday]

Thanks Bob and Rick for the quick response. Unfortunately, although your
solution makes perfect sense, I'm still getting the same error.
Other ideas?

Try closing the form AFTER opening the new form and change the close code to
what I've shown here (leave it exactly as is):

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo 'Close this form


--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

In my project I have a On Click action that opens another form. The code is:

Private Sub JobName_Click()
On Error GoTo HandleError

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]
DoCmd.Close 'Close this form
DoCmd.OpenForm stDocName, , , stLinkCriteria

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub

Pretty straight forward and it works great. I think something is corupting
the application though, because out of nowhere it will stop working and
return a "Invaild use of Null" error. I have used a msgbox to verify that the
proper criteria string is passed out of the procedure, but when the
frmProposal is opened the [Proposal ID] criteria is Null. The only way I've
found to fix it is to revert to a backup (backup early and often). I'm not
doing anything major when it breaks. Often I'm not even working on the same
form.

Ideas?

 

[chasday]

In my project I have a On Click action that opens another form.
The code is:

Private Sub JobName_Click()
On Error GoTo HandleError

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]
DoCmd.Close 'Close this form
DoCmd.OpenForm stDocName, , , stLinkCriteria

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub

Pretty straight forward and it works great. I think something is
corupting the application though, because out of nowhere it will
stop working and return a "Invaild use of Null" error. I have
used a msgbox to verify that the proper criteria string is passed
out of the procedure, but when the frmProposal is opened the
[Proposal ID] criteria is Null. The only way I've found to fix it
is to revert to a backup (backup early and often). I'm not doing
anything major when it breaks. Often I'm not even working on the
same form.

Ideas?

Do the Open line before the Close line. If the close is fast
enough then the reference to "Me" will no longer be there. In the
Close line you will then need to be more specific about what to
close...

DoCmd.Close acForm, Me.Name

Nonsensae, the reference to Me![Proposal ID] is made prior to
issuing the close statement and the variable will stay in scope
until the completion of the event. Access has always correctly
executed code in the event which closes the form, even the code
which follows the close statement.

Is it possible that frmProposal is being opened on a a new, unsaved,
record in the calling form?

No the record was there when the form opened

 

[Jeanette Cunningham]

Hi,
this is how I sometimes try to track down errors.
I would change the code that opens the next form for a test, by putting an
apostrophe in front of the line that says Docmd etc
I would check that the form closes without giving that null error.

If no problem with this, I would look at opening the called form from the
database window.
I would temporarily fix the query for the form's recordsource to open at a
particular record and open the form to see if that gives the null error.

You could keep on devising tests. You could also use the debugger to step
through the code to see on which line of code the error happens.

Jeanette Cunningham

Thanks Bob and Rick for the quick response. Unfortunately, although your
solution makes perfect sense, I'm still getting the same error.
Other ideas?

Try closing the form AFTER opening the new form and change the close code
to
what I've shown here (leave it exactly as is):

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo 'Close this form


--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

In my project I have a On Click action that opens another form. The
code is:

Private Sub JobName_Click()
On Error GoTo HandleError

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]
DoCmd.Close 'Close this form
DoCmd.OpenForm stDocName, , , stLinkCriteria

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub

Pretty straight forward and it works great. I think something is
corupting
the application though, because out of nowhere it will stop working and
return a "Invaild use of Null" error. I have used a msgbox to verify
that the
proper criteria string is passed out of the procedure, but when the
frmProposal is opened the [Proposal ID] criteria is Null. The only way
I've
found to fix it is to revert to a backup (backup early and often). I'm
not
doing anything major when it breaks. Often I'm not even working on the
same
form.

Ideas?

 

[chasday]

I should add: the "invalid use of Null" occurs when on the Form_Load event of
the called form hits the following code

Private Sub Form_Load()
Dim varSQL As Variant
Dim intProposalID As Integer

'On Error GoTo HandleError
intProposalID = Me.[Proposal ID].Value '< ERROR OCCURS HERE

If I comment the Form_Load code the form opens as a new record

Hi,
this is how I sometimes try to track down errors.
I would change the code that opens the next form for a test, by putting an
apostrophe in front of the line that says Docmd etc
I would check that the form closes without giving that null error.

If no problem with this, I would look at opening the called form from the
database window.
I would temporarily fix the query for the form's recordsource to open at a
particular record and open the form to see if that gives the null error.

You could keep on devising tests. You could also use the debugger to step
through the code to see on which line of code the error happens.

Jeanette Cunningham

Thanks Bob and Rick for the quick response. Unfortunately, although your
solution makes perfect sense, I'm still getting the same error.
Other ideas?

Try closing the form AFTER opening the new form and change the close code
to
what I've shown here (leave it exactly as is):

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo 'Close this form


--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________


:

In my project I have a On Click action that opens another form. The
code is:

Private Sub JobName_Click()
On Error GoTo HandleError

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]
DoCmd.Close 'Close this form
DoCmd.OpenForm stDocName, , , stLinkCriteria

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub

Pretty straight forward and it works great. I think something is
corupting
the application though, because out of nowhere it will stop working and
return a "Invaild use of Null" error. I have used a msgbox to verify
that the
proper criteria string is passed out of the procedure, but when the
frmProposal is opened the [Proposal ID] criteria is Null. The only way
I've
found to fix it is to revert to a backup (backup early and often). I'm
not
doing anything major when it breaks. Often I'm not even working on the
same
form.

Ideas?

 

[Jeanette Cunningham]

Put Debug.Print "proposal id: " & Me![Proposal ID]
just above the line
intProposalID = Me.[Proposal ID].Value '< ERROR OCCURS HERE

close the form and save changes.
Now open the form using the button as per usual
clear up after the error,
do Ctl + G to open the immediate window
Access will show you the value for Proposal ID
as the error happens here, you need to be a bit of a detective to figure out
what is wrong with the value for Proposal ID.
It might be null, or there might be some other reason why it isn't what
access needs to be able to open your form correctly

Jeanette Cunningham

I should add: the "invalid use of Null" occurs when on the Form_Load event
of
the called form hits the following code

Private Sub Form_Load()
Dim varSQL As Variant
Dim intProposalID As Integer

'On Error GoTo HandleError
intProposalID = Me.[Proposal ID].Value '< ERROR OCCURS HERE

If I comment the Form_Load code the form opens as a new record

Hi,
this is how I sometimes try to track down errors.
I would change the code that opens the next form for a test, by putting
an
apostrophe in front of the line that says Docmd etc
I would check that the form closes without giving that null error.

If no problem with this, I would look at opening the called form from the
database window.
I would temporarily fix the query for the form's recordsource to open at
a
particular record and open the form to see if that gives the null error.

You could keep on devising tests. You could also use the debugger to step
through the code to see on which line of code the error happens.

Jeanette Cunningham

Thanks Bob and Rick for the quick response. Unfortunately, although
your
solution makes perfect sense, I'm still getting the same error.
Other ideas?

:

Try closing the form AFTER opening the new form and change the close
code
to
what I've shown here (leave it exactly as is):

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo 'Close this form


--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________


:

In my project I have a On Click action that opens another form. The
code is:

Private Sub JobName_Click()
On Error GoTo HandleError

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"

stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]
DoCmd.Close 'Close this form
DoCmd.OpenForm stDocName, , , stLinkCriteria

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub

Pretty straight forward and it works great. I think something is
corupting
the application though, because out of nowhere it will stop working
and
return a "Invaild use of Null" error. I have used a msgbox to verify
that the
proper criteria string is passed out of the procedure, but when the
frmProposal is opened the [Proposal ID] criteria is Null. The only
way
I've
found to fix it is to revert to a backup (backup early and often).
I'm
not
doing anything major when it breaks. Often I'm not even working on
the
same
form.

Ideas?

 

[Dirk Goldgar]

I should add: the "invalid use of Null" occurs when on the Form_Load event
of
the called form hits the following code

Private Sub Form_Load()
Dim varSQL As Variant
Dim intProposalID As Integer

'On Error GoTo HandleError
intProposalID = Me.[Proposal ID].Value '< ERROR OCCURS HERE

If I comment the Form_Load code the form opens as a new record

That indicates pretty clearly that there is no matching record on this form.
So the form opens to a blank record, and therefore Me.[Proposal ID] is Null.
You should test that before attempting to assign Me.[Proposal ID] to an
Integer variable, because in Integer variable can't hold a Null value. You
would write something like:

With Me.[Proposal ID]
If Not IsNull(.Value) Then
intProposalID = .Value
End If
End With

However, it's probably better to wait until the form's Current event before
using the values of the current record's fields. It may not do any harm in
the Load event (I think it forces the Current event to fire early), but it
makes a bit more sense in the Current event.

 

[Jeanette Cunningham]

Thanks Dirk,
over time there are different views expressed over time about where to place
various pieces of code that might go in the open, load or current event.
Would you be willing to share any rules you personally use to decide which
of the 3 events to use.

Jeanette Cunningham

I should add: the "invalid use of Null" occurs when on the Form_Load event
of
the called form hits the following code

Private Sub Form_Load()
Dim varSQL As Variant
Dim intProposalID As Integer

'On Error GoTo HandleError
intProposalID = Me.[Proposal ID].Value '< ERROR OCCURS HERE

If I comment the Form_Load code the form opens as a new record

That indicates pretty clearly that there is no matching record on this
form. So the form opens to a blank record, and therefore Me.[Proposal ID]
is Null. You should test that before attempting to assign Me.[Proposal ID]
to an Integer variable, because in Integer variable can't hold a Null
value. You would write something like:

With Me.[Proposal ID]
If Not IsNull(.Value) Then
intProposalID = .Value
End If
End With

However, it's probably better to wait until the form's Current event
before using the values of the current record's fields. It may not do any
harm in the Load event (I think it forces the Current event to fire
early), but it makes a bit more sense in the Current event.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Rick Brandt]

Do the Open line before the Close line. If the close is fast
enough then the reference to "Me" will no longer be there. In the
Close line you will then need to be more specific about what to
close...

DoCmd.Close acForm, Me.Name

Nonsensae, the reference to Me![Proposal ID] is made prior to
issuing the close statement and the variable will stay in scope
until the completion of the event. Access has always correctly
executed code in the event which closes the form, even the code
which follows the close statement.

Yeah, I mis-read that one. I was thinking of the Me![Proposal ID] reference
being directly in the OpenForm argument. Since it was assigned to a variable
prior to the close then you're correct that the order of the last two lines
should not matter. I just go it in my head that it was a timing thing since the
OP said it was not consistent.

 

[chasday]

Starting again from scratch to clear things up.
I have a form with a subform that contains a list of proposals returned from
tblProposal.
The code that runs of clicking the proposal Job Name is:
Private Sub JobName_Click()
‘Error handling removed for brevity
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"
stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]

MsgBox stLinkCriteria 'Returns "[Proposal ID]=75"

DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo 'Close this form
End Sub

This code opens up frmProposal with a more detailed look at the same proposal
The problem is that when frmProposal opens the ProposalID value is Null. It
gets lost in between. It opens as a new record if I remove the form load
code. Otherwise, it Errors when the load code asks for ProposalID.
Notes:
1) As far as I can tell via the MsgBox the Criteria is being passed out of
the calling form is correct
2) By default, there is a record for the ProposalID since they both are
pulled from the same table.
3) This was working fine until changed by something, I’ll be working on
basic coding operations, and it will just stop working. I’ve tried Compact &
Repair, rebooting, adding and removing references, Compile, beating my head
against solid objects...
4) If I open the form directly from the Navigation view it opens fine and
pulls up a record.
5) I am using Access 2007
6) Using the code Debug.Print "proposal id: " & Me![Proposal ID] in the
frmProposal Load Code returns a Null ProposalID Value

 

[Dirk Goldgar]

Please see my comments and questions inline.

Starting again from scratch to clear things up.
I have a form with a subform that contains a list of proposals returned
from
tblProposal.
The code that runs of clicking the proposal Job Name is:
Private Sub JobName_Click()
‘Error handling removed for brevity
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProposal"
stLinkCriteria = "[Proposal ID]=" & Me![Proposal ID]

MsgBox stLinkCriteria 'Returns "[Proposal ID]=75"

DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo 'Close this form
End Sub

This code opens up frmProposal with a more detailed look at the same
proposal
The problem is that when frmProposal opens the ProposalID value is Null.
It
gets lost in between. It opens as a new record if I remove the form load
code. Otherwise, it Errors when the load code asks for ProposalID.

This suggests very strongly that there is no record in the recordsource of
frmProposal that has [Proposal ID] = 75. As I understand it, the form is
supposed to be based on tblProposal, so that should not be possible.
Therefore, you should check three things first:

1. Check the RecordSource property of frmProposal and make sure that it is
still tblProposal, and not a query with a WHERE clause that excludes some
records.

2. Check the Filter and FilterOnLoad properties of frmProposal to make sure
the form isn't opening with a filter applied that would exclude the record
for proposal 75.

3. Check the Open event of frmProposal and make sure you don't have code in
that event that resets the form's recordsource or applies a filter.

Having checked those things, if none of them is the source of the problem,
check the design of tblProposal and make sure the name of the field is
"Proposal ID", not "ProposalID" or anything else. It could be that you
changed the name of the field somewhere along the way.

4) If I open the form directly from the Navigation view it opens fine and
pulls up a record.

When you do that, can you find the record for proposal 75?

 

[Dirk Goldgar]

Thanks Dirk,
over time there are different views expressed over time about where to
place various pieces of code that might go in the open, load or current
event.
Would you be willing to share any rules you personally use to decide which
of the 3 events to use.

I don't do anything special; I just try to place my code according to the
logic of the events:

+ If I want to modify the form's recordsource before displaying it, or
show/hide controls based on an argument passed via OpenArgs, I'll use the
Open event, since that fires before the recordsource is queried.

+ If I want to do something based on whether or not the form has any records
to display -- but not needing any information about the number or contents
of those records -- I'll use the Load event, since that fires when the
recordsource has been queried and at least one record (if there are going to
be any) has been returned. For example, I may use the Load event to close
the form if no records have been returned.

+ If I want to work with the field values of a particular record, I'll use
the Current event, since that fires when the record has been loaded and made
current on the form.

Certainly there can be cases where what I want to do may not neatly fit into
those categories. For example, if I want to know something specific about
the very first record loaded, and there may be more than one, I might do
this processing in the form's Load event, rather than its Current event.
But that's not a need that arises very often.

 

[chasday]

This suggests very strongly that there is no record in the recordsource of
frmProposal that has [Proposal ID] = 75. As I understand it, the form is
supposed to be based on tblProposal, so that should not be possible.
Therefore, you should check three things first:

1. Check the RecordSource property of frmProposal and make sure that it is
still tblProposal, and not a query with a WHERE clause that excludes some
records.

Thanks Dick! That was it.
The frmProposal RecordSource had changed to ....WHERE [Proposal ID] = 73

I changed it back to tblProposals and it works great.
Any idea why the Record Source would change without my doing it in the code?

 

[Dirk Goldgar]

The frmProposal RecordSource had changed to ....WHERE [Proposal ID] = 73

I changed it back to tblProposals and it works great.
Any idea why the Record Source would change without my doing it in the
code?

I can only think of two way that could happen:

1. You did it manually (presumably without realizing it).

2. At some point, you had code that changed the form's RecordSource
property. At some point after that code had run and changed the property,
you saved the form.

You're using Access 2007, which I've only just started using, so it could be
there's some quirk that I'm not aware of. This new version is not without
its bugs and unexpected behaviors. Still, my money would be on one of the
two items above.

 

[Jeanette Cunningham]

Thanks Dirk

Jeanette Cunningham

I don't do anything special; I just try to place my code according to the
logic of the events:

+ If I want to modify the form's recordsource before displaying it, or
show/hide controls based on an argument passed via OpenArgs, I'll use the
Open event, since that fires before the recordsource is queried.

+ If I want to do something based on whether or not the form has any
records to display -- but not needing any information about the number or
contents of those records -- I'll use the Load event, since that fires
when the recordsource has been queried and at least one record (if there
are going to be any) has been returned. For example, I may use the Load
event to close the form if no records have been returned.

+ If I want to work with the field values of a particular record, I'll use
the Current event, since that fires when the record has been loaded and
made current on the form.

Certainly there can be cases where what I want to do may not neatly fit
into those categories. For example, if I want to know something specific
about the very first record loaded, and there may be more than one, I
might do this processing in the form's Load event, rather than its Current
event. But that's not a need that arises very often.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)