default value.

[Kenny Kepler]

Hello,

I have a DB for tracking orders that I have created in Access 2003. I have
been asked to create a profile for customers that contains all their details
and the products they buy from us. We sell the same products at different
prices to our various cutomers. For example:

Product A:
$10.00 for Customer A
$9.50 for Customer B
$11.05 for Customer C

Product B:
$5.95 for Customer A
$6.95 for Customer B
$6.45 for Customer C

This pricing information is to be included in their profile. I have
achieved this by creating a separate table called price_by_cust and set two
fields as the primary key (multiple-field primary key); the cust_id and the
prod_id fields. The only other field in that table is the price field. In
the relationships window I joined the primary key of the customer table
(cust_id) and the primary key of the products table (prod_id) to the
corresponding fields in the price_by_cust table. Using those two tables I
created a query that would return all the product information and the
pricing information of products by customer and used that query for a
subform in the form created for entering customer details (address, customer
ID, etc,), and it works really well.

Now when it comes to raising an order for a cutomer I have added a subform
to the orders table for adding products. When I add products to the subform
the price should default to that customers price. This would be easy but I
have been told that the price can change, for whatever reason, but it
shouldn't change the customers "guide price" in their profile. So if I add
Product A to an order for Cutomer B but I am now selling it at $8 per unit
because they bought in bulk I should be able to amend the defaulted price on
the order without affecting the price in the prcie_by_cust table.

Basically, how do I get Access to use a vale in a field in another table to
set the default for a field in the active table based on two criteria -
cust_id and prod_id?

Please help, I have come so far with this and I don't want to be defeated by
something that seems like it should be simple.

Thank you,

Kenny Kepler

 

[Nikos Yannacopoulos]

Kenny,

If I understand your situation correctly, then you should:
* Have a field for order item price in your order details table
* Have the item price field in the order subform bound to the above field
* Default the customer / product list price in that control in the order
subform

That way when you enter a product in the order the price is defaulted in,
but if you want to change it you can do so for the current order only; the
price is stored in the order details table, while the customer list price
remains unaffected.

Now, how to default in the customer/product list price: I'll assume the
following names for my example, and you'll change to the actual names in
your design:
Main form: frmOrders
Customer ID control: txtCustID
Subform: sfrmItems
Product ID control: txtProdID
Product Price control: txtPrice
Price field in table: fldPrice

In your subform design, use the Before Update event of txtProdID to run the
following piece of code:

