Solved Order Totals on a New Order

[GinaWhipp]

Are you sure your Subform is set for Continuous? Because it looks like it's set for Single which would explain that.

 

[debsamguru]

Yes, definitely continuous

 

[GinaWhipp]

Hmm, well that is odd. How are the Form\Subform linked?

 

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[debsamguru]

Thank you June. I've attached it here. I'm on UK time, so I won't be able to respond until tomorrow morning, my time. Thanks to all of you for your help.

 

[GinaWhipp]

On your continuous Form in the Properties Window set Data Entry to No. Having it set for Yes will always make it go to a new record allowing for Data Entry only.

Side, note: I had to remove the filter on the combo box in the subform as I kept getting errors. I also don't quite understand the purpose of an UNBOUND combo box in the continuous form.. You could easily store the ID will showing the description in the combo box. Less work that way.

 

[GinaWhipp]

Hmm, I got more questions:
1. Why is Customer ID in StockCodesT? If that's the stock\inventory then there should be no Customer ID in there.
2. DeliveryAddr1, DeliveryAddr2, DeliveryAddr3, etc is a red flag. If there is more than one Delivery Address they should be stored in a separate table, i.e. tblCustomerShipTos
3. I see Suppliers but I don't see that tied to Stock Codes. How do you know where the stock comes from?

I'm just thinking we should get these tables set up correctly to make it easier down the road.

 

[June7]

Actually, a form set for DataEntry Yes should allow multiple records to display. They would be all records entered in a single session. They display until form closes or navigate main form. That's the behavior in my db.

On this form, I scroll back up within subform and all the records just entered are displayed.

Agree with Gina. The ID should be saved, not all the related lookup info duplicated into another table. Unless there is a possibility of any changing in the future then saving preserves this history.

Should use combobox AfterUpdate event, not Change.

Along the same vein, why is Customer combobox on main form not bound?

I would simplify the RecordSource of subform and bind to OrderItemsT. I did that and bound combobox to ItemID and now the subform behaves properly.

 

[GinaWhipp]

Actually, a form set for DataEntry Yes should allow multiple records to display. They would be all records entered in a single session. They display until form closes or navigate main form. That's the behavior in my db.

This might vary depending on what version of Access but Data Entry set to Yes on a continuous subform would do exactly what I saw. What version did you test in? Just curious because I would like to try it and see what happens.

 

[June7]

I have Access 2010. It's behavior I've seen seen Access 2003. Can't imagine it would change.

Did you test OP db as I described?

 

[debsamguru]

Thank you all for your replies. Some answers for you.

1. The CustomerID is in the StockCodes table because each customer has its own set of items and can only order its own set of items.
2. Supplier will be linked to stock codes in time but I'm working on the basics at the moment.
3. There is a DeliveryAddress table which is used to populate the fields but they also want the option to set one-off delivery address details if needed. I'll try and get them to change their minds about that!
4. With regards to scrolling, I can scroll up and see the previous rows, but I wouldn't have expected to have to do that. I was expecting adding a new row to just appear under the previous row.
5. I'm using Access 2019.

I will try the things you have suggested and report back on how it goes.

Just so you know (as you may have rolled your eyes at my username having the word guru in it!!), I am an IT consultant with a large knowledge of many computer aspects and programming and my business name is The Computer Guru but I hadn't ever done anything in Access until just before Xmas. I was asked to do this project as a favour and thought that it would be an interesting project. Silly me thought that I could program anything!! Boy have I learned the hard way!! I have been learning what I can and programming what I have using YouTube videos and trial and error to get to the point that things work. That is probably the reason things aren't as you would expect it.

My next challenge is going to be programming it so that when an order is invoiced, a carry forward order is created with the unfulfilled parts of the order i.e. order 1 has item 1 with orderquantity of 100, quantityreceived of 50, so on invoicing, a second order 1a is created with item1 with orderquantity of 50.

 

[Minty]

Personally, I wouldn't create another order, provided you can match up your despatches/invoices with your orders you can calculate the outstanding order balances at any time and use those to drive the process.

Copying data around doesn't do you any favours, as it can easily get out of sync.

 

[GinaWhipp]

I have Access 2010. It's behavior I've seen seen Access 2003. Can't imagine it would change.

Did you test OP db as I described?

@June7 Yes, I did and tested in that version. As soon as I changed the Data Entry all was fine. Hmm, may have to fire up the Access 2003. I got some old databases around here somewhere.

 

