Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-10-2019, 03:35 AM   #1
matt beamish
Newly Registered User
 
Join Date: Sep 2000
Location: Leicester, UK
Posts: 181
Thanks: 18
Thanked 1 Time in 1 Post
matt beamish is on a distinguished road
Complex query - readonly recordset workaround

Hi folks. I run a membership database where I use a bunch of nested queries to show who has paid/not paid their subs in a given year, and also who has overpaid and underpaid. I then set my membership forms recordsource to be the result of these queries. This is good for filtering the data, but invariably ends up with non-editable data in the forms due to the complex queries running to generate the data. I think what I need to know how to do, is to create a temporary table that just includes the MemberIDs in any given query, and use that to match against the membership table so that I can then edit the form data.

Currently I have a form button, 'Unpaid Current Year' which on click runs On event: Me.RecordSource = "Q_CY_Not_Paid"

My membership table is "T_Membership_details".

Hope this makes sense.

thanks

matt beamish is offline   Reply With Quote
Old 09-10-2019, 04:04 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,238
Thanks: 40
Thanked 3,645 Times in 3,515 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Complex query - readonly recordset workaround

the normal rule is one form, one table but depending on how complex your queries are and on the assumption that what you are trying to update from your form is in a single table, try changing the form recordset type from dynaset to dynaset inconsistent updates.

another option is to have an unbound combo which list the members and modify that rowsource rather than the form recordsource.

I'm surprised the queries are so complex, would have thought it would be pretty straightforward to determine over/under paid, paid/not paid in a single query.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-10-2019, 04:06 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,649
Thanks: 50
Thanked 1,068 Times in 1,049 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Complex query - readonly recordset workaround

Hi. Why not open an updatable form based in the id you selected from the read only form?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-10-2019, 04:25 AM   #4
matt beamish
Newly Registered User
 
Join Date: Sep 2000
Location: Leicester, UK
Posts: 181
Thanks: 18
Thanked 1 Time in 1 Post
matt beamish is on a distinguished road
Re: Complex query - readonly recordset workaround

Quote:
Originally Posted by CJ_London View Post
the normal rule is one form, one table but depending on how complex your queries are and on the assumption that what you are trying to update from your form is in a single table, try changing the form recordset type from dynaset to dynaset inconsistent updates.

another option is to have an unbound combo which list the members and modify that rowsource rather than the form recordsource.

I'm surprised the queries are so complex, would have thought it would be pretty straightforward to determine over/under paid, paid/not paid in a single query.
thanks for reply.
The rates for each subscription year (there are three types) are held in one table. Payments received are held in two tables - cash and cheques paid in on pay in slips, and bank credits paid directly (the later two tables I join in a Union query). So I need to run a query to sum together what has been paid and then run a calculation against what they should pay to give a balance.
Then there is the Member details which are held separately.
matt beamish is offline   Reply With Quote
Old 09-10-2019, 04:28 AM   #5
matt beamish
Newly Registered User
 
Join Date: Sep 2000
Location: Leicester, UK
Posts: 181
Thanks: 18
Thanked 1 Time in 1 Post
matt beamish is on a distinguished road
Re: Complex query - readonly recordset workaround

Quote:
Originally Posted by theDBguy View Post
Hi. Why not open an updatable form based in the id you selected from the read only form?
Thanks for this. So what is the VB for this?
My key is "PrivID" and will appear in all tables and queries that relate to individual membership records.
I want to only show records in "T_Membership_details" where "PrivID" = "Q_CY_notPaid.PrivID"

cheers
Matt
matt beamish is offline   Reply With Quote
Old 09-10-2019, 04:34 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,649
Thanks: 50
Thanked 1,068 Times in 1,049 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Complex query - readonly recordset workaround

Quote:
Originally Posted by matt beamish View Post
Thanks for this. So what is the VB for this?
My key is "PrivID" and will appear in all tables and queries that relate to individual membership records.
I want to only show records in "T_Membership_details" where "PrivID" = "Q_CY_notPaid.PrivID"

cheers
Matt
Hi Matt. Assuming you have a form bound to T_Membership_details, you could try something like:
Code:
DoCmd.OpenForm "FormName", , , "PrivID=" & Me.PrivID
Make sure to replace Me.PrivID with the correct name from your read only form.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-10-2019, 04:37 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,238
Thanks: 40
Thanked 3,645 Times in 3,515 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Complex query - readonly recordset workaround

Quote:
Payments received are held in two tables - cash and cheques paid in on pay in slips, and bank credits paid directly (the later two tables I join in a Union query).
these could be in one table with a field to indicate cash/cheque/bank credit.

You could also include the membership fee due in the same table with the above new field to indicate 'fees due'

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-10-2019, 04:40 AM   #8
matt beamish
Newly Registered User
 
Join Date: Sep 2000
Location: Leicester, UK
Posts: 181
Thanks: 18
Thanked 1 Time in 1 Post
matt beamish is on a distinguished road
Re: Complex query - readonly recordset workaround

