Object invalid or no longer set (AC2007)

>>>> I've set up the relationships and enforced referential integrity in the BE <<<<
That's all you need to do.

Next step is to rethink the Record Source of your subforms and the layout. The focus should be on getting rid of the GROUP BY and using more subforms where necessary. If you're having difficulty removing the GROUP BY let me know or there might be a problem with the design of your tables.
 
I was thinking about this last night

The query used as the record source for the first subform (account level) looks like this :

Code: 
SELECT tblApprovals.AccountID, tblAccounts.AccountNumber, tblAccounts.AccountName, tblBanks.BankName, tblCurrencies.Ccy, tblEntities.Entity, tblSubGroups.SubGroup, tblFundingPartners.FundingPartner, tblFundingRoles.FundingRole, tblApprovals.Reject, tmp.Pending
 
FROM (((((((tblApprovals 
 
LEFT JOIN tblAccounts ON tblApprovals.AccountID = tblAccounts.AccountID) 
LEFT JOIN tblBanks ON tblAccounts.BankID = tblBanks.BankID) 
LEFT JOIN tblCurrencies ON tblAccounts.CcyID = tblCurrencies.CcyID) LEFT JOIN tblEntities ON tblAccounts.EntityID = tblEntities.EntityID) 
LEFT JOIN tblFundingPartners ON tblAccounts.FundingPartnerID = tblFundingPartners.FundingPartnerID) 
LEFT JOIN tblFundingRoles ON tblAccounts.FundingRoleID = tblFundingRoles.FundingRoleID) 
LEFT JOIN tblSubGroups ON tblAccounts.SubGroupID = tblSubGroups.SubGroupID) 
LEFT JOIN 
 
[COLOR=black]  (SELECT tblApprovals.AccountID, Count(tblApprovals.ApprovalID) AS Pending[/COLOR]
[COLOR=black]   FROM tblApprovals[/COLOR]
[COLOR=black]   WHERE tblApprovals.Reject = False[/COLOR]
[COLOR=black]   GROUP BY tblApprovals.AccountID) AS tmp [/COLOR]
[COLOR=black]  ON tblApprovals.AccountID = tmp.AccountID[/COLOR]
 
GROUP BY tblApprovals.AccountID, tblAccounts.AccountNumber, tblAccounts.AccountName, tblBanks.BankName, tblCurrencies.Ccy, tblEntities.Entity, tblSubGroups.SubGroup, tblFundingPartners.FundingPartner, tblFundingRoles.FundingRole, tblApprovals.Reject, [COLOR=black]tmp.Pending[/COLOR]
 
HAVING (((tblApprovals.Reject)=False))

To get rid of the GROUP BY, I created a separate query for the pending count and called it qryPending :

Code: 
SELECT tblApprovals.AccountID, Count(tblApprovals.ApprovalID) AS Pending
FROM tblApprovals
WHERE tblApprovals.Reject = False
GROUP BY tblApprovals.AccountID

And then used that in the main query thus :

Code: 
SELECT [COLOR=red]DISTINCT[/COLOR] tblApprovals.AccountID, tblAccounts.AccountNumber, tblAccounts.AccountName, tblBanks.BankName, tblCurrencies.Ccy, tblEntities.Entity, tblSubGroups.SubGroup, tblFundingPartners.FundingPartner, tblFundingRoles.FundingRole, tblApprovals.Reject, [COLOR=red]qryPending.Pending[/COLOR]
 
FROM (((((((tblApprovals 
 
LEFT JOIN tblAccounts ON tblApprovals.AccountID = tblAccounts.AccountID) 
LEFT JOIN tblBanks ON tblAccounts.BankID = tblBanks.BankID) 
LEFT JOIN tblCurrencies ON tblAccounts.CcyID = tblCurrencies.CcyID) LEFT JOIN tblEntities ON tblAccounts.EntityID = tblEntities.EntityID) 
LEFT JOIN tblFundingPartners ON tblAccounts.FundingPartnerID = tblFundingPartners.FundingPartnerID) 
LEFT JOIN tblFundingRoles ON tblAccounts.FundingRoleID = tblFundingRoles.FundingRoleID) 
LEFT JOIN tblSubGroups ON tblAccounts.SubGroupID = tblSubGroups.SubGroupID) 
[COLOR=red]LEFT JOIN qryPending ON tblApprovals.AccountID = qryPending.AccountID[/COLOR]
 
WHERE tblApprovals.Reject=False

Which removes the GROUP BY

Would this suffice?
 