[GinaWhipp]

Thank you all for your replies. Some answers for you.

1. The CustomerID is in the StockCodes table because each customer has its own set of items and can only order its own set of items.
2. Supplier will be linked to stock codes in time but I'm working on the basics at the moment.
3. There is a DeliveryAddress table which is used to populate the fields but they also want the option to set one-off delivery address details if needed. I'll try and get them to change their minds about that!
4. With regards to scrolling, I can scroll up and see the previous rows, but I wouldn't have expected to have to do that. I was expecting adding a new row to just appear under the previous row.
5. I'm using Access 2019.

I will try the things you have suggested and report back on how it goes.

Just so you know (as you may have rolled your eyes at my username having the word guru in it!!), I am an IT consultant with a large knowledge of many computer aspects and programming and my business name is The Computer Guru but I hadn't ever done anything in Access until just before Xmas. I was asked to do this project as a favour and thought that it would be an interesting project. Silly me thought that I could program anything!! Boy have I learned the hard way!! I have been learning what I can and programming what I have using YouTube videos and trial and error to get to the point that things work. That is probably the reason things aren't as you would expect it.

My next challenge is going to be programming it so that when an order is invoiced, a carry forward order is created with the unfulfilled parts of the order i.e. order 1 has item 1 with orderquantity of 100, quantityreceived of 50, so on invoicing, a second order 1a is created with item1 with orderquantity of 50.

1. Not the way to do it. You should create a junction table between the Stock and Customers to store those details. You will create a nightmare if Customer 1 wants to purchase a product used by Customer 2.
2. Don't wait till later, fix it now. You always get the foundation correct before moving to the actual build. This is the MOST important part of building an Access database. Many hours will be wasted trying to get things right if the foundation is not correct.
3. What they want is okay, what you develop is something else. In no way am I suggesting you don't give them what they want. You just need to develop it so it properly normalized.

No rolling of eyes here. We are all guru's in one thing or another and not always the thing we inquiring about.

 

[June7]

I think part of issue cause is the complicated RecordSource. It is a query based on a query.

I've never seen a customer's selection restricted like this, unusual.

 

[GinaWhipp]

@june I will agree with that. It is one of the reasons I think the tables should be adjusted.

 

[mike60smart]

Hi

The attached screenshot shows your Subform working as it should.

In the Header I bound the Customer Combobox to CustomerID, then used an Unbound textbox to obtain the CustomerID highlighted in Orange.

In the subform I bound the Item Combobox to ItemID
I also bound the Subform to a query based on the actual table.

 

[debsamguru]

Thanks all for your input. My head exploded with this, so I took a few days to screw my head back on!

In response to a few queries, I have convinced the user to use a Delivery table, so that has been changed. Customers can only purchase their own items, never any other customer's items, so a junction table isn't necessary. The SupplierT to StockCodeT relationship has been set up.

I have made the changes you suggested Mike and the items now display correctly. I calculate the TotalCostPrice and TotalSalesPrice for each OrderItemT line as QuantityRequired * CostPrice/SalesPrice accordingly and all works marvellously ... until I want to show the total for the whole order. I have used =sum(Nz([TotalCostPrice],0)) but all I get is #Error. Does the sum function not work for calculated fields?

 

[June7]

Aggregate functions must reference fields, not controls. The field can be calculated in table or query, but not form/report textbox. If calc is just in textbox then have to repeat in the aggregate calc: =Sum(Nz(expression here,0))

Is CostPrice/SalesPrice a field name? Advise not to use punctuation/special characters in naming convention.

 

[mike60smart]

Thanks all for your input. My head exploded with this, so I took a few days to screw my head back on!

In response to a few queries, I have convinced the user to use a Delivery table, so that has been changed. Customers can only purchase their own items, never any other customer's items, so a junction table isn't necessary. The SupplierT to StockCodeT relationship has been set up.

I have made the changes you suggested Mike and the items now display correctly. I calculate the TotalCostPrice and TotalSalesPrice for each OrderItemT line as QuantityRequired * CostPrice/SalesPrice accordingly and all works marvellously ... until I want to show the total for the whole order. I have used =sum(Nz([TotalCostPrice],0)) but all I get is #Error. Does the sum function not work for calculated fields?

Best not to use Calculated fields but carry out calculations in the query/record source of the Subform