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

[Stephanie53]

hmmmm...ok looking at the table this is what I see

(the Standard Cost was not brought over in the form no will it)

Each and every item has 2 prices...that is no different then having First name and Last name fields. I know that there is a code out there that can read off of 2 fields and make the decision on which one it suppose to put down by making the relationships above and matching it to the name of the customer that has a field that says either a "1 - List Price A" or a "2 - List Price B" I am not asking it to be caculated in the TABLE as I have Queries for this if I so need it which I do for the ORDERS. I am not asking for a MultiValue either to be caculated. All I want is one Field in the form to be able to READ both Fields in this Form "Products" and be able to make the determination of which one it gets by selection of CUSTOMER. So please tell me where I am asking it to caculate? Or create a Multivalue?

Supplier IDs	ID	Product Code	Product Name	Description	Standard Cost	List Price A	List Price B	Reorder Level	Target Level	Quantity Per Unit	Discontinued	Minimum Reorder Quantity	Category	Attachments
	100	GREY PADS 15X18 HVY	DUT1MBGPB-12 - GREY PADS 15X18 HVY	ABSORBANT	$22.79	$57.00	$41.00	15	30	1	No	30	PAD	0

[Stephanie53]

that part about Multivalue and Caculations is in reference to ANOTHER Form/Table/Query and has nothing to do with this issue. That is a generalized error stating that somewhere in my DB is these things and it can not be converted to your 2003 Access.

[June7]

Personally, I wouldn't store the TypeID in Customer table, I would store the text value (A, B). I would remove the autonumber field from CustomerType table. I would probably do the same with order status.

Exactly which form needs to be modified to accommodate this multi-price structure - is it [Order Subform for Order Details]? Do you want the price saved into Unit Price field? Saving the price value will require code.

Displaying the correct price for the product depends on the customer TypeID on the main form. Need to modify [Order Details] RecordSource to include the TypeID field from Customers table in the field grid.
SELECT Orders.*, [Orders Status].[Status Name], [Customers Extended].[E-mail Address], [Customers Extended].TypeID FROM [Customers Extended] RIGHT JOIN ([Orders Status] RIGHT JOIN Orders ON [Orders Status].[Status ID] = Orders.[Status ID]) ON [Customers Extended].ID = Orders.[Customer ID];

Need code in some event to retrieve price. I see there is already code in Product combobox AfterUpdate event to retrieve price value. The event calls a function (GetListPrice) which calls another function (DLookupNumberWrapper) which calls another function (DLookupWrapper) which calls another function (DomainFunctionWrapper). I've never taken this close a look at Northwind and it's driving me crazy! That code was designed to handle only a single price. Instead of trying to understand what these functions accomplish and how to use them with the new price structure, an immediate fix would appear to be to replace the GetListPrice function call with:

Me![Unit Price] = DLookup(IIf(Forms![Order Details]!TypeID = 1, "
[List Price A]", "
[List Price B]"), "Products", "ID=" & Me.Product_ID

<Don't know why that code won't stay all on one line here in post.>

Not every customer has a TypeID code in their record so the above code is failing to return a price for some of the order detail records.

This database cannot be converted to prior version. Get warning that it uses features that require the current file format. Those features could be:
calculated fields, multi-value fields, data macros, and maybe some other stuff.

[Stephanie53]

Displaying the correct price for the product depends on the customer TypeID on the main form. Need to modify [Order Details] RecordSource to include the TypeID field from Customers table in the field grid.( where is this located at??)

I figured out the rest of where that goes...I think

Stephanie

[Stephanie53]

I did see where RecordSource is but not where i can include it with the rest

[Stephanie53]

Got it where to do that part

[Stephanie53]

June

Ok i get this error:




and this is what it looks like...



now you understand my frustration regarding this Template :-)

TIA

[June7]

So you figured out how to pull the TypeID into the form RecordSource?

I just noticed I am missing a ) at the end of the suggested DLookup code.

Parens and quote marks always have to be in pairs.

[Stephanie53]

YES!! this part now working!!! and yes i found the RecordSource and was able to put in the TypeID

Thank you :-)

Now you aint going to believe this....They are wanting some changes AGAIN to this DB!! I am about to toss this to the wolves!

[June7]

A database is never finished. Might get to a point where changes are few a far between, but always growing, always improving. Good Luck.

[Stephanie53]

Just hope your around to still help me June HAHAHAHA!

[Stephanie53]

June I had to reopen this one cuz it has to do with the same issue...



The only thing I changed through out the whole code was adding Product Code in the mix (which not sure if works cuz having this issue pop up instead so may want to help me with that part too if you can)..

Private Sub Product_ID_AfterUpdate()
    'Initialize price and discount for each product change
    If Not IsNull(Me![Product ID]) Then
    Me.Product_ID = Me![Product Code] = True
        Me![Quantity] = 0
        Me.Quantity.Locked = False
        Me!
[List Price] = DLookup(IIf(Forms![Order Details]!TypeID = 1, "
[List Price A] ", "
[List Price B]"), "Products", "ID=" & Me.Product_ID)
        Me![Status ID] = None_OrderItemStatus
        
        
    'Empty Product records mean user wants to delete line item
    Else
        eh.TryToRunCommand acCmdDeleteRecord
    End If
End Sub

Please advise to what happened with that line item Me!
[List Price] as i never even touched it but now having that issue

Thanks in advance once more... :-)

Stephanie

[Stephanie53]

Database52.zip

Oh Heck June!! please look at this DB and tell me what I did wrong in removing [Unit Cost](field) in some places (like above issue) and changing it to Standard Cost in Purchasing forms that now I am having all these errors come up!

I so want to get this DB done before July when my husband comes home from Afghanistan I can take the 2 weeks off to be with him...UGHHH!!

*now shopping for that wig I said I needed*

Thanks again for your help :-)

Stephanie

[June7]

Need specifics.

Where did you do this change - what form or report or whatever?

Why did you make the change?

[Stephanie53]

what changed is this...

the first code we corrected..

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


'Empty Product records mean user wants to delete line item
Else
eh.TryToRunCommand acCmdDeleteRecord
End If
End Sub

then we did this..
[CODE] 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!
[List Price] = DLookup(IIf(Forms![Order Details]!TypeID = 1, "
[List Price A]", "
[List Price B]"), “Products", "ID=" & Me.Product_ID
Me![Status ID] = None_OrderItemStatus[CODE]

And now I have since added a field (to which I am having problems on also and will need help on which will probably solve this issue here)

[CODE] Private Sub Product_ID_AfterUpdate()
'Initialize price and discount for each product change
If Not IsNull(Me![Product ID]) Then
Me.Product_ID = Me![Product Code] = True
Me![Quantity] = 0
Me.Quantity.Locked = False
Me!
[List Price] = DLookup(IIf(Forms![Order Details]!TypeID = 1, "
[List Price A] ", "
[List Price B]"), "Products", "ID=" & Me.Product_ID)
Me![Status ID] = None_OrderItemStatus


'Empty Product records mean user wants to delete line item
Else
eh.TryToRunCommand acCmdDeleteRecord
End If
End Sub[CODE]

and when i now put in the product i not only get this Syntax error 3075 regarding the expression 'ID=".


i dont get the product in the combo box either nor can i get the Product Code that they they want on this form to populate either and in the correct format (which this field should be next to Product combo box (see pic) So this might be the issue with the reason i am getting this syntax error


and here is the design view..