how to lookup different price codes

T

Thomas

Hello all I am working on a database for a small Tile retail store.
I could use some help on how to lookup different price codes.
Then place the amount into the Orders Subform!UnitPrice TextBox.

I have the user select from a Options Group Check boxes
Retail, Contractor, Dealer
--------------------------------------------------------------------------
Option Group PriceCode

Control Source = PriceCodeID
In the orders Table (FieldName PriceCodeID)
--------------------------------------------------------------------------
The CheckBoxes

Retail
Option Value =1

Contractor
Option Value = 2

Dealer
Option Value =3
-----------------------------------------------------------------------
Products Table

FieldName DataType Caption Name

ProductID = AutoNumber
ProductName = Text

1 = Currency Retail
2 = Currency Contractor
3 = Currency Dealer
----------------------------------------------------------------------

Orders_SubForm.Form Code


Private Sub ProductID_AfterUpdate()

On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String
strFilter = "ProductID = " & Me!ProductID

(Look up product's unit price and assign it to UnitPrice control.)
Me!UnitPrice = DLookup("[1]", "Products", strFilter)
Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate



End Sub

------------------------------------------------------------------

I replace the number 1 with 2
I get the Contractor Price


I replace the number 1 with 3
I get the Dealer Price

Can this be accomplished with Code ?

Any Help or other methods
Ms Access 2003

Thanks in Advance

Thomas
 
A

Allen Browne

The way you have it set up, you want to change the field to look in
depending on the value of the option group. You will want to run the same
code in the AfterUpdate of the option group as well. The code below shows
how to do that.

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
Dim strField As String

'Look up product's unit price and assign to UnitPrice control.
If Not (IsNull(Me.ProductID) Or IsNull(Me.PriceCodeID)) Then
strField = "[" & Me.PriceCodeID & "]"
strFilter = "ProductID = " & Me!ProductID
Me.UnitPrice = DLookup(strField, "Products", strFilter)
End If

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate
End Sub

Private Sub PriceCodeID_AfterUpdate()
Call ProductID_AfterUpdate
End Sub

Since one product can have multiple prices, a better solution might be to
create a related table for the product prices. This ProductPrice table would
have fields like this:
ProductID Number relates to Product.ProductID
PriceCodeID Number relates to PriceCode.PriceCodeID
UnitPrice Currency how much each.
This would have several advantages, such as:
- able to cope with new price codes other than the 3 you thought of.
- able to get a price for the product even if the particular product doesn't
have the particular code.
- much more queryable.

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

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

Thomas said:
Hello all I am working on a database for a small Tile retail store.
I could use some help on how to lookup different price codes.
Then place the amount into the Orders Subform!UnitPrice TextBox.

I have the user select from a Options Group Check boxes
Retail, Contractor, Dealer
--------------------------------------------------------------------------
Option Group PriceCode

Control Source = PriceCodeID
In the orders Table (FieldName PriceCodeID)
--------------------------------------------------------------------------
The CheckBoxes

Retail
Option Value =1

Contractor
Option Value = 2

Dealer
Option Value =3
-----------------------------------------------------------------------
Products Table

FieldName DataType Caption Name

ProductID = AutoNumber
ProductName = Text

1 = Currency Retail
2 = Currency Contractor
3 = Currency Dealer
----------------------------------------------------------------------

Orders_SubForm.Form Code


Private Sub ProductID_AfterUpdate()

On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String
strFilter = "ProductID = " & Me!ProductID

(Look up product's unit price and assign it to UnitPrice control.)
Me!UnitPrice = DLookup("[1]", "Products", strFilter)
Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate



End Sub

------------------------------------------------------------------

I replace the number 1 with 2
I get the Contractor Price


I replace the number 1 with 3
I get the Dealer Price

Can this be accomplished with Code ?

Any Help or other methods
Ms Access 2003

Thanks in Advance

Thomas
Click to expand...
 
T

Thomas

Wow Allen thank you again for pointing the way.
I failed to note that the option group was on the main form.
Just as a follow-up final working code.


Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
Dim strField As String

'Look up product's unit price and assign to UnitPrice control.
If Not (IsNull(Me.ProductID) Or IsNull(Me.Parent.PriceCodeID)) Then
strField = "[" & Me.Parent.PriceCodeID & "]"
strFilter = "ProductID = " & Me!ProductID
Me.UnitPrice = DLookup(strField, "Products", strFilter)
End If

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate
End Sub


Thank You
Thomas

Allen Browne said:
The way you have it set up, you want to change the field to look in depending on the value of the
option group. You will want to run the same code in the AfterUpdate of the option group as well.
The code below shows how to do that.

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
Dim strField As String

'Look up product's unit price and assign to UnitPrice control.
If Not (IsNull(Me.ProductID) Or IsNull(Me.PriceCodeID)) Then
strField = "[" & Me.PriceCodeID & "]"
strFilter = "ProductID = " & Me!ProductID
Me.UnitPrice = DLookup(strField, "Products", strFilter)
End If

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate
End Sub

Private Sub PriceCodeID_AfterUpdate()
Call ProductID_AfterUpdate
End Sub

Since one product can have multiple prices, a better solution might be to create a related table
for the product prices. This ProductPrice table would have fields like this:
ProductID Number relates to Product.ProductID
PriceCodeID Number relates to PriceCode.PriceCodeID
UnitPrice Currency how much each.
This would have several advantages, such as:
- able to cope with new price codes other than the 3 you thought of.
- able to get a price for the product even if the particular product doesn't have the particular
code.
- much more queryable.

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

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

Thomas said:
Hello all I am working on a database for a small Tile retail store.
I could use some help on how to lookup different price codes.
Then place the amount into the Orders Subform!UnitPrice TextBox.

I have the user select from a Options Group Check boxes
Retail, Contractor, Dealer
--------------------------------------------------------------------------
Option Group PriceCode

Control Source = PriceCodeID
In the orders Table (FieldName PriceCodeID)
--------------------------------------------------------------------------
The CheckBoxes

Retail
Option Value =1

Contractor
Option Value = 2

Dealer
Option Value =3
-----------------------------------------------------------------------
Products Table

FieldName DataType Caption Name

ProductID = AutoNumber
ProductName = Text

1 = Currency Retail
2 = Currency Contractor
3 = Currency Dealer
----------------------------------------------------------------------

Orders_SubForm.Form Code


Private Sub ProductID_AfterUpdate()

On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String
strFilter = "ProductID = " & Me!ProductID

(Look up product's unit price and assign it to UnitPrice control.)
Me!UnitPrice = DLookup("[1]", "Products", strFilter)
Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate



End Sub

------------------------------------------------------------------

I replace the number 1 with 2
I get the Contractor Price


I replace the number 1 with 3
I get the Dealer Price

Can this be accomplished with Code ?

Any Help or other methods
Ms Access 2003

Thanks in Advance

Thomas
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

changing a product price and keeping hisotical data 5
Problem with update coding 2
AfterUpdate() event 1
Wierd Results from a combobox and recordsource 1
autofill in subform 0
return product with maximum price 6
Master Form/Subform problems 5
Convert code to lookup three criteria return fourth 15

Top