default value

  • Thread starter Thread starter Matthew Ellis
  • Start date Start date
M

Matthew Ellis

Is it possible to have a field hold the value entered in the same field from the immediateley previous record?? Thanks
 
Matt,
If your records have a DOC (Date of Creation) field, or DOLE (Date of
Last Edit) field, you could always do a DLookup for the field value with the
greatest date value. I try to avoid Dlookups though... as they really
affect performance.
Why not try this... set the defualt value for the field each time it's
updated?
If you had a field called [Model], and you entered "Ford"... then on the
AfterUpdate...

Private Sub Model_AfterUpdate()
Model.DefaultValue = " ' " & Model& " ' "
End Sub

(Remove spaces between quotes, I opened them up for clarity)
I set this up, and it worked. Whatever the last entered value of Model
was, new records will have that as the default.

Be careful using this tecnique too much. If you have lots of fields using
this "dynamic defaulting", you could experience performance issues. Also,
in a large multi-user environment, this could get a bit hinkey...

hth
Al Camp



Is it possible to have a field hold the value entered in the same field from
the immediateley previous record?? Thanks
 
Al
Regarding your comment about DLookups affecting performance.

I have several reports that use 16 DLookups.
The DLookups get name, address, city, state etc form the CompanyData table
based on the CompanyID which is an autonumber.
I then use the DLookup fields in an IIF statement to produce the company
information on a report.

I have often thought that there should be an easier way to do this and I am
taking this opportunity to ask if there is an easier way.

If it improves performance that would be a bonus.

As always your, or anyone else, thoughts are greatly appreciated.

Mike
 
in a large multi-user environment, this could get a bit hinkey...

Would that be Frank Hinkey, the All-American from Yale, or Hinkey Haines
formerly of the New York Yankees?? <g>

Cheers!
Fred Boer
 
The DLookups get name, address, city, state etc form the CompanyData table
based on the CompanyID which is an autonumber.
I then use the DLookup fields in an IIF statement to produce the company
information on a report.

Just join the CompanyData table to your report's recordsource query,
and pull the fields from CompanyData directly. If you're only
retrieving one record from CompanyData it won't affect the rest of the
report.

John W. Vinson[MVP]
(no longer chatting for now)
 
Mike,
Sounds like you need to "link" the Company information in your query
behind the report, rather than use so many DLookups.
From the information you've provided, it's very hard to say exactly what
the problem is. Sounds like you have a report based upon a table or a
query, and one of those fields is Company ID. Then you use the CompanyID
to Dlookup a bunch of values in another table.
Those tables should be "related" via the CompanyID, so in the query
behind the report, you would "link" your primary table (the one) to the
secondary table (the many) in a one to many relationship.
Then... those secondary fields can be placed on the report just like any
primary fields... no need to DLookup.
hth
Al Camp

Mike Revis said:
Al
Regarding your comment about DLookups affecting performance.

I have several reports that use 16 DLookups.
The DLookups get name, address, city, state etc form the CompanyData table
based on the CompanyID which is an autonumber.
I then use the DLookup fields in an IIF statement to produce the company
information on a report.

I have often thought that there should be an easier way to do this and I am
taking this opportunity to ask if there is an easier way.

If it improves performance that would be a bonus.

As always your, or anyone else, thoughts are greatly appreciated.

Mike
AlCamp said:
Matt,
If your records have a DOC (Date of Creation) field, or DOLE (Date of
Last Edit) field, you could always do a DLookup for the field value with
the
greatest date value. I try to avoid Dlookups though... as they really
affect performance.
Why not try this... set the defualt value for the field each time it's
updated?
If you had a field called [Model], and you entered "Ford"... then on the
AfterUpdate...

Private Sub Model_AfterUpdate()
Model.DefaultValue = " ' " & Model& " ' "
End Sub

(Remove spaces between quotes, I opened them up for clarity)
I set this up, and it worked. Whatever the last entered value of Model
was, new records will have that as the default.

Be careful using this tecnique too much. If you have lots of fields
using
this "dynamic defaulting", you could experience performance issues. Also,
in a large multi-user environment, this could get a bit hinkey...

hth
Al Camp



Is it possible to have a field hold the value entered in the same field
from
the immediateley previous record?? Thanks
 
