Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Forms (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=9)
-   -   Complex query - readonly recordset workaround (https://www.access-programmers.co.uk/forums/showthread.php?t=306776)

matt beamish 09-10-2019 03:35 AM

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

CJ_London 09-10-2019 04:04 AM

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.

theDBguy 09-10-2019 04:06 AM

Re: Complex query - readonly recordset workaround
 
Hi. Why not open an updatable form based in the id you selected from the read only form?

matt beamish 09-10-2019 04:25 AM

Re: Complex query - readonly recordset workaround
 
Quote:

Originally Posted by CJ_London (Post 1639574)
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 09-10-2019 04:28 AM

Re: Complex query - readonly recordset workaround
 
Quote:

Originally Posted by theDBguy (Post 1639575)
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

theDBguy 09-10-2019 04:34 AM

Re: Complex query - readonly recordset workaround
 
Quote:

Originally Posted by matt beamish (Post 1639579)
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.

CJ_London 09-10-2019 04:37 AM

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'

matt beamish 09-10-2019 04:40 AM

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 09-10-2019 04:42 AM

Re: Complex query - readonly recordset workaround
 
Quote:

Originally Posted by CJ_London (Post 1639581)
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.

CJ_London 09-10-2019 05:03 AM

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

theDBguy 09-10-2019 06:46 AM

Re: Complex query - readonly recordset workaround
 
Quote:

Originally Posted by matt beamish (Post 1639582)
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.

matt beamish 09-10-2019 08:19 AM

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.

theDBguy 09-10-2019 08:23 AM

Re: Complex query - readonly recordset workaround
 
Quote:

Originally Posted by matt beamish (Post 1639601)
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?

Pat Hartman 09-10-2019 08:27 AM

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.

Pat Hartman 09-10-2019 08:30 AM

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.


All times are GMT -8. The time now is 12:28 PM.

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