You've only replaced DISTINCT with GROUP BY so I'm afraid it won't suffice. They do similar things in this context so what you really want to do is return unique records without needing a GROUP BY clause or DISTINCT keyword.
From this point you begin to figure out which tables you can add that will cause it to still remain unique. If any of the tables cause it to show multiple records it's a sign that you need to put that in a subform.
 
I knew you were going to say that... :p

Okay well the problem I have then is that I want this subform to return the list of accounts (Account ID's) for which there are records present in the approvals table. And for each account to appear only once. Ideally, with a count of the number of pending items per account. And also with the associated account information from the relevant tables (I don't think there is a problem with these as each AccountID has only one corresponding BankID, CcyID etc., so no grouping required)

How can I possibly get this recordset without using either DISTINCT or GROUP BY? Excluding either means that I will get one record per pending item rather than one record per account?

Unless you know a way?
 
You shouldn't have asked ;)

Upload the updated fe and be let me have a quick look at the relationships.
 
Is the FE much good to you, given that the relationships are defined in the BE? (As I mentioned before, in the FE, the relationship page is blank?)

In any case - I think I see what you're getting at...

How about this? My thinking is that I am querying the approvals table (potentially multiple records per account), then retrieving the account information for each record, then grouping so that each account only appears once.

As an alternative, I query the accounts table and use a WHERE EXISTS clause to only return those accounts that appear in the approvals table (the number of records doesn't matter). Because I am basing the query on the accounts table, each account can only appear once, without the need to group (and I can still JOIN my qryPending to get the count)

It looks this (and appears to work)

Code: 
SELECT tblAccounts.AccountID, tblAccounts.AccountNumber, tblAccounts.AccountName, tblBanks.BankName, tblCurrencies.Ccy, tblEntities.Entity, tblSubGroups.SubGroup, tblFundingPartners.FundingPartner, tblFundingRoles.FundingRole, qryPending.Pending
 
FROM ((((((tblAccounts 
 
LEFT JOIN tblBanks ON tblAccounts.BankID = tblBanks.BankID) 
LEFT JOIN tblCurrencies ON tblAccounts.CcyID = tblCurrencies.CcyID) LEFT JOIN tblEntities ON tblAccounts.EntityID = tblEntities.EntityID) 
LEFT JOIN tblFundingPartners ON tblAccounts.FundingPartnerID = tblFundingPartners.FundingPartnerID) 
LEFT JOIN tblFundingRoles ON tblAccounts.FundingRoleID = tblFundingRoles.FundingRoleID) 
LEFT JOIN tblSubGroups ON tblAccounts.SubGroupID = tblSubGroups.SubGroupID) 
LEFT JOIN qryPending ON tblAccounts.AccountID = qryPending.AccountID
 
WHERE EXISTS
 
    (SELECT tblApprovals.AccountID
     FROM tblApprovals
     WHERE tblAccounts.AccountID = tblApprovals.AccountID AND tblApprovals.Reject=False)

No GROUP BY clause and no DISTINCT keyword; will this suffice?
 
Still missing the point. I'm not asking you to find a shortcut around GROUP BY or DISTINCT, I'm asking you to breakdown the query, have lesser tables meaning have lesser dependencies. Create the dependencies using subforms.

With subqueries your query may become read-only.
 
The focus should be on getting rid of the GROUP BY
Click to expand...

Hence I tried to rewrite the query without the GROUP BY?...

and using more subforms where necessary
Click to expand...

I don't really understand how I can get the desired output (i.e. one 'master' list of accounts and one 'child' list of approvals per account) by using more than 1 subform in each case?

Scrubbed version is uploaded again here with the relationships...

I'm lost on the whole 'creating dependencies using subforms' piece, I just don't understand what you are suggesting I try?
 

Attachments

Also - pedantry here, apologies - but do I really care if the query becomes read-only? I'm not using the query to update the tables, only to present the current dataset. The tables are updated via VBA-driven SQL directly, triggered by the command buttons, and then the queries are just run again to (in theory) show the updated data post-update. I don't see why these queries can't be read-only and the subforms' record sources just set to snapshot?
 
Thanks vbaInet, I do appreciate it - needless to say, only if you have a spare moment

Can only imagine how frustrating it must be that I'm not 'getting it' and for that I do apologise...
 
vbaInet,

I seem to have fixed the problem but can't really explain why...

I was messing about and, for whatever reason, tried this code as part of the Click event of the Approve / Reject buttons (in the second subform)

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

...and this works!

I'm not entirely sure why it works, when what I was using previously, didn't - namely :

Code: 
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][COLOR=red]    Me.Requery[/COLOR][/COLOR]
[COLOR=red][COLOR=red]    Forms!frmApprovals!sfmApprovalsByAccount.Requery[/COLOR][/COLOR]
 
