Can I use an expression for a default value?

[Terry Bickle]

Please forgive me for using the wrong term here or there. I'm an old Excel 4
macro guy who didn't convert to VB and I'm tinkering with an Access 2000 DB.
I'm sure there is a simple Access 101 answer for this.

I have a subform which the user selects an item description from a combo box
from a table. I want to obtain the cost for the item (another field in the
table) also. I've been able to produce the second data field by rewriting
the syntax in the item combo box to include the cost field and using
different syntaxes to obtain the specific cost column in the separate field
on the subform. I have the data I need. Now the problem.

When the user finishes the item entries for the subform line and moves to a
new row on the form I want the Cost recorded as a part of the permanent
transaction record, not a linked value to the item cost. (which changes if
you edit the value on the item's cost table... the cost today is X and Y
tomorrow, I need X as a permanent record)

The individual sales transaction is recorded in a table. The item #, and
user entered Units and Price are recorded to the a transactions table. A
subtotal is a linked / calculated field in the transactions table also. I
want to record the COST as a hard value (like the user entered values) in
this table. I created a field called ProductCost to post the COST data into
in this transaction table.

How do I populate ProductCost in the transactions table with COST from the
subform? I've tried unsuccessfully to set the default value of the subform
ProductCost to 'COST' and set the Control Source to ProductCost of the
transactions table. If I enter a true value like 17 as a default value
that does post to transactions. Since 'expression builder' is available for
use with 'default value' it seems like I should be able to use an expression
instead of a hard value. I'm using a text box.

I again apologize for the terminology errors and would appreciate any help.

Thanks
Terry B

[Bob Quintal]

terry, the problem you have encountered is that the default value
only gets pulled into the control when the first change is
entered (referred to as dirtying the record). Since the cost is
at that time still not defined, you'll have to use a different
event to trigger the writing of your cost. There are a few
different events you could use. The one I would use is probably
the combo box's AfterUpdate event. This event occurs when you
have selected a value from the list.

In the AfterUpdate property for the combo box, enter an
expression that writes the value from the combo box into the cost
field.

e.g. me!cost = me!cboItemDescription.column(x-1)
where cost is the name of the textbox control bound to the cost
field of your sales transaction record,, cboItemDescription is
the name of the combo box control that you pick from, and x is
the position of the column in the combo box, counting from the
left.

Bob Q

"Terry Bickle" <te****@cox.net> wrote in
news:%qjLb.30560$JD6.25308@lakeread04:

Please forgive me for using the wrong term here or there. I'm
an old Excel 4 macro guy who didn't convert to VB and I'm
tinkering with an Access 2000 DB. I'm sure there is a simple
Access 101 answer for this.

I have a subform which the user selects an item description
from a combo box from a table. I want to obtain the cost for
the item (another field in the table) also. I've been able to
produce the second data field by rewriting the syntax in the
item combo box to include the cost field and using different
syntaxes to obtain the specific cost column in the separate
field on the subform. I have the data I need. Now the problem.

When the user finishes the item entries for the subform line
and moves to a new row on the form I want the Cost recorded as
a part of the permanent transaction record, not a linked value
to the item cost. (which changes if you edit the value on the
item's cost table... the cost today is X and Y tomorrow, I
need X as a permanent record)

The individual sales transaction is recorded in a table. The
item #, and user entered Units and Price are recorded to the a
transactions table. A subtotal is a linked / calculated field
in the transactions table also. I want to record the COST as a
hard value (like the user entered values) in this table. I
created a field called ProductCost to post the COST data into
in this transaction table.

How do I populate ProductCost in the transactions table with
COST from the subform? I've tried unsuccessfully to set the
default value of the subform ProductCost to 'COST' and set the
Control Source to ProductCost of the transactions table. If I
enter a true value like 17 as a default value that does
post to transactions. Since 'expression builder' is available
for use with 'default value' it seems like I should be able to
use an expression instead of a hard value. I'm using a text
box.

I again apologize for the terminology errors and would
appreciate any help.

Thanks
Terry B

[Terry Bickle]

Bob:

Thanks again for taking the time to decipher my problem. I was able to
interpolate and apply your suggestion successfully!

Thanks again!
Terry

"Bob Quintal" <bq******@generation.net> wrote in message
news:71******************************@news.teranew s.com...

terry, the problem you have encountered is that the default value
only gets pulled into the control when the first change is
entered (referred to as dirtying the record). Since the cost is
at that time still not defined, you'll have to use a different
event to trigger the writing of your cost. There are a few
different events you could use. The one I would use is probably
the combo box's AfterUpdate event. This event occurs when you
have selected a value from the list.

In the AfterUpdate property for the combo box, enter an
expression that writes the value from the combo box into the cost
field.

e.g. me!cost = me!cboItemDescription.column(x-1)
where cost is the name of the textbox control bound to the cost
field of your sales transaction record,, cboItemDescription is
the name of the combo box control that you pick from, and x is
the position of the column in the combo box, counting from the
left.

Bob Q

"Terry Bickle" <te****@cox.net> wrote in
news:%qjLb.30560$JD6.25308@lakeread04:

Please forgive me for using the wrong term here or there. I'm
an old Excel 4 macro guy who didn't convert to VB and I'm
tinkering with an Access 2000 DB. I'm sure there is a simple
Access 101 answer for this.

I have a subform which the user selects an item description
from a combo box from a table. I want to obtain the cost for
the item (another field in the table) also. I've been able to
produce the second data field by rewriting the syntax in the
item combo box to include the cost field and using different
syntaxes to obtain the specific cost column in the separate
field on the subform. I have the data I need. Now the problem.

When the user finishes the item entries for the subform line
and moves to a new row on the form I want the Cost recorded as
a part of the permanent transaction record, not a linked value
to the item cost. (which changes if you edit the value on the
item's cost table... the cost today is X and Y tomorrow, I
need X as a permanent record)

The individual sales transaction is recorded in a table. The
item #, and user entered Units and Price are recorded to the a
transactions table. A subtotal is a linked / calculated field
in the transactions table also. I want to record the COST as a
hard value (like the user entered values) in this table. I
created a field called ProductCost to post the COST data into
in this transaction table.

How do I populate ProductCost in the transactions table with
COST from the subform? I've tried unsuccessfully to set the
default value of the subform ProductCost to 'COST' and set the
Control Source to ProductCost of the transactions table. If I
enter a true value like 17 as a default value that does
post to transactions. Since 'expression builder' is available
for use with 'default value' it seems like I should be able to
use an expression instead of a hard value. I'm using a text
box.

I again apologize for the terminology errors and would
appreciate any help.

Thanks
Terry B