M
Matthew Ellis
Is it possible to have a field hold the value entered in the same field from the immediateley previous record?? Thanks
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.
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 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
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.
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