Thanks again. Yes lets assume my form is bound to "T_Membership_details". In order to only show "PrivID" in the query result, somewhere I am going to need to give "Q_CY_Not_Paid" as the source.
matt beamish is offline   Reply With Quote
Old 09-10-2019, 04:42 AM   #9
matt beamish
Newly Registered User
 
Join Date: Sep 2000
Location: Leicester, UK
Posts: 181
Thanks: 18
Thanked 1 Time in 1 Post
matt beamish is on a distinguished road
Re: Complex query - readonly recordset workaround

Quote:
Originally Posted by CJ_London View Post
these could be in one table with a field to indicate cash/cheque/bank credit.

You could also include the membership fee due in the same table with the above new field to indicate 'fees due'
...well anything is possible but the data is generated from bank statements and pay in slip details that fly in from other sources, and I'm not going to start redesigning that element of the relational database to eliminate 1 table. But thanks for the advice.
matt beamish is offline   Reply With Quote
Old 09-10-2019, 05:03 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,238
Thanks: 40
Thanked 3,645 Times in 3,515 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Complex query - readonly recordset workaround

Quote:
I'm not going to start redesigning that element of the relational database to eliminate 1 table.
At the moment, from your description it is not normalised and it's about making your design easier to do what you want. But it's your app, I'll butt out

go with the dbGuys solution - but perhaps use the combo as I suggested before
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-10-2019, 06:46 AM   #11
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,649
Thanks: 50
Thanked 1,068 Times in 1,049 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Complex query - readonly recordset workaround

Quote:
Originally Posted by matt beamish View Post
Thanks again. Yes lets assume my form is bound to "T_Membership_details". In order to only show "PrivID" in the query result, somewhere I am going to need to give "Q_CY_Not_Paid" as the source.
Hi. I was talking about the editable form being bound to T_Membership_details. The read-only form can be bound to your complex query including Q_CY_Not_Paid. The important part is to have the matching value of PrivID in the read-only form with which you can use to open the editable form.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-10-2019, 08:19 AM   #12
matt beamish
Newly Registered User
 
Join Date: Sep 2000
Location: Leicester, UK
Posts: 181
Thanks: 18
Thanked 1 Time in 1 Post
matt beamish is on a distinguished road
Re: Complex query - readonly recordset workaround

Thanks for this. So I think I'm going to close the form and reopen on the same record. As I am closing the form that has become readonly due to the underlying recordset, I need to trap the PrivID and use that to reopen the form with. So this is what I have
Code:
Private Sub Command200_Click()
Dim PrivIDrem As String
 PrivIDrem = Me.PrivIDcntrl
' MsgBox PrivIDrem
DoCmd.Close
DoCmd.OpenForm "F_PrivateMembers", , , "PrivID = '" & PrivIDrem & "'"
End Sub
but it isn't working. PrivID is a string.
matt beamish is offline   Reply With Quote
Old 09-10-2019, 08:23 AM   #13
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,649
Thanks: 50
Thanked 1,068 Times in 1,049 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Complex query - readonly recordset workaround

Quote:
Originally Posted by matt beamish View Post
Thanks for this. So I think I'm going to close the form and reopen on the same record. As I am closing the form that has become readonly due to the underlying recordset, I need to trap the PrivID and use that to reopen the form with. So this is what I have
Code:
Private Sub Command200_Click()
Dim PrivIDrem As String
 PrivIDrem = Me.PrivIDcntrl
' MsgBox PrivIDrem
DoCmd.Close
DoCmd.OpenForm "F_PrivateMembers", , , "PrivID = '" & PrivIDrem & "'"
End Sub
but it isn't working. PrivID is a string.
Hi. What does "isn't working" mean? Are you getting an error message? If so, what does it say?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-10-2019, 08:27 AM   #14
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,163
Thanks: 15
Thanked 1,574 Times in 1,496 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Complex query - readonly recordset workaround

I would not reuse the same form for this task. I would leave the list form open and then add a dbl-click event that opens a single record form with a subform as a popup. Keep in mind that the list form will NOT show the changes made in the popup unless you requery the list form. And, if you do that, you will loose your place in the list unless the modified record no longer qualifies for selection.

PS - the reasons your queries are not updateable is NOT because they are complex, it is because they aggregate data. When a query aggregates data by summing or grouping, the ID's of the individual records are lost.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is online now   Reply With Quote
Old 09-10-2019, 08:30 AM   #15
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,163
Thanks: 15
Thanked 1,574 Times in 1,496 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Complex query - readonly recordset workaround

Your code isn't working because the openForm code is run before the active form actually closes. Do what I suggested and use two forms.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Alternative or workaround to using LIKE in query trstorer Queries 3 04-17-2017 05:13 AM
Query is read-only, need workaround help fluid Queries 12 01-19-2017 05:31 PM
Workaround SharePoint Query Limit? nickbarnes Modules & VBA 5 08-29-2011 06:02 AM
Result of subquery based on Snapshot query is not readonly Guus2005 Queries 2 04-24-2008 06:26 AM
Need Readonly query output ashishprem Modules & VBA 6 03-22-2008 08:10 AM




All times are GMT -8. The time now is 07:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World