Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / May 2008

Tip: Looking for answers? Try searching our database.

That darned error message "Object variable or With block variable not     set" !

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Gordon - 26 May 2008 14:17 GMT
I have a form (frmDiscs) which can be opened from several other forms.
In this particular case, it is called from a button on
frmVocalArrangements.  On closing frmDiscs and after updating its
subform (frmDiscsSubform), I want to return to frmVocalArrangments at
the updated record.

To achieve this I put theis code in the load event of frmDiscSubform:

Private Sub Form_Load()
Dim frmCalling As Form
Set frmCalling = Screen.ActiveForm
End sub

After updating frmDiscs and its subform, I put the following code in
the on close event of the subform (frmDiscsSubform):

Private Sub Form_Close()
Dim intVocalistID As Integer, frmCalling As Form
On Error GoTo cmdCloseForm_Click_Err

If Me.Dirty Then Me.Dirty = False

If frmCalling.Name = "frmVocalArrangements" Then
intVocalistID = Me.fldVocalistID

'Requery the original "master" form
Forms!frmVocalArrangements.Requery

'Do Recordsetclone processing
Forms!frmVocalArrangements.RecordsetClone.FindFirst "fldVocalistID=" &
intVocalistID
If Not Forms!frmDiscs.Form.frmDiscsSubform.RecordsetClone.NoMatch Then
Forms!frmVocalArrangements.Bookmark = Forms!
frmVocalArrangements.RecordsetClone.Bookmark
End If
End If
CloseForm Me

cmdCloseForm_Click_Exit:
   Exit Sub

cmdCloseForm_Click_Err:
   MsgBox Error$
   Resume cmdCloseForm_Click_Exit

End Sub

This all works fine except that I am getting an error message "Object
variable or With block variable not set ".  What is causing this?

Thanks

Gordon
Rick Brandt - 26 May 2008 14:31 GMT
> I have a form (frmDiscs) which can be opened from several other forms.
> In this particular case, it is called from a button on
[quoted text clipped - 8 lines]
> Set frmCalling = Screen.ActiveForm
> End sub

The variable frmCalling goes out of scope as soon as the Load event is
finished and as such is destroyed.  If you want to use this variable
eleswhere it would have to be declared as a gloabl variable at the top of a
standard module.

> After updating frmDiscs and its subform, I put the following code in
> the on close event of the subform (frmDiscsSubform):
[quoted text clipped - 7 lines]
> If frmCalling.Name = "frmVocalArrangements" Then
> intVocalistID = Me.fldVocalistID

Here again you are dimming the variable frmCalling and then expecting it to
have a Name property value a few lines later even though it was never set to
an object.  Even if you declared the first frmCalling variable above as a
public variable that would not be the same variable in this code block
because you have dimmed a new one.  You would not do that.  You would just
reference the public variable without having a Dim on it in this code.

You really can go much simpler that this.  Just pass the name of the calling
form as the OpenArgs argument when opening the frmDiscs form.  Then it can
retrieve that value later and do whatever you want to do with it.

Signature

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

Jan Baird - 27 May 2008 20:52 GMT
Jan Baird is out of the country until September 20.   Every effort will be
made to respond to messages, but please be patient.
fredg - 26 May 2008 16:59 GMT
> I have a form (frmDiscs) which can be opened from several other forms.
> In this particular case, it is called from a button on
[quoted text clipped - 49 lines]
>
> Gordon

Nowhere in the Close event code do you have
Set frmCalling = anything.

You have it in the form's Load event but not in the Close.
Values go out of scope when you leave the event, so that value is not
available in the other event.

I would do this differently.

Open the form from some other form and include an OpenArgs argument:

DoCmd.OpenForm "frmDiscs", , , , , , Me.Name
Me.Visible = False

The above opens the other form and hides the current form.
The form's name is passed to the second form.

Then all you need, in the frmDiscs close event, is:

If Not IsNull(Me.OpenArgs) Then
  Forms(Me.OpenArgs).Visible = True
