Aggregate fields on editable continuous forms

[Loden]

Hello to all... I've been dabbling off and on with MS Access for a few years and am not - by far - a professional developer.

I have searched through the forums and did not find an answer to the following (basic) situation at work involving two tables in Access (2010 or 2013): Clients and Orders.

Each Client has a 1-n relationship to his orders. (no big surprise)

I need a form where the user can scroll through an editable client list which is easily done with a continuous form or subform. By Editable I mean that the user can directly edit some fields in the continuous form (like spelling errors on the client's name)

The problem is that I also need a field in the client continuous form that displays how many orders are associated to each client. Typically:

ID Client Orders
123 John Smith 50
124 Peter Jones 12
125 Mark Alder 04

Now I know I can display this data with an aggregate query....buuuuut... access tells me the query is "not updeatable" so my continuous form cannot be editable as needed.

I've also tried to play with aggregate functions to no avail....

The only workaround I found was to create a "orders" field in the client table and have VBA procedures update the count everytime an order is added or removed.... But that seems awfully complicated and messy.

I assume this must be a frequent need but haven't been able to find a satisfying answer :banghead:

Any insights greatly appreciated !!!!!!

 

[burrina]

Welcome to the forum. Not sure I completely understand. Normally you would have a subform that displays the corresponding orders for that client linked by the Primary Key.

 

[Loden]

Hi Burrina, thanks and sorry if I wasn't clear....

I am not talking about having a main "client" form with a subform with the list of orders. That's relatively trivial indeed.

The requirements I have to work with (and not around) is that I need a continuous form that shows an editable list of clients (so the names and details can be modified in this list mode) which also displays - for each client - a field with the total number of orders.

Once again this would look like :

ID Client number of Orders
123 John Smith 50
124 Peter Jones 12
125 Mark Alder 04

So that the user can scroll through the clients and see at a glance how many orders they have.

Only then - by double clicking on a specific client would we then go to the "Client main form - Orders subform" model you alluded to.

Hope this clarified things a bit?

 

[burrina]

Okay, if I understand correctly you will need a Fake Key for the Orders subform.
Example: This would give you a list of ONLY those corresponding records.

frmChangeLog subform with Primary Key of ChangeLogID 'main form
frmChangeLog sub with Link Master Fields to txtfakekey 'subform
ChangeLogID with Link Child Fields 'subform
txtfakekey=[Change Log].[Form]![ChangeLogID] ' on the form's footer.

 

[essaytee]

Hi Burrina, thanks and sorry if I wasn't clear....

I am not talking about having a main "client" form with a subform with the list of orders. That's relatively trivial indeed.

The requirements I have to work with (and not around) is that I need a continuous form that shows an editable list of clients (so the names and details can be modified in this list mode) which also displays - for each client - a field with the total number of orders.

Once again this would look like :

ID Client number of Orders
123 John Smith 50
124 Peter Jones 12
125 Mark Alder 04

So that the user can scroll through the clients and see at a glance how many orders they have.

Only then - by double clicking on a specific client would we then go to the "Client main form - Orders subform" model you alluded to.

Hope this clarified things a bit?

Here is one approach and no VBA required.

1. Create a totals query. Two fields, one the ID of the client, the other a COUNT of the orders. Order by ID, if you want.

2. Create a normal client query (one table), set that as your recordsource for the continuous form.

3. Add all the Client fields required on the form.

4. Add an Unbound Text Box, enter a DLookup expression as the controlsource. Within the Dlookup expression you will be referring to the totals query created in step 1.

5. Subject to the form settings for editing are set correctly, your client names will be editable. Obviously, the order count will not.

6. In my simple test I noticed that if there are no orders the Order count field is blank (not zero). If you require a zero there are ways to achieve it.

As I said, this is one approach but should get you up and running. If you require elaboration just ask.

 

[essaytee]

Checkout the DCount function as well (is probably more to the point).

Also, as you've probably gathered, no need to create that totals query, just use the table as is.

I think of these things after posting.

 

[Loden]

Thanks a million essaytee.... and thanks to all other contributors too !

I had fumbled with Dcount before but without success - Your post led me to the solution though.

1) "Client_List" A simple continuous form to show the client list

2) In it a field "Order_count" with the following Control Source:
=DCount("[Order_Id]","Orders","[Order]![Client_Id]= '" & [Forms]![Client_List]![Client_Id] & "'")

~ Works like a charm ~

A subsidiary question though, display of the calculated field is a bit sluggish, even on a sample table with 50 clients with between 1 and 5 orders each...[Knowing that the database is on an SSD and runs on a powerful machine. ...so the issue is not with the hardware]
=> Any suggestions to speed things up?

 

[essaytee]

Thanks a million essaytee.... and thanks to all other contributors too !

I had fumbled with Dcount before but without success - Your post led me to the solution though.

1) "Client_List" A simple continuous form to show the client list

2) In it a field "Order_count" with the following Control Source:
=DCount("[Order_Id]","Orders","[Order]![Client_Id]= '" & [Forms]![Client_List]![Client_Id] & "'")

~ Works like a charm ~

A subsidiary question though, display of the calculated field is a bit sluggish, even on a sample table with 50 clients with between 1 and 5 orders each...[Knowing that the database is on an SSD and runs on a powerful machine. ...so the issue is not with the hardware]
=> Any suggestions to speed things up?

I'll attempt to do some testing in the days/week ahead to confirm my initial thoughts.

You could 'roll your own' DCount function.

1. This involves creating a totals query, similar to what I described in a previous post (step 1).

2. At the Form module level create a Recordset based on the totals query.

3. In the Form module, create a function which returns the Order Count.

4. The function header similar to:

Function GetOrderCount(Client_ID As Long) As Long​

5. Within the function it's just a matter of accessing the Recordset, FindFirst on the Client_ID and returning the OrderCount. Remember, the Recordset is already created at the Form Module level.

6. On your form, replace the existing DCount function with "=GetOrderCount([Client_ID])"

The reason I think this might be faster is that the Recordset only needs to be executed once and we are merely accessing a particular record. I believe this is where the DCount function may be a bit sluggish on lots of data.

Look, I could be wrong, but as I said, these are my initial thoughts. At least you've got something to work with. I'm sure others will have opinions/suggestions.

 

[stopher]

Personally I would not have the form editable. The thing is it would be very easy for a user to accidentally over type a name with something else. Instead, either add a button or use the double click event on the name field to open another form that is specifically for changing personal details.

Then you can reinstate your aggregate query which is more efficient than domain functions.

 

[essaytee]

Personally I would not have the form editable. The thing is it would be very easy for a user to accidentally over type a name with something else. Instead, either add a button or use the double click event on the name field to open another form that is specifically for changing personal details.

Then you can reinstate your aggregate query which is more efficient than domain functions.

I concur. This would be my preferred approach.

 

[Loden]

I agree with you both .... and in fact my initial proposal was to have a "non editable" form.

Unfortunately the internal customer is adamant that being able to edit data "on the fly" in list mode is a mandatory requirement and not one open to discussion....

Thanks again for the help and advice !

Essaytee, I'll try to implement your suggestion and see if I can improve performance. Will report my findings (but may take a few days to do so !)

~~

 

[stopher]

You could base your form on a temporary table.

- In the OnOpen event of the form, populate the temp table with the aggregate qry
- In the AfterUpdate event of the Cleint Name on the form, write any changes of that record to the original table
- In the OnClose event of the form, delete the contents of the temp table

This should run pretty quick.

hth