Complex query - readonly recordset workaround (1 Viewer)

matt beamish

Registered User.
Local time
Today, 19:58
Joined
Sep 21, 2000
Messages
208
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

Super Moderator
Staff member
Local time
Today, 19:58
Joined
Feb 19, 2013
Messages
16,553
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

I’m here to help
Staff member
Local time
Today, 12:58
Joined
Oct 29, 2018
Messages
21,358
Hi. Why not open an updatable form based in the id you selected from the read only form?
 

matt beamish

Registered User.
Local time
Today, 19:58
Joined
Sep 21, 2000
Messages
208
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

Registered User.
Local time
Today, 19:58
Joined
Sep 21, 2000
Messages
208
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

I’m here to help
Staff member
Local time
Today, 12:58
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 19:58
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 19:58
Joined
Sep 21, 2000
Messages
208
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

Registered User.
Local time
Today, 19:58
Joined
Sep 21, 2000
Messages
208
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

Super Moderator
Staff member
Local time
Today, 19:58
Joined
Feb 19, 2013
Messages
16,553
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

I’m here to help
Staff member
Local time
Today, 12:58
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 19:58
Joined
Sep 21, 2000
Messages
208
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

I’m here to help
Staff member
Local time
Today, 12:58
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 15:58
Joined
Feb 19, 2002
Messages
42,973
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

Super Moderator
Staff member
Local time
Today, 15:58
Joined
Feb 19, 2002
Messages
42,973
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:58
Joined
Oct 29, 2018
Messages
21,358
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.
Hi Matt. I agree with Pat. It's better if you use two separate forms like I was saying earlier.
 

matt beamish

Registered User.
Local time
Today, 19:58
Joined
Sep 21, 2000
Messages
208
Ah thanks for replies, yes I will try a separate form on a pop up. DBGuy - sorry for "it isn't working" as I appreciate that that is so useless (although Pat understood...). No there is no error, the form simply opens as it would otherwise do according to the arguments built into 'On Open'.
thanks folks.
PS Why doesn't DoCmd.Close happen before DoCmd.Open when it is told to do that?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:58
Joined
Oct 29, 2018
Messages
21,358
Ah thanks for replies, yes I will try a separate form on a pop up. DBGuy - sorry for "it isn't working" as I appreciate that that is so useless (although Pat understood...). No there is no error, the form simply opens as it would otherwise do according to the arguments built into 'On Open'.
thanks folks.
PS Why doesn't DoCmd.Close happen before DoCmd.Open when it is told to do that?
Well, Pat is one of the smartest people around here. I still have a long ways to go. Good luck and let us know how it goes.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:58
Joined
Feb 19, 2002
Messages
42,973
Well, Pat is one of the smartest people around here.
??? I've just made more mistakes than all of you put together. My curse is that I remember them but my gift is that I frequently remember the solution also.

PS Why doesn't DoCmd.Close happen before DoCmd.Open when it is told to do that?
Because the form can't close until the code module finishes executing. If it did, it would never execute the open command so you'd still be wondering why it didn't work :)
 

Users who are viewing this thread

Top Bottom