Hi and need some help

[DanielleCO]

Hello,
i have a form in which i've included a subform.
in the footer of my subform i have the total of the total price. this works.
now i also want the totals of my margin 1 calculations and my margin 2 calculations.
but when i make the same formula as for my total price than i get an error.
Chat gpt tells me i need to use vba but i can't make it work.
my fields in the header (margin 1 and margin 2) also need to be fields that can be different in each form.
for the moment, when i change them, they change in all my forms.
can someone please help me with this?
thanks!
D

 

[arnelgp]

easier if you can share a strip down version of your db to see what is going on.

 

[DanielleCO]

what do you need exactly so i can send you the right things. i'm new to access

 

[arnelgp]

your db, share it or a sample db.

 

[DanielleCO]

Here it is. i've build it to extend it aftewards

 

[DanielleCO]

i'm going crazy over this DB. so if you can help me, i would be very grateful!

 

[MajP]

my fields in the header (margin 1 and margin 2) also need to be fields that can be different in each form.

Currently you have margins specified for each sale, is this your intent that different sales have different margins?
If you want different views for potential margin than you cannot specify a specific margin in each record. If you need a view to show what the cost on a specific margin would be and then a way to record that margin then you could do that.
I do not really understand what you mean by different margins on different forms if you have specific margins for each record.
You need to describe this better.

 

[DanielleCO]

i'll prepare an excel to show you what i want
just a minute please

 

[DanielleCO]

I need 2 fields that are variable per order for the margin. and then the total per subform.

 

[DanielleCO]

i can't upload an excel
so here is the print screen

 

[DanielleCO]

 

[DanielleCO]

in my second order the 1.6 and 1.8 need to be different.

 

[DanielleCO]

indeed i need to be able to change the margin per order. so in one order it can be 1.7 and 1.2. in an other it can be 1.6 and 1.8 f.e.
my margin fields and calculations work but i can't get the sum for margin1 and margin 2. as you can see i have it for the total but i get errors when i add it for the margins.

 

[MajP]

If each item in the Order has the same margin then the Margin fields should be in the Order table and not the detail table. They would be in the detail table if each product in the order had a different margin.
If that is correct add those fields to the order table, remove from the details table and upload the new database. To make this more illustrative add several products to each order and several orders. It is hard to demonstrate a concept without any default data. Hard to show a sum works if only one record to sum.

 

[DanielleCO]

i understand. i'll have a look. i'm with my inlaws right now i'll try it in a bit. thanks so far for trying to help me out.

 

[MajP]

Before you run into a huge disaster get rid of all table lookups. You will irritate anyone in this forum who tries to give you help.

The Access Web - The Evils of Lookup Fields in Tables

You will not be able to delete the margins out of the details table because you have a hidden relationship. In the relationship window select show all relationships. Get rid of that relationship.
I do not think you need the factor table unless you have set categories
Factor A 1.7 1.2
Factor B 1.9 1.3

In other words you can only choose specific combinations of margin1 and margin 2. If that is the case and you have to pick from this list then your order table will not have 2 margins but instead a foreign key "factorID_FK" which relates the order to a set of margins in the margins table.

If you simply need to pick a value then you keep the two margin fields in the order table and pull down values not a foreign key from a table of acceptable values.
My guess is the latter with some kind of check to validate that the margin 2 is valid based on margin 1.
My guess this is a set of cascading combo boxes where you pick a value for margin 1 and then the combo for margin 2 only shows acceptable margin two values

 

[theDBguy]

Hi. Welcome to AWF!

If you could also describe your business process, perhaps it would be easier for us to understand what it is you're trying to accomplish.

 

[mike60smart]

i understand. i'll have a look. i'm with my inlaws right now i'll try it in a bit. thanks so far for trying to help me out.

Hi Danielle

Would you want to manually type in the values for Margins 1 and 2 ?

Or would you always have a table to store the various Margin Combinations?

If you are going to have a table then as MajP suggested you would need to use Cascading Combobox's.

 

[DanielleCO]

Before you run into a huge disaster get rid of all table lookups. You will irritate anyone in this forum who tries to give you help.

The Access Web - The Evils of Lookup Fields in Tables

You will not be able to delete the margins out of the details table because you have a hidden relationship. In the relationship window select show all relationships. Get rid of that relationship.
I do not think you need the factor table unless you have set categories
Factor A 1.7 1.2
Factor B 1.9 1.3

In other words you can only choose specific combinations of margin1 and margin 2. If that is the case and you have to pick from this list then your order table will not have 2 margins but instead a foreign key "factorID_FK" which relates the order to a set of margins in the margins table.

If you simply need to pick a value then you keep the two margin fields in the order table and pull down values not a foreign key from a table of acceptable values.
My guess is the latter with some kind of check to validate that the margin 2 is valid based on margin 1.
My guess this is a set of cascading combo boxes where you pick a value for margin 1 and then the combo for margin 2 only shows acceptable margin two values

 

[mike60smart]

???