Form after change one product price does not hold the previous pri

[ldiaz]

hi all..

here is my question..

I have a subform where I select the product that I want to buy,,(This a
Purchase Form) the problem is here..
when I change the price in the product source and if after of the change I
open a made form the previous price dissapear.

anybody know how to keep the previous prices in all Previous Order??



I have attached all staments that the ProductField combobox contain in the
subform..

thanks..


""""ROW SOURCE""""
SELECT Products.ProductID, Products.ProductName, Products.Description,
Products.UnitPriceDLLs, Products.UnitPricePesos, Products.Active FROM
Categories INNER JOIN Products ON Categories.CategoryID=Products.CategoryID
WHERE (((Products.Active)=Yes));

"""Double Click """"
________________________________________________________________
Private Sub ProductID_DblClick(Cancel As Integer)

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim strDocName As String
Dim strLinkCriteria As String

If IsNull(Me![ProductID]) Then

MsgBox "<< Select a Supplier whereof want to see more Information >> ",
vbInformation, "Select a Supplier !!"

Me![ProductID].SetFocus

Else

strDocName = "ProductsForm"
strLinkCriteria = "[ProductName]= """ & Me![ProductID].Column(1) & """"




DoCmd.OpenForm strDocName, , , strLinkCriteria
End If





End Sub
____________________________________________________________

""AFTER UPDATE"""
Private Sub ProductID_AfterUpdate()
Description = ProductID.Column(2)
UnitPriceDLLs = ProductID.Column(3)
UnitPricePesos = ProductID.Column(4)
End Sub
____________________________________________________________

 

[Allen Browne]

You need to have the UnitPrice field in both the OrderDetail table (where
you enter the orders) and also in the Product table.

The price in the Product table is the current price. The price in the
OrderDetail table is the price for that sale, and may be different than the
current price. With this structure, you can change the price in the Product
table without affecting existing orders.

For an example of that structure, open the Northwind sample database, and
choose Relationships on the Tools menu.

 

[ldiaz]

Hi Allen..
thank you for your help..but I forgot to tell you that: when I change the
price I make the folllowing:

I uncheck the Activefield checkBox for prdouct ( this way will not appear
more in the Form detail)

to add other price I make the following:
I add a new row with the same description but with different price and I
check the Activefield,,this way I'll have the product actived with the new
price...and also I'll have a record of how many times the price has been
changed in one specified product.

could you check this and try to help me please..your help is really very
important

Thanks you..

You need to have the UnitPrice field in both the OrderDetail table (where
you enter the orders) and also in the Product table.

The price in the Product table is the current price. The price in the
OrderDetail table is the price for that sale, and may be different than the
current price. With this structure, you can change the price in the Product
table without affecting existing orders.

For an example of that structure, open the Northwind sample database, and
choose Relationships on the Tools menu.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a subform where I select the product that I want to buy,,(This a
Purchase Form) the problem is here..
when I change the price in the product source and if after of the change I
open a made form the previous price dissapear.

anybody know how to keep the previous prices in all Previous Order??



I have attached all staments that the ProductField combobox contain in
the
subform..

thanks..


""""ROW SOURCE""""
SELECT Products.ProductID, Products.ProductName, Products.Description,
Products.UnitPriceDLLs, Products.UnitPricePesos, Products.Active FROM
Categories INNER JOIN Products ON
Categories.CategoryID=Products.CategoryID
WHERE (((Products.Active)=Yes));

"""Double Click """"
________________________________________________________________
Private Sub ProductID_DblClick(Cancel As Integer)

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim strDocName As String
Dim strLinkCriteria As String

If IsNull(Me![ProductID]) Then

MsgBox "<< Select a Supplier whereof want to see more Information >> ",
vbInformation, "Select a Supplier !!"

Me![ProductID].SetFocus

Else

strDocName = "ProductsForm"
strLinkCriteria = "[ProductName]= """ & Me![ProductID].Column(1) & """"




DoCmd.OpenForm strDocName, , , strLinkCriteria
End If





End Sub
____________________________________________________________

""AFTER UPDATE"""
Private Sub ProductID_AfterUpdate()
Description = ProductID.Column(2)
UnitPriceDLLs = ProductID.Column(3)
UnitPricePesos = ProductID.Column(4)
End Sub
____________________________________________________________

 

[Allen Browne]

So, you have a similar structure to the Northwind database, with an extra
field in the Products table named Active. The current price for the product
is the one where the Active field is true.

In the Orders Subform (form in Northwind), they use the AfterUpdate event of
the ProductID combo to look up the price each, and assign it to the
UnitPrice field. You would need to modify the filter string so that it reads
the Active record from products, i.e.:


Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "(ProductID = " & Me!ProductID & ") AND (Active = True)"
....

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen..
thank you for your help..but I forgot to tell you that: when I change the
price I make the folllowing:

I uncheck the Activefield checkBox for prdouct ( this way will not appear
more in the Form detail)

