Need to add another Field in Form already created...

[rpeare]

wherever you need to find the customer price you can link in your items table (the one that has the price list) and have a formula similar to:



iif([CustomerType] = X, ListPriceA, iif([CustomerType = Y, ListPriceB, null))

where X and Y would be the PK to your customer type table (you could have, theoretically, an unlimited number of customer types) and ListPriceA and ListPriceB would be the associated list price for each CustomerType

[Stephanie53]

I was able to link the tables. Now my question is this. Instead of adding another field IN the Form how can i get that one field to read both fields of ListPriceA and ListPrice B under the Products (not Parts as its named this way) - where do I put this code:

LineItemCost: iif(customertype = 1, ListA, ListB) * qty

[Stephanie53]

Ok this is what I have:

Private Sub Product_ID_AfterUpdate()
'Initialize price and discount for each product change
If Not IsNull(Me![Product ID]) Then
Me![Quantity] = 0
Me.Quantity.Locked = False
Me![Unit Price] = GetListPrice(Me![Product ID])
Me![Discount] = 0
Me![Status ID] = None_OrderItemStatus


I need to change where its RED to read from 2 fields under the Products which is related to the Customer.

I have it in the Relationship as stated above in 5th post.

how would I change this in VBA to have it read the accordingly to the Customers Discount of A or B? Would it still be the following:

Private Sub Product_ID_AfterUpdate()
'Initialize price and discount for each product change
If Not IsNull(Me![Product ID]) Then
Me![Quantity] = 0
Me.Quantity.Locked = False
Me![Unit Price] = GetListPrice(Me![Product ID])
Me![Discount] = iif(customertype = 1, ListA, ListB) * qty
Me![Status ID] = None_OrderItemStatus

And then change in the properties under Discount from Percentage to Currency?

TIA

Stephanie

[Stephanie53]

I have created the query as stated above in #5

Now I am not sure where to put this in:

LineItemCost: iif(customertype = 1, ListA, ListB) * qty

Do I create it under its own? or do I put it in the field that I need it done in? The Field i need it done in is called *Discount*

[rpeare]

if you want to calculate the percent discount wouldn't it be something more like:

([highercost]-[lowercost])/[highercost]

or

1-([lowercost]/[highercost])


as far as creating a field in your query, just type that in exactly has you have it and you should end up with a field called 'lineitemcost' when you look at the datasheet view of your query.

The only possible conflict you'll have is if any of those field names (ListA, ListB or Qty) appear in more than one table within the query. if they do then you'll have to specify the table the value you want is coming from like:

lineitemcost: iif([Customers]![CustomerType] = 1, [products]!
[lista], [products]!
[listb]) * [orderdetails]![qty]

That would be assuming every field in the formula appears in more than one table. you can leave off the table definition in any field name that is unique in the tables that form the query.

[Stephanie53]

In the Customer table it has a field that is called TypeID to which it has either 1 (for ListPriceA under Products) or 2 (ListPriceB under Product) (which I have it reflected in relationship with CustomerType as ) So I would use that second code then..but how would I direct it under the field in the Form called Discount to reflect the price?

[rpeare]

is your form based on a query or SQL statement or is it based directly on a table?

if it's based directly on a table you could just have an unbound field on your form that has the same formula except you'd call your FORM field names (if they differ from your TABLE field names)

if it's based on a query or SQL statement you can build the formula into the query or SQL statement and bind a field on your form to your calculated value, just make the field locked and turn off the tab stop so it's not part of your data entry.

[Stephanie53]

When trying to change in the Invoice Data query to add CustomerType and relationship to the others in the query (i.e. from order details to order to customer to customertype) I get the SQL error on another issue (see https://www.accessforums.net/forms/s...ook-34399.html) Once I removed the customertype the other issue worked fine. and this issue still stands. So please advise what to do next to get this to work for me please?

TIA

Stephanie

[rpeare]

if you can provide a sample of your database (2007 or prior please) I can take a look, I can not understand your issue based on what you've typed.

[Stephanie53]

Database51.zip

Not sure I did this right but I have Access 2010

[rpeare]

You've got to convert it, I can't read 2010 files.

Click on your windows button, hover over the save as button, then choose a database format prior to 2010., then zip and upload it.

[Stephanie53]

it only gives me one option to save to and that is Microsoft Access Database (doesnt give option for earlier versions in 2010)

[rpeare]

Did you do a search in your help for

convert 2010 to 2003?

Click FILE
Click SAVE AND PUBLISH
Click SAVE DATABASE AS

there should be options in there for prior versions.

[Stephanie53]

will not allow me to save to earlier version per the reasons stated in this popup

[rpeare]

If the problem we're trying to fix is not related to a multi value or calculated field in a TABLE delete those columns from your table (or change the data type to something other than multi-value/calculated) then try again.

Just as an aside to your problem. Multi value and calculated value fields in a TABLE are a horrible idea and you should try to avoid them at all costs.