vCust = Forms!frmOrders!txtCustID
vProd = Forms!frmOrders!sfrmItems!txtProdID
Me.txtPrice = DLookup("[fldPrice]","price_by_cust","[cust_id]=" & cCust & "
AND [prod_id] = " & vProd)
(the DLookup expression is all in one line, watch out for wrapping in the
post)

where I have assumed that both cust_id and prod_id are numeric. If they are
text, then the expression becomes:
Me.txtPrice = DLookup("[fldPrice]","price_by_cust","[cust_id]='" & cCust &
"' AND [prod_id] = '" & vProd & "'")

HTH,
Nikos

 

[Kenny Kepler]

Hi Nikos,

Thank you for your help with this. I have used your code to create an event
procedure but something has stumped me. I have replaced the names you used
to the actual names:

frmOrders : Orders
txtCustID : Cust_ID
sfrmItems : Order_Details
txtProdID : Prod_ID
txtPrice : Price
fldPrice : Price

The code now looks like this:

Private Sub Prod_ID_BeforeUpdate(Cancel As Integer)
vCust = Forms!Orders!Cust_ID
vProd = Forms!Orders!Order_Details!Prod_ID
Me.Price = DLookup("[fldPrice]", "Prices_by_Cust", "[Cust_ID]='" & vCust &
"' AND [Prod_ID] = '" & vProd & "'")
End Sub

When I select a product in the "Order_Details" subform I get the following
error:

Microsoft Access can't find the field 'Order_Details' referred to you in
your expression.

Why does it think it is a field? I have tried to remove the Orders! from
the expression in line 3, but that doesn't work either. It then tells me
that it can't find the form Order_Details. It's ridiculous becauseI can see
that the subform's name is definately Order_Details!

Please help!!!

Thanks

:-(

Kenny,

If I understand your situation correctly, then you should:
* Have a field for order item price in your order details table
* Have the item price field in the order subform bound to the above field
* Default the customer / product list price in that control in the order
subform

That way when you enter a product in the order the price is defaulted in,
but if you want to change it you can do so for the current order only; the
price is stored in the order details table, while the customer list price
remains unaffected.

Now, how to default in the customer/product list price: I'll assume the
following names for my example, and you'll change to the actual names in
your design:
Main form: frmOrders
Customer ID control: txtCustID
Subform: sfrmItems
Product ID control: txtProdID
Product Price control: txtPrice
Price field in table: fldPrice

In your subform design, use the Before Update event of txtProdID to run
the
following piece of code:

vCust = Forms!frmOrders!txtCustID
vProd = Forms!frmOrders!sfrmItems!txtProdID
Me.txtPrice = DLookup("[fldPrice]","price_by_cust","[cust_id]=" & cCust &
"
AND [prod_id] = " & vProd)
(the DLookup expression is all in one line, watch out for wrapping in the
post)

where I have assumed that both cust_id and prod_id are numeric. If they
are
text, then the expression becomes:
Me.txtPrice = DLookup("[fldPrice]","price_by_cust","[cust_id]='" & cCust
&
"' AND [prod_id] = '" & vProd & "'")

HTH,
Nikos

Hello,

I have a DB for tracking orders that I have created in Access 2003. I have
been asked to create a profile for customers that contains all their details
and the products they buy from us. We sell the same products at
different
prices to our various cutomers. For example:

Product A:
$10.00 for Customer A
$9.50 for Customer B
$11.05 for Customer C

Product B:
$5.95 for Customer A
$6.95 for Customer B
$6.45 for Customer C

This pricing information is to be included in their profile. I have
achieved this by creating a separate table called price_by_cust and set two
fields as the primary key (multiple-field primary key); the cust_id and the
prod_id fields. The only other field in that table is the price field. In
the relationships window I joined the primary key of the customer table
(cust_id) and the primary key of the products table (prod_id) to the
corresponding fields in the price_by_cust table. Using those two tables
I
created a query that would return all the product information and the
pricing information of products by customer and used that query for a
subform in the form created for entering customer details (address, customer
ID, etc,), and it works really well.

Now when it comes to raising an order for a cutomer I have added a
subform
to the orders table for adding products. When I add products to the subform
the price should default to that customers price. This would be easy but I
have been told that the price can change, for whatever reason, but it
shouldn't change the customers "guide price" in their profile. So if I add
Product A to an order for Cutomer B but I am now selling it at $8 per
unit
because they bought in bulk I should be able to amend the defaulted price on
the order without affecting the price in the prcie_by_cust table.

Basically, how do I get Access to use a vale in a field in another table to
set the default for a field in the active table based on two criteria -
cust_id and prod_id?

Please help, I have come so far with this and I don't want to be defeated by
something that seems like it should be simple.

Thank you,

Kenny Kepler

 

[Nikos Yannacopoulos]

Kenny,

Come to think of it, it may have to do with the fact that the procedure is
in the subform itself... try this instead:
vProd = Me.Prod_ID

Also, in the DLookup, you have forgotten to change my fldPrice to the
correct Price.

HTH,
Nikos

Hi Nikos,

Thank you for your help with this. I have used your code to create an event
procedure but something has stumped me. I have replaced the names you used
to the actual names:

frmOrders : Orders
txtCustID : Cust_ID
sfrmItems : Order_Details
txtProdID : Prod_ID
txtPrice : Price
fldPrice : Price

The code now looks like this:

Private Sub Prod_ID_BeforeUpdate(Cancel As Integer)
vCust = Forms!Orders!Cust_ID
vProd = Forms!Orders!Order_Details!Prod_ID
Me.Price = DLookup("[fldPrice]", "Prices_by_Cust", "[Cust_ID]='" & vCust &
"' AND [Prod_ID] = '" & vProd & "'")
End Sub

When I select a product in the "Order_Details" subform I get the following
error:

Microsoft Access can't find the field 'Order_Details' referred to you in
your expression.

Why does it think it is a field? I have tried to remove the Orders! from
the expression in line 3, but that doesn't work either. It then tells me
that it can't find the form Order_Details. It's ridiculous becauseI can see
that the subform's name is definately Order_Details!

Please help!!!

Thanks

:-(

Kenny,

If I understand your situation correctly, then you should:
* Have a field for order item price in your order details table
* Have the item price field in the order subform bound to the above field
* Default the customer / product list price in that control in the order
subform

That way when you enter a product in the order the price is defaulted in,
but if you want to change it you can do so for the current order only; the
price is stored in the order details table, while the customer list price
remains unaffected.

Now, how to default in the customer/product list price: I'll assume the
following names for my example, and you'll change to the actual names in
your design:
Main form: frmOrders
Customer ID control: txtCustID
Subform: sfrmItems
Product ID control: txtProdID
Product Price control: txtPrice
Price field in table: fldPrice

In your subform design, use the Before Update event of txtProdID to run
the
following piece of code:

vCust = Forms!frmOrders!txtCustID
vProd = Forms!frmOrders!sfrmItems!txtProdID
Me.txtPrice = DLookup("[fldPrice]","price_by_cust","[cust_id]=" & cCust &
"
AND [prod_id] = " & vProd)
(the DLookup expression is all in one line, watch out for wrapping in the
post)

where I have assumed that both cust_id and prod_id are numeric. If they
are
text, then the expression becomes:
Me.txtPrice = DLookup("[fldPrice]","price_by_cust","[cust_id]='" & cCust
&
"' AND [prod_id] = '" & vProd & "'")

HTH,
Nikos

Hello,

I have a DB for tracking orders that I have created in Access 2003. I have
been asked to create a profile for customers that contains all their details
and the products they buy from us. We sell the same products at
different
prices to our various cutomers. For example:

Product A:
$10.00 for Customer A
$9.50 for Customer B
$11.05 for Customer C

Product B:
$5.95 for Customer A
$6.95 for Customer B
$6.45 for Customer C

This pricing information is to be included in their profile. I have
achieved this by creating a separate table called price_by_cust and set two
fields as the primary key (multiple-field primary key); the cust_id and the
prod_id fields. The only other field in that table is the price field. In
the relationships window I joined the primary key of the customer table
(cust_id) and the primary key of the products table (prod_id) to the
corresponding fields in the price_by_cust table. Using those two tables
I
created a query that would return all the product information and the
pricing information of products by customer and used that query for a
subform in the form created for entering customer details (address, customer
ID, etc,), and it works really well.

Now when it comes to raising an order for a cutomer I have added a
subform
to the orders table for adding products. When I add products to the subform
the price should default to that customers price. This would be easy

but
I

have been told that the price can change, for whatever reason, but it
shouldn't change the customers "guide price" in their profile. So if I add
Product A to an order for Cutomer B but I am now selling it at $8 per
unit
because they bought in bulk I should be able to amend the defaulted

price
on

the order without affecting the price in the prcie_by_cust table.

Basically, how do I get Access to use a vale in a field in another

table
to

set the default for a field in the active table based on two criteria -
cust_id and prod_id?

Please help, I have come so far with this and I don't want to be

defeated
by

something that seems like it should be simple.

Thank you,

Kenny Kepler

 

[Kenny Kepler]

Nikos you have a level headed genius of a mind. I was freaking out so much
I missed the most obvious thing staring me in the face!

Thanks a million! It works perfectly.

Kenny

Kenny,

Come to think of it, it may have to do with the fact that the procedure is
in the subform itself... try this instead:
vProd = Me.Prod_ID

Also, in the DLookup, you have forgotten to change my fldPrice to the
correct Price.

HTH,
Nikos

Hi Nikos,

Thank you for your help with this. I have used your code to create an event
procedure but something has stumped me. I have replaced the names you used
to the actual names:

frmOrders : Orders
txtCustID : Cust_ID
sfrmItems : Order_Details
txtProdID : Prod_ID
txtPrice : Price
fldPrice : Price

The code now looks like this:

Private Sub Prod_ID_BeforeUpdate(Cancel As Integer)
vCust = Forms!Orders!Cust_ID
vProd = Forms!Orders!Order_Details!Prod_ID
Me.Price = DLookup("[fldPrice]", "Prices_by_Cust", "[Cust_ID]='" & vCust
&
"' AND [Prod_ID] = '" & vProd & "'")
End Sub

When I select a product in the "Order_Details" subform I get the
following
error:

Microsoft Access can't find the field 'Order_Details' referred to you in
your expression.

Why does it think it is a field? I have tried to remove the Orders! from
the expression in line 3, but that doesn't work either. It then tells me
that it can't find the form Order_Details. It's ridiculous becauseI can see
that the subform's name is definately Order_Details!

Please help!!!

Thanks

:-(

Kenny,

If I understand your situation correctly, then you should:
* Have a field for order item price in your order details table
* Have the item price field in the order subform bound to the above field
* Default the customer / product list price in that control in the
order
subform

That way when you enter a product in the order the price is defaulted in,
but if you want to change it you can do so for the current order only; the
price is stored in the order details table, while the customer list price
remains unaffected.

Now, how to default in the customer/product list price: I'll assume the
following names for my example, and you'll change to the actual names
in
your design:
Main form: frmOrders
Customer ID control: txtCustID
Subform: sfrmItems
Product ID control: txtProdID
Product Price control: txtPrice
Price field in table: fldPrice

In your subform design, use the Before Update event of txtProdID to run
the
following piece of code:

vCust = Forms!frmOrders!txtCustID
vProd = Forms!frmOrders!sfrmItems!txtProdID
Me.txtPrice = DLookup("[fldPrice]","price_by_cust","[cust_id]=" &
cCust &
"
AND [prod_id] = " & vProd)
(the DLookup expression is all in one line, watch out for wrapping in the
post)

where I have assumed that both cust_id and prod_id are numeric. If they
are
text, then the expression becomes:
Me.txtPrice = DLookup("[fldPrice]","price_by_cust","[cust_id]='" & cCust
&
"' AND [prod_id] = '" & vProd & "'")

HTH,
Nikos

Hello,

I have a DB for tracking orders that I have created in Access 2003. I
have
been asked to create a profile for customers that contains all their
details
and the products they buy from us. We sell the same products at
different
prices to our various cutomers. For example:

Product A:
$10.00 for Customer A
$9.50 for Customer B
$11.05 for Customer C

Product B:
$5.95 for Customer A
$6.95 for Customer B
$6.45 for Customer C

This pricing information is to be included in their profile. I have
achieved this by creating a separate table called price_by_cust and
set
two
fields as the primary key (multiple-field primary key); the cust_id
and
the
prod_id fields. The only other field in that table is the price
field.
In
the relationships window I joined the primary key of the customer
table
(cust_id) and the primary key of the products table (prod_id) to the
corresponding fields in the price_by_cust table. Using those two tables
I
created a query that would return all the product information and the
pricing information of products by customer and used that query for a
subform in the form created for entering customer details (address,
customer
ID, etc,), and it works really well.

Now when it comes to raising an order for a cutomer I have added a
subform
to the orders table for adding products. When I add products to the
subform
the price should default to that customers price. This would be easy but
I
have been told that the price can change, for whatever reason, but it
shouldn't change the customers "guide price" in their profile. So if
I
add
Product A to an order for Cutomer B but I am now selling it at $8 per
unit
because they bought in bulk I should be able to amend the defaulted price
on
the order without affecting the price in the prcie_by_cust table.

Basically, how do I get Access to use a vale in a field in another table
to
set the default for a field in the active table based on two
criteria -
cust_id and prod_id?

Please help, I have come so far with this and I don't want to be defeated
by
something that seems like it should be simple.

Thank you,

Kenny Kepler