Object invalid or no longer set (AC2007)

[AOB]

Hi guys,

I'll try my best to explain this one but I'm stumped as to the cause...

I have a form with two continuous subforms. The records returned in the first subform will each have multiple associated records from another dataset, which are displayed in the second subform.

To achieve this, I have a command button for each row of the first subform. Clicking on it takes the identifier for the selected record and passes it to a hidden textbox on the main form. The second subform uses the value in that hidden textbox as the criteria for its own query. This allows me to effectively pass a selected value from the first subform over to the second subform, via the common parent.

Using the OnClick event of the command button, the code checks the hidden textbox in the main form. If it already holds the value it was going to pass, the hidden textbox is cleared (in other words, the user is deselecting that record). If it doesn't, it passes the value (the user is selecting that record)

The query for the second subform therefore looks something like this :

SELECT [tbl1].[fld1], [tbl1].[fld2], [tbl2].[fld3]...
FROM [tbl1]
INNER JOIN [tbl2] ON [tbl1].[fld4]=[tbl2].[fld5]...
[COLOR=red]WHERE [tblx].[fldy] = Nz([Forms]![frmMainForm]![txtID],0)[/COLOR]

So if the hidden textbox is empty (no record selected) the query returns an empty dataset (the ID will never be 0) for the second subform. Otherwise, the query returns any records associated with that ID.

Works fine everywhere else I've used this approach

However, with my latest form, I need to use 3 hidden textboxes (this is just the quickest / easiest way given the complexity of the related tables)

Same basic principle though, only difference being that on clicking the command button in the first subform, I pass 3 values to 3 hidden textboxes (one ID and two dates) Or, if the ID is already present (already selected), I clear all 3.

And then the query for the second subform looks like this :

