Object invalid or no longer set (AC2007)

[vbaInet]

Is that not what you're doing with this?

No Al. I didn't mention anything about updating fields from one form to another. Linking is what I'm doing and Access does everything else.

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?

You don't drive the synching, Access does that.

 

[AOB]

Okay, sorry if I didn't make it clear, but I have my form + subforms set up exactly that way. Textbox control on the main form with a ControlSource of a textbox in the first subform. Second subform using Master / Child links based on textbox control on main form as described above and corresponding field in RecordSource.

But as records in the second subform are updated (via the second subform itself) the recordsets are NOT automatically updating (so records which should no longer meet the criteria of the underlying queries are not disappearing from view)

Hence I have tried using .Requery as part of the update process but that does not work either?

Does this make sense?

 

[vbaInet]

If you can whip up a quick db that replicates the problem, I'll have a look.

 

[AOB]

You're very kind vbaInet - let me see what I can knock together this morning...

 

[AOB]

vbaInet,

Sorry for the delay, it took me several hours to properly scrub the DB to something appropriately shareable. Here it is. The form(s) in question are under the 'Approve' button (the rest have been disabled and the underlying functionality removed)

I've put in a couple of dummy issues pending approval - suggest you use the reject option (to move an issue from 'Approvals' to 'Rejections') and the resubmit option (to move it back again) to save you having to manually add your own directly into the tables (I had to remove the interface for adding issues as it was all inherently linked to very sensitive information)