End Sub

I think it has something to do with not requerying the actual subform, as such, but rather requerying the instance of the subform as it resides within the main form? (Perhaps you can shed some light?)

But - it works!
 
It's not working for me. I think you should test that thoroughly.

I'm still going to look at your db and come back with comments anyway. I've already got it working but need to dig a bit when I get some more time.
 
Haha thanks vbaInet

That's weird, it works perfectly for me, both in .accdb and .accde, and housing FE and BE both locally and across the network

Bizarre that the same behaviour doesn't replicate for yourself?
 
Al, see attached and here are some comments:

1. qryApprovalsNotReject - new query that's joined against Accounts table in qyrApprovalsAccount
2. qryApprovalsAccount - the outter join is not required against qryApprovalsNotReject
3. qryApprovals - removed Criteria reference to the txtAccountID textbox on the main form, not required because filtering is already done on Link Master/Child Fields
4. qryApprovals - Comments could be displayed in the footer in a wider textbox
5. qryApprovals - I've removed the 3 subqueries used to get the comments fields. Look at qryLastComments and qryLastCommDate
6. Requery - only a requery of sfmApprovals is needed
7. sbfApprovals - The formatting of the controls in the footer section can be performed in code rather than through Conditional Formatting. I may have also removed some of the conditional formats in some of the controls so check them.
8. sbfApprovals - Regarding formatting again, if the logic is the same for all controls in the Detail section format one textbox and make the other controls transparent
9. sbfApprovals - Amended all the calculations in the footer section. E.g. Is Null is mainly for use in queries, whereas IsNull() function is for VBA, and you were referencing the AccountID in the main form whereas you could have referenced the AccountID in its own form
10. sbfApprovals and sbfApprovalsByAccount - the Record Source of both subforms have been changed to the equivalent queries I created, same name as your original one but with a "_New" suffix. Anything you see with this suffix is what I created.
11. Please note the way relationships are arranged in the Relationships Designer and in the queries. Just makes it more legible.
12. I'm not sure why you need three instances of tblUsers. Obviously you know better.
13. I've commented out some of the code so some things won't work

Most of the things I've mentioned above have been amended in the forms or queries.

Please if you want to reference any of my comments, you don't need to quote it literally, just reference it by number. Helps to keep your post short.
 

Attachments

AOB,

I read through this briefly. I am just curious why you didn't embed Subform2 into Subform1 and repeated the Master Child relationship like that. So you have two levels of nesting. With this approach you will not get any errors and it's quite easy. If you make the second subform a datasheet you can have a + next to each record in Subform1 and drill down to each record in Subform2 just like with Excel Groupings. Another approach to consider. Perhaps it may be effective.
 
AOB, I just noticed that you require 3 criteria for the Parent Child relationship. You can still do that in Access just separate the fields with a ";" like so: ID;Date;Date

It's just another solution. I understand if you decided to go with your method perhaps for visual reasons.
 
ions said:
AOB,

I read through this briefly. I am just curious why you didn't embed Subform2 into Subform1 and repeated the Master Child relationship like that. So you have two levels of nesting. With this approach you will not get any errors and it's quite easy. If you make the second subform a datasheet you can have a + next to each record in Subform1 and drill down to each record in Subform2 just like with Excel Groupings. Another approach to consider. Perhaps it may be effective.
Click to expand...
He's also using the Link Master/Child fields in the subform, an approach I showed him so nesting a subform in another subform is not the issue. And if you do nest the sub subform will not be seen as a single entity when scrolling. If you read through the entire thread I've already given detailed analysis of what needs doing.
ions said:
AOB, I just noticed that you require 3 criteria for the Parent Child relationship. You can still do that in Access just separate the fields with a ";" like so: ID;Date;Date
Click to expand...
Just the one, AccountID.
 
You must log in or register to reply here.

Similar threads

F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
744
Faoineag
F
C
Another Save Error
Replies
4
Views
419
Isaac
Isaac
C
control variable
2
Replies
30
Views
1,038
cheekybuddha
cheekybuddha
C
jumpy record selector
Replies
16
Views
501
ClaraBarton
C
H
Solved Search across multiple fields
Replies
8
Views
865
Harry Paraskeva
H

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom