Solved How to make a Subform (bounded to a query) field updateable

[raziel3]

I made a query and bound it to a subform but the field [AMTPD] on frmPAYABLES is not updateable. Can someone explain what I am doing wrong, please?

 

[theDBguy]

Not always; but sometimes, using an OUTER JOIN (LEFT or RIGHT) could make a query read only.

 

[plog]

To answer your question--because you have built that form on a query that is not updateable. You've used queries upon queries to make the data that feeds that form. You've got LEFT JOINs in there which can cause a query to be unupdateable as well as aggregation which does the same thing.

A simple rule is that when you want a form to interact with data (add/edit/delete) you should base it on a table. Why is that query so complex?

With that said, I question your table structure, so you might have bigger issues than the one you posted about. The main issue is you have a circular relationship--your relationships among your tables can be traversed clockwise or counter clockwise. That's incorrect.

Open the Relationship tool of the database you posted. You have INVOICE field in both Payments and Purchases which complete the loop I am talking about. Why? As it is now payments don't go to a purchase they get attributed to a supplier. That can't be correct. I don't know which the correct order your tables should be in, but the one you have now isn't correct.

You also have PAYEE in multiple places and what seems like 2 paid amount fields. You should fix these issues before proceeding to forms. In fact, after you think your tables are correct you should work on Reports to make sure you can get data out of this database like you need. Forms should be the last items to work on.

 

[raziel3]

Ok noted, but what would be the best approach? Here is what I'm trying to achieve

Purchases are made so I created a Purchases Table.
Cheques are written out, so I have a Cheques Table

Now here's the problem,
Cheques are written but may be applied to several Invoices. Additionally, Cheques can part-pay an Invoice. So I created a Payments Table for that.

Now to pull things together I want to be able to enter the cheque in main form and if needed, split the payments in the subform. Also, the Subform, once the supplier is selected will only list the outstanding invoices. This may include the balance from a part-paid invoice, that is why I created the Payables query to be the source of the subform.

I was thinking that if I could have gotten the fields AMTPD, CHQNUM and PAYDATE updateable, that will solve everything. Any suggestions how to implement this?

 

[plog]

Generally every entity you mention becomes a table. I've heard checks, invoices, payments, suppliers. Those are your 4 tables.

Cheques are written but may be applied to several Invoices. Additionally, Cheques can part-pay an Invoice. So I created a Payments Table for that.

Based on that here is what your table relationship should look like:

I have notes on the highlighted fields:

SUBTOTAL - what is this exactly? Seems like an Invoice wouldn't have a subtotal, but a total.

PAYDATE \ CHQDATE - would these ever be different? How and why? How come you don't just need one of them?

PAYEE - wouldn't this just be the SUPPLIER? would this ever be different?

And lastly, forms don't drive table structure. So let's put all form talk on hold until your tables/fields are correct.

 

[raziel3]

SUBTOTAL - The total before Taxes/VAT, I need to use this amount in another report.

PAYDATE \ CHQDATE - Yes they are the same.

PAYEE - Yes, they are the same also.

 

[plog]

So SUBTOTAL should stay. PAYDATE should go and CHQDATE should stay. PAYEE should go.

For forms in general, I represent a 1->many table relationship with a main for the 1 and a subform for the many. One Invoice can have many payments, so the main form would be a form that shows one invoice with a subform that shows every payment to that invoice.

I want to be able to enter the cheque in main form and if needed, split the payments in the subform.

That's simple. Let's change 'main form' to Main Menu. On it you would have a drop down/text box to enter an existing check number or a button for 'Add New Check'. In both cases those go to the Check Form which operates on just 1 check at a time. At the top is the number, the date, everything in the Checks table. At the bottom is a subform based on the Payments table, its a continous form that shows every payment that check goes to and allows you to enter data into Payemnts for new records.

Also, the Subform, once the supplier is selected will only list the outstanding invoices.

This is a different form. On the same Main Menu you can have a dropdown of every supplier and a button. On Click that button opens the Supplier form to the selected supplier (or even a new one if you want to add one). It deals with just one supplier at the time, its information is at the top. At the bottom is a subform based on a query that lists all the suppliers invoices and if they are outstanding or not. This subform will not be updateable. Instead, beside each invoice is a button, click on the button and it takes you to the Invoices form. This too operates on just one invoice record (or allows you to enter a new one). At the top is the Invoice data you can edit and at the bottom is another subform based on Payments which is editable. It lists all the payments made to that specific invoice.

Additionally on that main menu you can have an Invoice drop down/text input where you can jump directly to a specific Invoice and see its payments.

 

[raziel3]

In the op I've attached the db on how far I reached.

 

[raziel3]

Hope this thread didn't go dead?

 

[plog]

Are there outstanding questions?

 

[raziel3]

In the subform, how can I make the fields AMTPD, CHQNUM and PAYDATE updateable?

 

[GPGeorge]

I believe the answer was previously given by Plog, but perhaps not called out sufficiently to catch your attention.

Forms based on multi-table queries are often not updateable because such queries are not updateable. If your subform is bound to such a query, it's not likely to be updateable.

In your sample database, the one you uploaded, the actual query bound to the form form combined left joins, which are often, as theDBGuy called out, not updateable, but also based on two other queries! That design is pretty close to a guarantee not to be updateable.

Data entry works best, therefore, when forms and subforms are bound to one table, or to a query based on one table.

That's where you need to start.

 

[GPGeorge]

I believe the answer was previously given, but perhaps not called out sufficiently.

Forms based on multi-table queries are often not updateable because such queries are not updateable. If your subform is bound to such a query, it's not likely to be updateable.

In your sample database, the one you uploaded, the actual query bound to the form form combined left joins, which are often, as theDBGuy called out, not updateable, but also based on two other queries! That design is pretty close to a guarantee not to be updateable.

Data entry works best, therefore, when forms and subforms are bound to one table, or to a query based on one table.

That's where you need to start.

Oh, drilling further, one of those subqueries is actually an aggregate, which is NEVER updateable, and that floats up through the other query based on it.

Why not bind the subform to a query based ONLY on the payments table? The other fields in the layered queries add nothing to that. If you want to display the remaining balance on an invoice in the same subform, use a DLookup, or DSum to display it in an unbound control. It's not part of the actual payment itself anyway.

 

[raziel3]

Thanks. Yes, I understood what @plog and @theDBguy were saying but I wanted a way to list out the outstanding invoices after selecting the payee in the main form. Also, try to apply a payment to each record (the outstanding invoices) in the subform. Which can't be done with the current design because to get the outstanding invoices, I did an aggregate query.

So the question is, do I included another subform to handle the data entry for payments?

 

[GPGeorge]

How you handle the DISPLAY of aggregate data is a separate function from updating the table itself.

At a higher level, it might help to take into account these are really two different functions, after all. Instead of trying to merge them into a single form (show aggregated payment history and simultaneously add or update individual payments), create an interface that supports them both in their own little compartments. It's possible to put a subform, for example, showing the aggregated payments in a Footer section on the main form, and a subform for the actual data entry in the detail section on that form. But trying to mash up the two functions into a single form really disables the desired functionality for data entry and renders the whole thing problematic and overly complex.

You could use a list box, a subform, and probably other approaches to displaying the historical data. The important point is that these two things--data entry, and data reporting-- don't coexist comfortably in a single form. Well, it's probably possible to code up some sort of complex approach in VBA to do that. But each time you go down that path, you increase the fragility of the interface and the functions it is supposed to support. The old axion, "KISS" applies. Over the years, I'll bet most Access developers give up on such approaches and trust the basic designs that are proven to work.

 

[raziel3]

Yes, you are right @GPGeorge. I was trying to get everything in one form but you gave me an idea. I'll just use a list box to display all the outstanding invoices and bind Payments to the subform for data entry. Thanks.