Weirdly, the 'Rejection' side seems to be working perfectly (not so in my main version :banghead but the same problem still persists on the 'Approve' side - as you approve / reject records, the subforms fail to reflect the changes. As I mentioned before, I did try to trigger this by adding a .Requery into the code for approving and rejecting, but it had no effect.

Hope it makes sense, apologies if it's a bit amateur - needless to say, happy to answer any questions if anything isn't clear

Thanks again vbaInet, very much appreciated!

Al

 

[vbaInet]

What's the name of the form(s) I should be looking at?

 

[AOB]

frmApprovals is the main form

sfmApprovalsByAccount and sfmApprovals are the two subforms within it

One-to-many relationship between them

 

[vbaInet]

I guessed as much. Will have a look in a moment.

 

[vbaInet]

Only had time to look at your db Al.

I don't see an Me.Requery code in any of your buttons. Or you're not aware what this does? This is what you need to reflect any changes.

And the txtCurrentID textbox on subform1 that updates the txtAccountID textbox on your main form that in turn syncs subform2 is not what I advised. Just set the Control Source of txtAccountID (on the main form) to:

=[sfmApprovalsByAccount].[Form]![AccountID]

The rest access will deal with. So that code you have in the click event of the button to sync txtCurrentID and txtAccountID is not needed.

NB: Bear in mind that once you requery a form it jumps to the first record.

 

[AOB]

Thanks vbaInet - I appreciate you taking the time to look at this at all, especially in your own free time, so no complaints about the delay (obviously!)

So...

So that code you have in the click event of the button to sync txtCurrentID and txtAccountID is not needed

Interesting - I didn't realise I could link it to the current record of the subform. Have changed the ControlSource of the textbox on the main form to suit. That said, I do want to keep the code as well, but only because I use that txtCurrentID to drive the conditional formatting within the subform (to highlight the selected row and 'grey-out' the unselected rows) It's purely for aesthetic and to make it easy for users to identify what they are working with. I will no longer use it to sync it to the main form.

I don't see an Me.Requery code in any of your buttons. Or you're not aware what this does? This is what you need to reflect any changes.

I did have Me.Requery in there but I took it out of the scrubbed version (for some reason I thought you were suggesting Access would automatically refresh due to the linking structure between the subforms - which, to be honest, didn't make sense to me in the first place, so I'm really glad you're telling me to put it back in!!)

I actually need to requery both subforms (to update the count of the pending items per account and / or remove the 'master' record entirely if no pending items remain

So my code on the Approve / Reject buttons now looks like this :

Private Sub comApprove_Click()
 
  Call ApproveUpdate(Me.txtApprovalID.Value)
 
  [COLOR=red]Me.Requery[/COLOR]
 
[COLOR=red]  Forms!frmApprovals!sfmApprovalsByAccount.Requery[/COLOR]
 
End Sub
 
Private Sub comReject_Click()
 
  Call RejectUpdate(Me.txtApprovalID.Value)
 
  [COLOR=red]Me.Requery[/COLOR]
 
[COLOR=red]  Forms!frmApprovals!sfmApprovalsByAccount.Requery[/COLOR]
 
End Sub

And guess what? It works absolutely perfectly in the scrubbed version! Everything updates exactly as it should. But this is how I coded it in the first place?...

So - if I take that scrubbed version, split it into FE/BE and convert the FE into an .accde - it no longer works?

Try it yourself - or if you like I can upload another zip - the exact same code no longer performs as it should. The updates to the tables ARE taking place - if you close and reopen the forms, they show the correct information. But even though a record has been approved / rejected, this isn't immediately reflected in the subforms?

The ApproveUpdate / RejectUpdate functions are working (the tables are duly updated) but it's as if the .Requery's which follow them aren't firing? Or, perhaps the .Requery's are firing before the updates have actually completed (?)

As I mentioned before, if I debug the code and step through it line by line, it works just as it does in the standalone version. The problem only happens at run-time. I think I need some kind of Pause or Refresh or DoEvents-type command before I fire the Requery. But I don't really understand a) why this is necessary and b) what is appropriate?

This is exactly the same behaviour I'm getting in my master version - I was half wondering (worrying) if the problem was down to a much larger dataset but I'm getting exactly the same behaviour with this scrubbed version, which has only a half dozen records.

Can you do the split / .accde on your side and see if you get the same behaviour? Even with the .Requery, the subforms don't update - and the only difference is that the DB has been split and the FE is an .accde file?

Thanks mate!

Al

 

[vbaInet]

Access does the synching but refreshing of data still needs to be done in code.

For now leave the Requery code there but as it is you're requerying more than you need to. It needs to go into the part where data has changed. I'll explain how to go about this later, so just leave it there for now.

I would rather you give me your split version with a few more Approved/Rejected data. That way we'll be working on the same "problem" db.

One question, does it work if the BE is on your local machine?

 

[AOB]

Thanks vbaInet - here is the new zip

1 x .accdb front-end
1 x .accde front-end
1 x .accdb back-end

You may need to relink the tables locally once you save your own copy

Have added a few more records - if you need more you can just add them to tblApprovals

I copied everything to a folder on my local machine and it still doesn't work...

 

[vbaInet]

Right Al, here are my questions/comments/findings:

1. Do your tables have any relationships? I opened up the relationships page and there's none.
2. The problem is a combination of point 1 and the use of too many tables. There are just too many dependencies on one form.
3. From point 2, I noticed that you're doing a GROUP BY in the Record Source of your subform, and to be honest GROUP BY is mostly used for aggregating data not for hiding duplicates for use in an updatable form.
4. I created a simple subform based on tblApprovals, clicked on Approved and after a requery (in code) the subform was immediately updated.

Points 1 to 3 need addressing.

 

[AOB]

Thanks vbaInet

Sorry, busy day, saw this first thing but only getting around to it now...

1 - No. I was slightly wary of 'hard-coding' relationships between the tables for fear of cascades - whereby deleting a record in one table could lead to deleting a whole bunch of records in another. So I've designed each table to 'stand alone' and relate them using JOINS with each query as I build it. That said, if I know that defining these relationships up-front is a) correct and proper practice and b) will not compromise the data, I would happy to give it a try? Are there any considerations I should be aware of before jumping into the Relationships page and linking stuff together?

Bear in mind that, once implemented, this should be a fairly dynamic DB. Users will be constantly adding information across most of the tables, via the FE interface, in some cases in reasonably large volume. What I desperately want to avoid is any situation where, somewhere down the line, this data becomes in any way corrupted or lost, because Access 'thought' a record was no longer required or valid (because of an inherent relationship) Do please tell me if I'm being unnecessarily careful (i.e. paranoid!)

2 - The reason I have so many tables is that I am trying to normalise as much as possible. I was always of the impression that it was better to have lots of small tables than a handful of large tables (by small/large, I mean the number of fields) The DB stores a lot of information and I'm trying to do it as efficiently as possible by segregating everything into groups and linking via common identifiers. I thought this was best practice, no?

3 - I presume you are referring to sfmApprovalsByAccount? If so, then yes, I am aggregating - because I only want to see one record per account (the details of the account in question and the number of pending items), not each individual pending item (which is the purpose of the other subform) If I didn't GROUP BY, I would get the same account multiple times (one for each pending item) which I don't want there?

Like I say, I'm happy to try hard-coding the table relationships, just nervous about messing around with the structure detrimentally!

Thanks again vbaInet, much appreciated!

Al

 

[vbaInet]

1. Nothing wrong with setting the relationships before-hand. It helps to give the engine a view of how everything is interlinked. Cascade update and cascade delete is something you set when you create the join, it's not going to delete or update a child record if it's not set to do so.
2. The tables I was referring to are those used to form the Record Source of your form.
3. If you're needing to GROUP BY at form level then it's an indication that you're joining on too many tables.

Did you try out the subform test I mentioned?

 

[AOB]

Hi vbaInet,

Long weekend here in Ireland, haven't been on...

Your subform test is based solely on tblApprovals, is that correct? I can replicate that but how do you retrieve the account-specific information (in tblAccounts, tblBanks, tblCurrencies etc.) without JOINs?

I guess what I'm struggling with here is, the general advice is to maximise the use of normalised data. And you are telling me I'm using too many tables. These two contradict each other (if I reduce the number of tables, I also reduce the normalisation) So which should take priority?

What exactly is wrong with using GROUP BY at form level? I would have thought GROUP BY was a fairly standard instruction in any query (requiring aggregation) and therefore should be a perfectly valid command in a query used to populate a subform?

 

[vbaInet]

I hope you've enjoyed it.

Yes it's solely based on tblApprovals as a test.

You're misreading my posts. I said you're joining on too many tables in the Record Source of your form. I'm talking specifically about the Record Source, nothing related to normalisation. And again, like I mentioned in my last post, it can broken down using subforms or any other design techniques. You're trying to do too much in one view.

A GROUP BY clause is used to aggregate data and generally used for displaying data. If you're aggregating to display records in a read-only form, then it's absolutely fine. However, if you're doing this in an editable form to allow you get unique records, as I have mentioned before, this an indication that you should break down the join in the Record Source of your form.

You're spending a too much time trying to justify your current setup rather than re-thinking your design to reach a solution.

 

[AOB]

Sorry, I am probably misinterpreting

I guess what I'm trying to say is, I don't know how to break it down in such a way that I can present the data I need to present, in each of the two subforms, without using JOINs. If I knew, I'd be more than happy to redesign to fit?

How does one 'break down a JOIN'?

Can you send me a simple example from which I could extrapolate?

If I set up a subform with a Record Source based solely on tblApprovals, I will only get the information held within that table. How do I expand that to include the account-specific information without JOINing to those other tables?

Sorry, it's a steep learning curve for me so appreciate your patience!

 

[vbaInet]

How does one 'break down a JOIN'?

By using a subform to represent the child record just like you've already done with Rejected and Approved. A subform within a subform if you like.

Or

By breaking up your form into logical parts, i.e. using tabs to hold related information.

Your db has has grown a great deal and I know I won't have time to change anything there. Plus there are no relationships so I can't begin to guess what goes where.

Begin with setting up the relationships and enforcing referential integrity in the Relationships Designer.

 

[AOB]

I've set up the relationships and enforced referential integrity in the BE. Do I have to replicate this in the FE? (The relationships screen in the FE is blank - also, it's not possible to enforce referential integrity in the FE)

What's next?