SELECT [tbl1].[fld1], [tbl1].[fld2], [tbl2].[fld3]...
FROM [tbl1]
INNER JOIN [tbl2] ON [tbl1].[fld4]=[tbl2].[fld5]...
[COLOR=red]WHERE [tblx].[fldy] = Nz([Forms]![frmMainForm]![txtID],0)[/COLOR]
[COLOR=red]AND [tblx].[fldz] >= Nz([Forms]![frmMainForm]![txtDate1],#12/31/1999#)[/COLOR]
[COLOR=red]AND [tblx].[fldz] < Nz([Forms]![frmMainForm]![txtDate2],#01/01/1900#)[/COLOR]

Again, the theory is that if the hidden ID textbox is empty (no record selected) the query returns a blank dataset (the ID will never be 0 and no date can fall in that range) for the second subform. Otherwise, the query returns the records associated with that ID and between those dates.

Here's the problem (finally!!)

The functionality works fine initially. When the form loads, the first subform has records, the second subform is empty (no record selected yet) When I select a record from the first subform, the second subform populates with the correct results associated. When I change my selection in the first subform, the second subform updates accordingly.

But when I deselect from the first subform (and empty all the hidden textboxes) the second subform displays "#Name?" across all the visible controls and after a couple of seconds I get the following error :

Object invalid or no longer set

(I actually get this error several times, with random delays in between, until I close the form)

At this point, selecting / deselecting records in the first subform has no effect, the second subform is basically defunct. If I close the form and reopen it, everything's back to normal again.

I have fairly extensive error handling throughout the DB but it is not firing (i.e. it is not logging the error to my error table) so it's difficult to pinpoint exactly where I need to focus my attention.

What's weird is, I use this method of passing values between subforms via hidden textboxes on the common parent quite a bit and it works seamlessly everywhere else. The only difference I can see here is that I'm passing 3 'hidden' values instead of one?

Can anybody make a suggestion as to what I should be checking?

Thanks & apologies for the long-winded synopsis; if you've made it this far, I thank you for your patience!

Al

 

[vbaInet]

These are the main points I picked out from your post. In the future please try and condense it so that your question can be answered quicker.

I have a form with two continuous subforms. The records returned in the first subform will each have multiple associated records from another dataset, which are displayed in the second subform.

To achieve this, I have a command button for each row of the first subform. Clicking on it takes the identifier for the selected record and passes it to a hidden textbox on the main form. The second subform uses the value in that hidden textbox as the criteria for its own query.

Using the OnClick event of the command button, the code checks the hidden textbox in the main form.

If the hidden textbox is called "txtHidden1" (for example) you can use the Link Master/Child properties of a subform to link the second subform to it. That way everything stays in sync. Now to do it all you have to do is type in the name of the textbox directly into the Link Master Field property, and type the name of the ID into the Link Child Field property. If you click the ellipsis button and try to do it through the wizard it won't work that's why I said type it in.

 

[AOB]

Thanks vbaInet

Apologies for the lack of brevity; the problem is, I don't know where the problem is, so I thought I should give as much background as possible to help isolate it. (I got a telling off before for condensing the question and not providing enough information, precisely for the reasons you laid out...!)

I've never tried using the Link Master/Child properties before, mainly because I never really understood it (or rather, I got the jist of it, but never knew how to use it properly) So this will be a good learning experience.

Before I mess everything up though...

Can this be done with multiple fields? That is, the 3 hidden textboxes that I use as parameters for the query? Or are you suggesting I use the ID as the Master/Child link and keep the reference to the dates as they are in the existing query?

Also, do I need to remove the ID parameter from the underlying query? Or keep it as it is?

Finally, do I only need to update the Link Master/Child properties on the second subform or on the first one as well?

Sorry, I've never done this before and I don't fully grasp how it works and what the implications are?

Thanks

Al

 

[vbaInet]

Don't worry, as long as you mention the main points (enough to give some background), we can delve into details later.

What the Link Master/Child properties do is synchronise the child form with the parent form (which Microsoft decided to call Master) via their respective IDs. So if subform1 is the one passing values it would be the Master, and if subform2 is the one receiving the value it will be the child.

Try it with just the ID and we'll look into incorporating the dates.

 

[AOB]

Okay...

I'm inferring from the above that you are suggesting not using the main form as a location to store the common identifier but to link the two subforms together directly. That being the case...

At this point let me provide names for the subform controls as it may get confusing. The first (Master) is called [sfmReportRevaluations] and the second (Child) is called [sfmReportRevaluationsImpact]

When I click on [sfmReportRevaluations] in the design mode of the main form, and go to Properties > Data > Link Master Fields, and click on the 'builder' which appears to the right, I get the following error :

Can't build a link between unbound forms

Ditto with Link Child Fields

And ditto again with [sfmReportRevaluationsImpact]

I can, however, manually key in a value...

So what I've done is, on [sfmReportRevaluationsImpact], I've changed the properties to the following :

Link Master Fields : [sfmReportRevaluations].[txtCurrentID]
Link Child Fields : [AccountID]
​

Where :

• [txtCurrentID] is the name of the textbox within [sfmReportRevaluations] which holds the identifier for the currently selected record. This textbox is within the FormHeader section, so does not repeat in the continuous Detail section
• [AccountID] is the field in the query of [sfmReportRevaluationsImpact] which should use that identifier as a parameter value (in the WHERE clause)

When I save, close in Design Mode and open in Form Mode, I am prompted to provide [sfmReportRevaluations].[txtCurrentID]
Where do I go next?...

 

[vbaInet]

I did mention in my initial not to click on the elipsis button (i.e. the builder/wizard) and that it must be typed in.

txtCurrentID is on your main form right? It's already pulling the data from the subform. So that tells you it shouldn't be prefixed with the subform name.

 

[AOB]

You did - sorry, I wasn't sure what you meant by 'elipsis'...

[txtCurrentID] is on the first subform. I thought you were suggesting linking the two subforms directly and bypassing the main form :

you can use the Link Master/Child properties of a subform to link the second subform to it

The corresponding textbox on the main form is called [txtAccountID]

So have changed the properties as follows :

Link Master Fields : [txtAccountID]
Link Child Fields : [AccountID]

​

This now seems to work! I can deselect a record from the first subform and the second subform does not crash!

However - it does retain the records for the previously selected record? I can select another record and it updates correctly but when nothing is selected, it should revert to blank?

And I am triggering a requery on each selection / deselection so when a record is deselected, the query should be searching for records with an AccountID of 0 (of which there are none, therefore an empty dataset)

Any suggestions?

 

[vbaInet]

I thought you were suggesting linking the two subforms directly and bypassing the main form

I wasn't suggesting that. I was just telling you how you can link a subform to another subform.

So let me explain. Let's say you have a parent form (Parent1) and two subforms (subform1 and subform2). In simple terms you want a setup where Parent1 syncs with subform1 and then subform1 syncs with subform2 right?

At this point I'm guessing you're already happy with the linkage between Parent1 and subform1 so let's proceed with the subforms.

For the linkage between subform1 and subform2:
* You will need to place one textbox (let's call it txtLinkToSub2) on Parent1 and in its Control Source you'll link it to the ID field from subform1 like so:

=[COLOR="blue"]Subform1[/COLOR]!ID

So as you can already tell txtLinkToSub2 is pulling the ID value from subform1 which we'll use to sync subform2
* In the Link Master Field for subform2 you'll key in txtLinkToSub2 and in the Child you'll enter the name of the ID field in subform2.

Does this help?

 

[AOB]

Yes it does, and thank you. It's actually working great now, the problem with the 'Object invalid or no longer set' error has gone away

The only problem I'm left with now is where a record is selected, then subsequently deselected in subform1. On selection, subform2 is filled with the relevant records. But on deselection, subform2 retains those records. It should become blank.

The linking textbox (your 'txtLinkToSub2'; my 'txtAccountID') is empty. I've made it visible so I can track its value throughout. Which means the WHERE clause for the query for subform2 :

WHERE [tblBalances].[AccountID] = Nz([Forms]![frmReportRevaluations]![txtAccountID],0)
AND [tblBalances].[BalanceDate] >= Nz([Forms]![frmReportRevaluations]![txtValueDate],#12/31/9999#)
AND [tblBalances].[BalanceDate] < Nz([Forms]![frmReportRevaluations]![txtReportDate],#01/01/1900#);

Should evaluate to :

WHERE [tblBalances].[AccountID] = 0
AND [tblBalances].[BalanceDate] >= #12/31/9999#
AND [tblBalances].[BalanceDate] < #01/01/1900#);

Which should return no records (there is no record with an AccountID of 0, whatever about the dates)

So why are the records being retained when the dataset returned by this query, with these parameters, should be empty? And when I am requerying subform2 as part of the selection / deselection process?

(As an aside - I can get around this problem by simply hiding subform2 when no records are selected - which I want to do anyway - so this is purely for my own curiosity and understanding rather than a major issue for my finished DB...)

Thank you so much for your patience and clarity in helping me fix this and learn Master/Child links, I really appreciate it

Al

 

[vbaInet]

There's an explanation but I can't remember because it's been a long time I used Access. If you upload a sample db I'll be able to tell you why.

 

[AOB]

Thanks mate. My overall DB is huge (in terms of complexity) and contains quite sensitive information which I would not be able to share. It would take me quite some time to create a cleansed version which I would be able to upload. And considering I have a satisfactory workaround, it's probably not worth the time either of us would spend looking into it.

I'll play around with it here and see if i can figure it out. I suspect it's because the Master field is empty (I could have it default to 0 rather than Null, maybe that would do the trick?)

I'm mightily impressed that you were able to guide me through all of the above not having used Access in a long time. I use it every day and I can barely remember how to bind a textbox...

 

[vbaInet]

Ok I've just replicated your design on my end and I can't see what you mean. How do you de-select?

 

[vbaInet]

And by the way, which subform is the SQL you showed me in post #9 on?

 

[AOB]

Deselection is via the same command button on the first continuous subform (one button per record) - OnClick event code is below

The SQL from #9 is from the query used as the RecordSource for the second continuous subform

Private Sub comExpand_Click()
 
  On Error GoTo ErrorHandler
 
  Dim frm As Form
 
  Set frm = Forms("frmReportRevaluations")
 
  With Me
 
    If frm.txtRevaluationID.Value = .txtRevaluationID.Value Then
 
      ' Record already selected - this is a deselect action
      ' Clear the hidden controls on the main form
 
      frm.txtRevaluationID.Value = ""
      frm.txtAccountID.Value = ""
      frm.txtValueDate.Value = ""
 
      ' Update the 'CurrentID' in the subform (for CF to highlight selections)
 
      .txtCurrentID.Value = ""
 
      ' No record selected - hide the 2nd level subform
 
      frm.lblImpactDetail.Visible = False
      frm.sfmReportRevaluationsImpact.Visible = False
 
    Else
 
      ' Record not already selected - this is a select action
      ' Pass the values for the selected record to the hidden controls on the main form
 
      frm.txtRevaluationID.Value = .txtRevaluationID.Value
      frm.txtAccountID.Value = .txtAccountID.Value
      frm.txtValueDate.Value = .txtValueDate.Value
 
      ' Update the 'CurrentID' in the subform (for CF to highlight selections)
 
      .txtCurrentID.Value = .txtRevaluationID.Value
 
      ' Record selected - show the 2nd level subform
 
      frm.lblImpactDetail.Visible = True
      frm.sfmReportRevaluationsImpact.Visible = True
 
    End If
 
  End With
 
  ' Requery the 2nd level subform
 
  frm.sfmReportRevaluationsImpact.Requery
 
Exit_comExpand_Click:
 
  Set frm = Nothing
  Exit Sub
 
ErrorHandler:
 
  Call LogError(Err.Number, Err.Description, "comExpand_Click", Me.Name)
  Resume Exit_comExpand_Click
 
End Sub

 

[vbaInet]

Ok, I see some problems. You're setting it to the empty string "" in stead of Null. And instead of setting all three hidden textboxes to Null, set only the top most hidden textbox to Null and the children will follow suit.

In any case, I would advise that you don't set their values anyway because all you're doing is forcing a requery everytime you set to Null and set to a value so just hide and unhide instead.

One final note, it looks like you're constantly setting the values of the hidden textboxes in code. You must have a reason for doing that? You of course know (as I had explained in previous posts) that you can hard code the Control Source instead right?

 

[AOB]

Thanks vbaInet

Like I say, I hadn't delved into Master / Child links before now so I will revisit and see if that would be a more efficient method of doing things.

Have a great weekend and thanks again for all your assistance

 

[vbaInet]

You're welcome! Have a good one too.

 

[AOB]

Hi vbaInet / all

Sorry to revisit but this is causing me problems when I thought I had it sorted...


It's a different form but same basic principle :

• one parent form
• two subforms
• one-to-many relationship between the records in subform1 and subform2
• command button in subform1 used to 'select' a record
  • which populates a textbox on the main form (bound)
    • which in turn is used as the Master field for subform2 (Master/Child Links)

With a twist :

• command button in subform2 used to update the record (sets a Boolean field to 'True')

So the query which is used as the RecordSource for subform2 includes a check on a Yes/No field and only returns records which are set to 'False'. Clicking the command button next to the record in subform2 sets this flag to 'True', hence a requery should remove this record from subform2.

But it doesn't? Or, rather more weirdly, it doesn't every time (sometimes it does, sometimes it doesn't)

When I step through the code line by line it works every time - perfectly! But at run-time, it's hit and miss. I know the update to the table is working because if I close the form and check the table, it has been updated. So it's as if the requery isn't triggering - or, rather, the requery isn't triggering every time (which I don't understand?)

This is the code attached to the command button in subform2 :

Private Sub comApprove_Click()
  Call ApproveUpdate(Me.txtApprovalID.Value)
  Forms("frmApprovals").sfmSubForm1.Requery
  Me.Requery
End Sub

The ApproveUpdate function just checks the record to see what type of update is required (new record, update to an existing record or record deletion) and performs the necessary SQL on the table

Once the record is updated, I requery both subforms - the record which has just been updated should be removed from SubForm2 (it no longer meets the criteria of the query), leaving the remaining records, if any exist. If no records remain in SubForm2, the corresponding master record in SubForm1 should also disappear. This should continue until all of the records in SubForm2, for each record in SubForm1, have been updated, leaving both subforms empty.

Only it doesn't? At least, not consistently - annoyingly, sometimes it works and sometimes it doesn't? Sometimes the record disappears (i.e. the requery "works") and sometimes it stays visible even though I know the underlying table has been appropriately updated (i.e. the requery "doesn't work")

The data is correct, it's just what is presented in the subforms is misleading and this could potentially cause serious problems. I need the subforms to show an accurate representation of what is outstanding in real time, as items are being approved and rejected.

Any suggestions as to where to look? As I say, when I debug the code, it works flawlessly, making it really difficult to pinpoint exactly where the problem is?

Thanks in advance

Al

 

[vbaInet]

You're complicating matters here Al. I thought we already discussed how the relationships should be handled?

It's a different form but same basic principle :

• one parent form
• two subforms
• one-to-many relationship between the records in subform1 and subform2
• command button in subform1 used to 'select' a record
  • which populates a textbox on the main form (bound)
    • which in turn is used as the Master field for subform2 (Master/Child Links)

Why are you needing to go up to the bound field in the parent form and from there go down to subform2 but still have subform1 and subform2 synchronised? It's this back and forth that could be causing the confusion here.

You already have a 1-to-many relationship between subform1 and subform2. If you need to update a field in the parent form, do that independently from subform2. The relationship is between sub1 and sub2 not main and sub2.

Like already discussed in previous posts, to synch subform1 and subform2:

1. drop a hidden unbound textbox in the main form, call it txtLink1To2
2. in the Control Source enter:

=[COLOR="blue"][COLOR="blue"]Subform1[/COLOR][/COLOR]![COLOR="blue"]NameOfField[/COLOR]

Where NameOfField is the field that links subform1 to subform2 at table level.
3. Go to Subform2 and enter txtLink1To2 as the Link Main Field and the name of the field as the Child.

I'm not even sure why you're needing to update a field in the main form from subform1. Sounds like a normalisation problem to me.

 

[AOB]

I'm not even sure why you're needing to update a field in the main form from subform1

Is that not what you're doing with this?

1. drop a hidden unbound textbox in the main form, call it txtLink1To2
2. in the Control Source enter : =Subform1!NameOfField

That's exactly what I've done. Perhaps you mean driving the update from subform1 (i.e. via VBA on the OnClick event) - in which case, that's not what I'm doing - I've linked the textbox on the main form directly to the corresponding textbox on subform1 which is, I believe, what you are recommending above?