default value.

  • Thread starter Thread starter Kenny Kepler
  • Start date Start date
K

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
 
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
 
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

:-(


Yannacopoulos said:
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 said:
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
Click to expand...
Click to expand...
 
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


Kenny Kepler said:
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

:-(


Yannacopoulos said:
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 said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
defeated
by
something that seems like it should be simple.

Thank you,

Kenny Kepler
Click to expand...
Click to expand...
Click to expand...
 
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

Nikos Yannacopoulos said:
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


Kenny Kepler said:
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

:-(


Yannacopoulos said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top