Fred,
Typo... I meant John Hinkley!! :-)
Al Camp

Fred Boer said:
Would that be Frank Hinkey, the All-American from Yale, or Hinkey Haines
formerly of the New York Yankees?? <g>

Cheers!
Fred Boer
 
Thanks to all.
Mike
AlCamp said:
Mike,
Sounds like you need to "link" the Company information in your query
behind the report, rather than use so many DLookups.
From the information you've provided, it's very hard to say exactly what
the problem is. Sounds like you have a report based upon a table or a
query, and one of those fields is Company ID. Then you use the CompanyID
to Dlookup a bunch of values in another table.
Those tables should be "related" via the CompanyID, so in the query
behind the report, you would "link" your primary table (the one) to the
secondary table (the many) in a one to many relationship.
Then... those secondary fields can be placed on the report just like any
primary fields... no need to DLookup.
hth
Al Camp

Mike Revis said:
Al
Regarding your comment about DLookups affecting performance.

I have several reports that use 16 DLookups.
The DLookups get name, address, city, state etc form the CompanyData
table
based on the CompanyID which is an autonumber.
I then use the DLookup fields in an IIF statement to produce the company
information on a report.

I have often thought that there should be an easier way to do this and I am
taking this opportunity to ask if there is an easier way.

If it improves performance that would be a bonus.

As always your, or anyone else, thoughts are greatly appreciated.

Mike
AlCamp said:
Matt,
If your records have a DOC (Date of Creation) field, or DOLE (Date of
Last Edit) field, you could always do a DLookup for the field value
with
the
greatest date value. I try to avoid Dlookups though... as they really
affect performance.
Why not try this... set the defualt value for the field each time
it's
updated?
If you had a field called [Model], and you entered "Ford"... then on the
AfterUpdate...

Private Sub Model_AfterUpdate()
Model.DefaultValue = " ' " & Model& " ' "
End Sub

(Remove spaces between quotes, I opened them up for clarity)
I set this up, and it worked. Whatever the last entered value of Model
was, new records will have that as the default.

Be careful using this tecnique too much. If you have lots of fields
using
this "dynamic defaulting", you could experience performance issues. Also,
in a large multi-user environment, this could get a bit hinkey...

hth
Al Camp



Is it possible to have a field hold the value entered in the same field
from
the immediateley previous record?? Thanks
 
After thinking about this for a while I finally realized the reference to
only retrieving one record.

This db can have up to 5 companies involved in one transaction in various
capacities.

I have all company information regardless of the role the company plays in
the transaction stored in one table. tblCompanyData. With CompanyID as the
PK.

All the other tables only store the CompanyID in the slots assigned to a
particular function within the transaction.

My reports can show company information for up to 5 different companies.
Seller, Buyer, Deliver To, Ship Via, Bill To.

Usually there are only 3 or 4 companies shown on a single report.

As always, your thoughts and suggestions are welcome

Mike
 
After thinking about this for a while I finally realized the reference to
only retrieving one record.

This db can have up to 5 companies involved in one transaction in various
capacities.

I have all company information regardless of the role the company plays in
the transaction stored in one table. tblCompanyData. With CompanyID as the
PK.

All the other tables only store the CompanyID in the slots assigned to a
particular function within the transaction.

My reports can show company information for up to 5 different companies.
Seller, Buyer, Deliver To, Ship Via, Bill To.

Usually there are only 3 or 4 companies shown on a single report.

You can base your report on a Query linking the Transactions table to
*FIVE DIFFERENT INSTANCES* of the company table. Add the Company table
to the query grid five times; join the first one to Seller, the second
to Buyer, and so on.

If any of these fields may be NULL, make all the joins "Left Joins" -
select the join line in the query and pick the option that says "Show
all records from Transactions and any matching records from Company".

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thank you.
I'll give it a go.

Mike

John Vinson said:
You can base your report on a Query linking the Transactions table to
*FIVE DIFFERENT INSTANCES* of the company table. Add the Company table
to the query grid five times; join the first one to Seller, the second
to Buyer, and so on.

If any of these fields may be NULL, make all the joins "Left Joins" -
select the join line in the query and pick the option that says "Show
all records from Transactions and any matching records from Company".

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top