Invalid use of Null 12-21-07

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?
0
Utf
12/21/2007 1:01:01 AM
access.formscoding 7493 articles. 0 followers. Follow

17 Replies
1047 Views

Similar Articles

[PageSpeed] 53

chasday wrote:
> 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

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com



0
Rick
12/21/2007 1:08:32 AM
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.
__________________________________


"chasday" wrote:

> 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?
0
Utf
12/21/2007 1:12:00 AM
"Rick Brandt" <rickbrandt2@hotmail.com> wrote in
news:kmEaj.56344$eY.33806@newssvr13.news.prodigy.net: 

> chasday wrote:
>> 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?

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
12/21/2007 1:13:31 AM
Thanks Bob and Rick for the quick response. Unfortunately, although your 
solution makes perfect sense, I'm still getting the same error. 
Other ideas?

"boblarson" wrote:

> 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.
> __________________________________
> 
> 
> "chasday" wrote:
> 
> > 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?
0
Utf
12/21/2007 2:35:00 AM

"Bob Quintal" wrote:

> "Rick Brandt" <rickbrandt2@hotmail.com> wrote in
> news:kmEaj.56344$eY.33806@newssvr13.news.prodigy.net: 
> 
> > chasday wrote:
> >> 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?
> 
> -- 
> Bob Quintal
> 
> PA is y I've altered my email address.

No the record was there when the form opened
0
Utf
12/21/2007 2:47:02 AM
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

"chasday" <chasday@discussions.microsoft.com> wrote in message 
news:B92B22A7-C6A0-4CC1-8F2B-E0266FF63B8D@microsoft.com...
> Thanks Bob and Rick for the quick response. Unfortunately, although your
> solution makes perfect sense, I'm still getting the same error.
> Other ideas?
>
> "boblarson" wrote:
>
>> 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.
>> __________________________________
>>
>>
>> "chasday" wrote:
>>
>> > 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? 


0
Jeanette
12/21/2007 2:57:11 AM
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



"Jeanette Cunningham" wrote:

> 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
> 
> "chasday" <chasday@discussions.microsoft.com> wrote in message 
> news:B92B22A7-C6A0-4CC1-8F2B-E0266FF63B8D@microsoft.com...
> > Thanks Bob and Rick for the quick response. Unfortunately, although your
> > solution makes perfect sense, I'm still getting the same error.
> > Other ideas?
> >
> > "boblarson" wrote:
> >
> >> 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.
> >> __________________________________
> >>
> >>
> >> "chasday" wrote:
> >>
> >> > 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? 
> 
> 
> 
0
Utf
12/21/2007 3:14:03 AM
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



"chasday" <chasday@discussions.microsoft.com> wrote in message 
news:53A584B3-3393-4411-9F8A-E1A4F560295F@microsoft.com...
>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
>
>
>
> "Jeanette Cunningham" wrote:
>
>> 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
>>
>> "chasday" <chasday@discussions.microsoft.com> wrote in message
>> news:B92B22A7-C6A0-4CC1-8F2B-E0266FF63B8D@microsoft.com...
>> > Thanks Bob and Rick for the quick response. Unfortunately, although 
>> > your
>> > solution makes perfect sense, I'm still getting the same error.
>> > Other ideas?
>> >
>> > "boblarson" wrote:
>> >
>> >> 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.
>> >> __________________________________
>> >>
>> >>
>> >> "chasday" wrote:
>> >>
>> >> > 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?
>>
>>
>> 


0
Jeanette
12/21/2007 3:51:12 AM
"chasday" <chasday@discussions.microsoft.com> wrote in message 
news:53A584B3-3393-4411-9F8A-E1A4F560295F@microsoft.com...
>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)

0
Dirk
12/21/2007 4:09:44 AM
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

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
news:79921D14-0739-4394-873D-D3F7192100B6@microsoft.com...
> "chasday" <chasday@discussions.microsoft.com> wrote in message 
> news:53A584B3-3393-4411-9F8A-E1A4F560295F@microsoft.com...
>>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)
> 


0
Jeanette
12/21/2007 4:30:18 AM
Bob Quintal wrote:
> "Rick Brandt" <rickbrandt2@hotmail.com> wrote in
> news:kmEaj.56344$eY.33806@newssvr13.news.prodigy.net:
> > 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.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
12/21/2007 12:35:01 PM
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




0
Utf
12/21/2007 2:09:01 PM
Please see my comments and questions inline.

"chasday" <chasday@discussions.microsoft.com> wrote in message 
news:E8A8C8D6-2A1F-4D89-87AB-15BBA7E83461@microsoft.com...
> 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, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
12/21/2007 2:44:07 PM
"Jeanette Cunningham" <nnn@discussions.microsoft.com> wrote in message 
news:%23pz$xo4QIHA.5980@TK2MSFTNGP04.phx.gbl...
> 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.

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

(please reply to the newsgroup)

0
Dirk
12/21/2007 3:03:14 PM

"Dirk Goldgar" wrote:

> 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?
0
Utf
12/21/2007 3:33:00 PM
"chasday" <chasday@discussions.microsoft.com> wrote in message 
news:CF605907-B84A-484E-A00B-2DDCB17286AB@microsoft.com...

> 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.

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

(please reply to the newsgroup)

0
Dirk
12/21/2007 3:46:17 PM
Thanks Dirk

Jeanette Cunningham


"Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
news:67159036-D94F-4517-8038-257398C9E3D8@microsoft.com...
> "Jeanette Cunningham" <nnn@discussions.microsoft.com> wrote in message 
> news:%23pz$xo4QIHA.5980@TK2MSFTNGP04.phx.gbl...
>> 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.
>
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
> 


0
Jeanette
12/21/2007 9:02:38 PM
Reply:

Similar Artilces:

Invalid syntax
I get the error invalid syntax for the following line : ( summing up figures in case GetBonus = Yes) Bonus : iif(getBonus = -1;nz(BonusL1;0)+ NZ([BonusL2];0)+ NZ([BonusL3];0) could you help ? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200712/1 Ditch the semicolons. iif( Formula to test,results if true, results if false.) I would guess you want iif(getBonus = -1,nz(BonusL1;0)+ NZ([BonusL2];0)+ NZ([BonusL3],0) peljo via AccessMonster.com wrote: > I get the error invalid syntax for the following line : ( summing up figures...

how to copy and paste with the gridlines using excel
I am new to excel. I am doing a catalog using excel. One row has item #, another size and etc. I can get the information to paste into my web page, but all I get is the information. There are no lines "gridlines". I was told to use a border and this did not help. Please let me know if there is any way to copy and paste with the lines I used in the excel to show the same on my web page. I am deperate here for help. Thank you in advance. Select the range you want to copy. Hold the Shift key down and use menu item Edit --> Copy Picture... Ed FErrero >I am new to...

The 'CRM_FullName' parameter is missing a value 03-08-07
Hello, I installed Microsoft CRM with SQL 2005 Server and reporting services on different server. but when I'm trying to run the reports from Reports Area in Microsoft CRM I am receiving the following error message? "The 'CRM_FullName' parameter is missing a value" Has anyone resolve this problem and could you help me? Thanks in advance, Rena Hi Rena, I was having the exact same problem. This is how I got it solved: It turend out that this error ("The 'CRM_FullName' parameter is missing a value") was only appearant when trying to view the...

Using VC++ 6.0 under Vista
Hi All, I was wondering if anyone has any experience to share, successful or unsuccessful, using VC++ 6.0 under Vista. I know it isn't supported but we may have to do it anyway. Googling has yielded some references to issues installing it, but not much about using it to compile and/ or to debug. Wondering how feasible it is and if there are any gotchas. Thanks! Sherri ranin02 wrote: > Hi All, > > I was wondering if anyone has any experience to share, successful or > unsuccessful, using VC++ 6.0 under Vista. I know it isn't supported > but we may have to do it a...

regression using excel
what are the steps to run regression using excel? Thanks Two ways... 1) Go to <Tools> <Data Anlaysis> and then select <Regression>. Then follow the steps to input your a nd y values and then the read out will give you a pearson r value and a means to construct the equation of the regression line aswell as some other interesting stats. Ordinarily the <Data Analysis> toolpak is not added into Excel. You need to add this in. 2) Plot your data (best in scatter graph) and then right- click and select <Add trendline...>. Here select <Linear> and on the ...

command not found when using excel VBA function format
I have an extensive piece of VBA code that use the format function. The macros run on my development machine and on one other in building. I tried to use the code on an additional computer and got a command not found error. I am assuming that means that there is some file missing on that computer. Can anyone suggest what the specific fiel might be? Thank you On 7 dec, 19:40, OHCQ_DB <OHCQ...@discussions.microsoft.com> wrote: > I have an extensive piece of VBA code that use the format function. =A0Th= e > macros run on my development machine and on one other in b...

Invalid File Format
When I start Excel I get an initial message that says ssipro97.dot is an invalid format. Any idea why this would come up? It doesn't seem to affect how Excel works, it's just annoyint. ...

using office student for commercial use.
I have Windows 7 Home Premium installed in my laptop and I have MS office 2007 Home and student edition installed. I need to use powerpoint at my office. can I use these software at work ? legally is there any problem ? can I use powerpoint works at work also ? can I also sell my work that I made with powerpoint home and student ? shortly commercially is there any problem ? Sentiere wrote: > I have Windows 7 Home Premium installed in my laptop and I have MS office > 2007 Home and student edition installed. I need to use powerpoint at my > office. can I use these soft...

How to use Windows Media Player control to play avi file
Hello, I need to play an avi file in my program using the media player activex control.I need to be able to indicate a starting frame and an end frame that the control will stop playing after reaching it. Thanks in advance ...

invalid handle
hi frds i m very new in vc++ and i m founding too much difficulty in this so plz can any one solve this problem .... actully i m inserting a image in list box using this code ........ everything is returning write thing still it is saying invalid handle after ImageList_Add(hList,m_hBmpNew,0); when i m going to dibug it plz help me BOOL Fun() { // Create 256 color image lists HIMAGELIST hList = ImageList_Create(32,32, ILC_COLOR8 , 8, 1); HBITMAP m_hBmpNew = (HBITMAP) LoadImage( AfxGetInstanceHandle(), // handle to instance "c:\\img.bmp", /...

Pictures in received mail using outlook 2000
When I used outlook express and someone sent me an e-mail with pictures, they would show in the body of the message....in outlook express they just appear as icons at the end of the message.... how can I get these pictures to appear in the body of the message in outlook? Thanks for your help! ...

find all cells that match and use in an index/vlookup
I have a LARGE set of data... I parse it to a pivot. red blue Chair living room .1 .2 dining room .6 .3 bedroom .3 .4 sofa living room .9 .2 dining room .01 0 bedroom .2 .5 etc... (4 colums, multiple rows) I need to be able to: Find what room a soffa is most likely to be in, and what that chance is, and use that data in another worksheet where there will just be a list of furniture, in room, with max chance. I can seem to figure how to find the range of rows that have chairs (there are not always the same amou...

Using sumproduct while skipping cells in the array
I'm trying to use sumproduct in a situation with two arrays of numbers. It works in the place where the arrays are contiguous, looking like sumproduct($A7:$A15,C7:C15). However, in one place one of the arrays is alternating with strings. (This format was decreed from on high, and I don't want to argue with them about it...) I thought I should be able to fake it by using sumproduct($A7:$A15>0,C7:C15>0) to ignore the strings, but Excel refused to buy that. Well, actually Excel seems to buy it but just returns 0, which is obviously wrong. I tried a bunch of options trying to...

in excell office xp I used to be able to use arrow keys to move b.
Now I need to point and click to move to new cells can not find in tools/options Hi JC, Is it a case that your scroll lock key has been pressed? You should be able to tell as the light next to you num lock, or caps lock will be on. The Scroll lock key is located above the home key (which is next to the delete key). Hope that helps ___________________ Naz London "JC" wrote: > Now I need to point and click to move to new cells can not find in > tools/options ...

Use Both M04 & M05?
Is there a way to install and use Money 2005 while still maintaining Money 2004? Stated differently, can both versions be operational on the same PC at the same time? If so, this would allow a user to 'test drive' Money 2005 without the risk of removing Money 2004. You should be able to. You can't run then both simultaneously, but close 04 and start 05 instead. Probably (untested) you'll need to install 04 first then 05, and 05 will grab the handler for online imports and for .MNY files. You;ll need to have separate files for each one and make sure you load up the co...

Using Visual C++ 6.0 MFC Application
01/19/2004 Using a single document or a dialog application, I am able to use my own variables that are declared in the same source file, however, if I try to declare a global variable in a header file or a source file included before the code I am using, I get the error "Undeclared Identifier". Example in Old C: "First.h" int i; "Main.cpp" #include "First.h" main() { i=5; printf("%d",i); } When I declare a variable in a header file, in Class View - Globals, my variable does show up, b...

Second serie doesn't use X-as values
I created a chart on a worksheet and defined the X-ax (Category Xax label =Position!$G$4:$G$42) and the first serie (Values =Position!$O$4:$O$42). Everything looks OK. Now I add another serie (Values =Position!$P$4:$P$42) and although this line should be straight it doesn't. If I select the first serie in the chart both the ranges for the X-ax (G) and the values (O) are marked on the worksheet. If I select the second serie in the chart ONLY the range for the values (P) has been marked. Also , if I delete the first serie from the chart after selection it, the values displayed for the ...

Issues with 12.1.2 with tables and page breaks.
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I work for a small company that is primarily mac based. I noticed in this latest patch that our documents are having major issues after this patch. For one the text in tables is missing totally or partially. It is there though. The solution I found is to select the table and do a word wrap in the table properties but we have thousands of documents like this. Also I have been getting reports and also noticed that it is inserting extra page breaks in documents. Not sure what is going on with this new patch but it has made...

Invalid Property Value
I have a macro that uses an input form to populate a spreadsheet. There are various validations that run prior to the data populating. On one validation if an incorrect choice has been made from a drop down box a error message prompts the user to select again form the drop down box and will not populate until a valid selection has been made. I am now getting an Excel warning message (exclamation message) saying 'Invalid Property Value' after the spreadsheet has populated which only happens if an invalid selection has been made and corrected from the drop down box. If the ...

OFX
When doing an update from my broker I get the message cannot establish secure communication with the server. Digital certificate is invalid. OFXIE12038. This is new happening in last month. Broker support says problem is Money 2002 not supported so I installed Money 2005 (with some qualms) and of course, still get the same message. I can access the brokerage website OK from Money, just cannot get statement downloads. -- Bill ...

Setting sStyle using SetParaFormat
I have some code that does something like this: PARAFORMAT2 pf; pf.cbSize = sizeof(PARAFORMAT2); pf.dwMask = PFM_STYLE; pf.sStyle = kPlain; assert(m_Edit.SetParaFormat(pf)); and then I immediately try to retrieve the style: PARAFORMAT2 pf; DWORD dwMask = m_Edit.GetParaFormat(pf); char buf[200]; sprintf(buf, "%x %d %d", dwMask, dwMask & PFM_STYLE, pf.sStyle); AfxMessageBox(buf, 0, 0); which prints something like this: 8001003f 0 -13108 Questions: 1. Why is the PFM_STYLE mask not set when GetParaFormat returns()? 2. The documentation for PARAFORMAT2 indicates that ...

Backup job using UNC path
In SQL2008, I can manually backup from the server to different machine using UNC path but if I run it using Backup Maintenance Plan - I don't have any success. What am I missing? Thanks, Cecile On Jul 21, 10:26=A0pm, Cecile <Cec...@discussions.microsoft.com> wrote: > In SQL2008, I can manually backup from the server to different machine us= ing > UNC path but if I run it using Backup Maintenance Plan - I don't have any > success. > > What am I missing? > > Thanks, > Cecile What exact error are you getting ? The backup command ...

how to use power point
I am trying to use my power point for the first time and am clueless on how to do it, can you give me instructions on how to use it? "sara benson" wrote: > I am trying to use my power point for the first time > and am clueless That is an understatement. Try posting in a PPt newsgroup. Sara Try a powerpoint news group. Here's a list to all microsoft news groups(with links). http://aumha.org/nntp.htm Gord Dibben Excel MVP On Sun, 11 Dec 2005 20:04:02 -0800, "sara benson" <sara benson@discussions.microsoft.com> wrote: >I am trying to use my power...

Can't use any CRM reports
Whenever I try to run any report in CRM I get the following message: File frs://Input/a_170/000/000/170/cc831bddb68f98.rpt not found on File Repository Server. [On Cache/Page Server: DWCRM.pageserver] Everything else appears to be working correctly on the server. I am running an standard suite installation with 5 users. I am sure something is missing but I can find no information about what is wrong with this error. Thanks Brent, You will need to contact MBS Support to get a utility to re-publish the reports to the repository. Matt "Brent Dunn" <bdunn@digitalwitne...

on-line diary using outlook?
Please can anyone help? trying to set-up a diary with another freelace work colleague. can this be done using microsoft outlook? thank you This from Outlook help: You can share calendar information with other people by using Microsoft Office Outlook 2007 in many ways. In Calendar, in the Navigation Pane (Navigation Pane: A column that provides access to folders used to organize your information. Click a folder to show the items it contains. It also includes the Favorite Folders section and buttons to switch between Mail, Calendar, Tasks and other views.), there are severa...