End If

The above makes the calling form, whichever it one it was, visible
again.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Gordon - 26 May 2008 18:30 GMT
Thanks Rick and Fred,

I now understand why my original code was not working ! I am trying to
get this working using the openargs feature you suggested but without
success so far.

On the button on frmVocalArrangements I have the code:

DoCmd.OpenForm "frmdiscs", , , , acFormAdd, , Me.Name ('I am happy to
keep frmVocalArrangements visible - it is in fact hidden behind the
other form.)

But nothing is being passed to the form (I put a stop in some of the
code on frmDiscs and cheked the contents of openargs in the immediate
window - null !

A reminder of what I am tring to do here. I want frmDiscs to open in
add mode - I add details, including the use of a OnNotInListEvent, to
open yet another form to add details before returning to frmDiscs.
Will the openargs value survive all that?

In the on close event of frmDiscs, I want to return to
frmVocalArrangements at the same record on which I started. (my
notinlist code for that works OK).

Help !

Gordon
Rick Brandt - 26 May 2008 18:39 GMT
> Thanks Rick and Fred,
>
[quoted text clipped - 11 lines]
> code on frmDiscs and cheked the contents of openargs in the immediate
> window - null !

Try just putting...

MsgBox Me.OpenArgs

..into some relevent events to see what you get.  What you have should work.

Signature

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

Gordon - 26 May 2008 20:02 GMT
> > Thanks Rick and Fred,
>
[quoted text clipped - 24 lines]
>
> - Show quoted text -

Sorry Rick,

As I suspected, I get an error message "Invalid use of null". But
you're right, it should work.  I tried opening a very simple test form
from the same button on frmVocalArrangments with the MsgBox me.opeargs
in the on open event.  The message correctly showed
frmVocalArrangements as openargs.  I then put that test message in the
on open event of the main form (frmDiscs) and it worked too ! So it
must have something to do with the subform (frmDiscsSubform).

But what?

Help again?

Gordon
Rick Brandt - 26 May 2008 21:34 GMT
> Sorry Rick,
>
[quoted text clipped - 5 lines]
> on open event of the main form (frmDiscs) and it worked too ! So it
> must have something to do with the subform (frmDiscsSubform).

Well you cannot see the OpenArgs from the subform as it applies to the main
form only.  You woudl have to use...

Me.Parent.OpenArgs

Signature

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

John W. Vinson - 26 May 2008 21:42 GMT
> So it must have something to do with the subform (frmDiscsSubform).

If you're putting the Open code on the subform... don't. A Subform isn't
actually opened in its own right, only as a child of the mainform; and it will
not be able to see the openargs property, since it isn't actually being
"opened". Can you put the code in frmDiscs Open event instead?
Signature


            John W. Vinson [MVP]

Gordon - 27 May 2008 07:33 GMT
On 26 May, 21:42, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> > So it must have something to do with the subform (frmDiscsSubform).
>
[quoted text clipped - 5 lines]
>
>              John W. Vinson [MVP]

Thanks Rick and John,

The code is in the main form (frmDiscs) but I didn't realise the
subform could not see the openargs.  Using me.parent.openargs in the
subform solved the problem. Another Access lesson learned !

Fixing this enabled me to complete the form processing to which I
referred in my posts above, but I have just one nagging residual
problem.  When I finally close frmDiscs (and obviously its subform.
frmDiscsSubform) having added data to both, I get a message asking me
if I want to save changes to the design of frmDiscs.

How can that happen?  I haven't opened the form in design mode at any
stage in the data processing.  What code could be sitting in my form
(or subform) that would give rise to that message?

Gordon
Rick Brandt - 27 May 2008 12:21 GMT
> Thanks Rick and John,
>
[quoted text clipped - 11 lines]
> stage in the data processing.  What code could be sitting in my form
> (or subform) that would give rise to that message?

Never seen that, but I believe the newer versions of Access have a property
that determines whether you can make design changes only while in design
view or also in both views.  Perhaps you need to change that setting to
"Design View Only".

Signature

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

Gordon - 27 May 2008 13:44 GMT
> > Thanks Rick and John,
>
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Well that's very strange. I am running Access 2007 but I can't see any
switch (even in Access Options) to only allow design changes whilst in
design mode.  There are switches for "Enable layout view" and "Enable
design changes for tables in datasheet view" but neither of these has
any effect on my "problem" whether they are on or off.

Guess I'll just have to live with the nagging dialog box. It doesn't
make any difference to the data added whether I accept or decline the
offer to save the design changes !

Thanks for all your help.

Gordon
Jan Baird - 27 May 2008 20:52 GMT
Jan Baird is out of the country until September 20.   Every effort will be
made to respond to messages, but please be patient.
Jan Baird - 27 May 2008 20:52 GMT
Jan Baird is out of the country until September 20.   Every effort will be
made to respond to messages, but please be patient.
John W. Vinson - 27 May 2008 18:02 GMT
>Fixing this enabled me to complete the form processing to which I
>referred in my posts above, but I have just one nagging residual
>problem.  When I finally close frmDiscs (and obviously its subform.
>frmDiscsSubform) having added data to both, I get a message asking me
>if I want to save changes to the design of frmDiscs.

If you're closing the form in code, just explicitly specify acSaveNo as a
parameter to the Close action. Are you doing anything like setting a filter or
an orderby on the form?
Signature


            John W. Vinson [MVP]

Gordon - 27 May 2008 19:43 GMT
On 27 May, 18:02, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> >Fixing this enabled me to complete the form processing to which I
> >referred in my posts above, but I have just one nagging residual
[quoted text clipped - 8 lines]
>
>              John W. Vinson [MVP]

John,

I have the following code in the on closee event of the subform
(frmDiscsSubform):

Private Sub Form_Close()
Dim intVocalist As Integer

On Error GoTo cmdCloseForm_Click_Err

If Me.Parent.OpenArgs = "frmVocalArrangements" Then
intVocalist = Me.fldVocalistID

'Requery the original "master" form
Forms!frmVocalArrangements.Requery

'Do Recordsetclone processing
Forms!frmVocalArrangements.RecordsetClone.FindFirst "fldVocalistID=" &
intVocalist

If Not Forms!frmVocalArrangements.RecordsetClone.NoMatch Then
Forms!frmVocalArrangements.Bookmark = Forms!
frmVocalArrangements.RecordsetClone.Bookmark
End If
End If

cmdCloseForm_Click_Exit:
   Exit Sub

cmdCloseForm_Click_Err:
   MsgBox Error$
   Resume cmdCloseForm_Click_Exit

End Sub

The main form (frmDiscs) is not closed from code but even if I put
docmd.close "frmDiscs", acSaveNo in the on close event, I still get
the message asking me to save changes to the design of frmDiscs. There
are no filters or orderbys in operation.  It is a data entry form.
Does the fact that the message asks me to save changes to the design
of frmDiscs indicate that it is that form that has changed, or could
it also mean the subform within the main form?

Gordon
Jan Baird - 27 May 2008 20:52 GMT
Jan Baird is out of the country until September 20.   Every effort will be
made to respond to messages, but please be patient.
Jan Baird - 27 May 2008 20:52 GMT
Jan Baird is out of the country until September 20.   Every effort will be
made to respond to messages, but please be patient.
Jan Baird - 27 May 2008 20:52 GMT
Jan Baird is out of the country until September 20.   Every effort will be
made to respond to messages, but please be patient.
Jan Baird - 27 May 2008 20:51 GMT
Jan Baird is out of the country until September 20.   Every effort will be
made to respond to messages, but please be patient.
Jan Baird - 27 May 2008 20:51 GMT
Jan Baird is out of the country until September 20.   Every effort will be
made to respond to messages, but please be patient.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.