to add other price I make the following:
I add a new row with the same description but with different price and I
check the Activefield,,this way I'll have the product actived with the new
price...and also I'll have a record of how many times the price has been
changed in one specified product.

could you check this and try to help me please..your help is really very
important

Thanks you..

You need to have the UnitPrice field in both the OrderDetail table (where
you enter the orders) and also in the Product table.

The price in the Product table is the current price. The price in the
OrderDetail table is the price for that sale, and may be different than
the
current price. With this structure, you can change the price in the
Product
table without affecting existing orders.

For an example of that structure, open the Northwind sample database, and
choose Relationships on the Tools menu.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a subform where I select the product that I want to buy,,(This a
Purchase Form) the problem is here..
when I change the price in the product source and if after of the
change I
open a made form the previous price dissapear.

anybody know how to keep the previous prices in all Previous Order??



I have attached all staments that the ProductField combobox contain in
the
subform..

thanks..


""""ROW SOURCE""""
SELECT Products.ProductID, Products.ProductName, Products.Description,
Products.UnitPriceDLLs, Products.UnitPricePesos, Products.Active FROM
Categories INNER JOIN Products ON
Categories.CategoryID=Products.CategoryID
WHERE (((Products.Active)=Yes));

"""Double Click """"
________________________________________________________________
Private Sub ProductID_DblClick(Cancel As Integer)

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim strDocName As String
Dim strLinkCriteria As String

If IsNull(Me![ProductID]) Then

MsgBox "<< Select a Supplier whereof want to see more Information >>
",
vbInformation, "Select a Supplier !!"

Me![ProductID].SetFocus

Else

strDocName = "ProductsForm"
strLinkCriteria = "[ProductName]= """ & Me![ProductID].Column(1) &
""""




DoCmd.OpenForm strDocName, , , strLinkCriteria
End If





End Sub
____________________________________________________________

""AFTER UPDATE"""
Private Sub ProductID_AfterUpdate()
Description = ProductID.Column(2)
UnitPriceDLLs = ProductID.Column(3)
UnitPricePesos = ProductID.Column(4)
End Sub
____________________________________________________________

 

[ldiaz]

Hi allen..

yes. it has a similary structure,,,but mine is for Purchase.

but the problem is still there..I willl try to make this work,and if I can I
will contact you.

anyway Thank you so much for your help..

ldiaz

So, you have a similar structure to the Northwind database, with an extra
field in the Products table named Active. The current price for the product
is the one where the Active field is true.

In the Orders Subform (form in Northwind), they use the AfterUpdate event of
the ProductID combo to look up the price each, and assign it to the
UnitPrice field. You would need to modify the filter string so that it reads
the Active record from products, i.e.:


Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "(ProductID = " & Me!ProductID & ") AND (Active = True)"
....

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen..
thank you for your help..but I forgot to tell you that: when I change the
price I make the folllowing:

I uncheck the Activefield checkBox for prdouct ( this way will not appear
more in the Form detail)

to add other price I make the following:
I add a new row with the same description but with different price and I
check the Activefield,,this way I'll have the product actived with the new
price...and also I'll have a record of how many times the price has been
changed in one specified product.

could you check this and try to help me please..your help is really very
important

Thanks you..

You need to have the UnitPrice field in both the OrderDetail table (where
you enter the orders) and also in the Product table.

The price in the Product table is the current price. The price in the
OrderDetail table is the price for that sale, and may be different than
the
current price. With this structure, you can change the price in the
Product
table without affecting existing orders.

For an example of that structure, open the Northwind sample database, and
choose Relationships on the Tools menu.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I have a subform where I select the product that I want to buy,,(This a
Purchase Form) the problem is here..
when I change the price in the product source and if after of the
change I
open a made form the previous price dissapear.

anybody know how to keep the previous prices in all Previous Order??



I have attached all staments that the ProductField combobox contain in
the
subform..

thanks..


""""ROW SOURCE""""
SELECT Products.ProductID, Products.ProductName, Products.Description,
Products.UnitPriceDLLs, Products.UnitPricePesos, Products.Active FROM
Categories INNER JOIN Products ON
Categories.CategoryID=Products.CategoryID
WHERE (((Products.Active)=Yes));

"""Double Click """"
________________________________________________________________
Private Sub ProductID_DblClick(Cancel As Integer)

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim strDocName As String
Dim strLinkCriteria As String

If IsNull(Me![ProductID]) Then

MsgBox "<< Select a Supplier whereof want to see more Information >>
",
vbInformation, "Select a Supplier !!"

Me![ProductID].SetFocus

Else

strDocName = "ProductsForm"
strLinkCriteria = "[ProductName]= """ & Me![ProductID].Column(1) &
""""




DoCmd.OpenForm strDocName, , , strLinkCriteria
End If





End Sub
____________________________________________________________

""AFTER UPDATE"""
Private Sub ProductID_AfterUpdate()
Description = ProductID.Column(2)
UnitPriceDLLs = ProductID.Column(3)
UnitPricePesos = ProductID.Column(4)
End Sub
____________________________________________________________