473,372 Members | 1,135 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Can I use an expression for a default value?

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

Nov 12 '05 #1
2 8172
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



Nov 12 '05 #2
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


Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: Paul Rubin | last post by:
OK, I want to scan a file for lines matching a certain regexp. I'd like to use an assignment expression, like for line in file: if (g := re.match(pat, line)): croggle(g.group(1)) Since...
25
by: Steven Bethard | last post by:
So I end up writing code like this a fair bit: map = {} for key, value in sequence: map.setdefault(key, ).append(value) This code basically constructs a one-to-many mapping -- each value that...
5
by: Bradley Plett | last post by:
I'm hopeless at regular expressions (I just don't use them often enough to gain/maintain knowledge), but I need one now and am looking for help. I need to parse through a document to find a URL,...
1
by: Earthling | last post by:
I want to enter an expression into my Default Value under my field properties within the design view of my table. In English: If the previous record has the same "product ID" number then by...
18
by: Steve | last post by:
Hi I have a really weird problem and any assistance would be welcome. I have developed an app in Access 2002. The app runs perfectly on the development machine. I have packaged the app using...
8
by: Leon | last post by:
My web application allow the user to enter the site by typing in a subdomain such as 'name.domain.com'. However, I want to retrieve just the 'name' part of the subdomain. see code below (the equal...
10
by: Lyle Fairfield | last post by:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acfctNZ_HV05186465.asp "If the value of the variant argument is Null, the Nz function returns the number zero or a...
37
by: gardenfrogs | last post by:
I have fields in a table that include dates of meetings, phone calls, mailings, and other contact with clients. I have one field that is "most recent contact date". I want that default value to be...
1
by: NvrBst | last post by:
I want to use the .replace() method with the regular expression /^ %VAR % =,($|&)/. The following DOESN'T replace the "^default.aspx=,($|&)" regular expression with "":...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.