History table

Poorly worded question. Sorry.
I would like to see ALL of the previous transactions for a particular
building upon demand.
Ideally with one click.
For instance, maybe I could allow users to toggle between the form and
the table it is bound to?
Any suggestions on that and hiding the navigation arrows would be much
appreciated.

Jeff
DCide

BruceM wrote:
I got very busy today and did not get back to the newsgroup until I was
almost ready to leave for the day. Quick answers for now, with more detail
to follow.

1. Yes, you can hide the navigation arrows, or you can create your own. I
don't have time to go into the details today, but it is not all that
complex.

2. I may be missing something in the question, but you would use the
navigation arrows to move to previous records. However, if you have subform
data Entry property set to Yes I don't think you will see the previous
records. How have you set it up to see just the most recent record?

"Techknownothing" wrote in message
ups.com...
OK
I have the tables and forms set up per your suggestion and everything
is working fine
Thank you.
A few more questions.
1. Right now I have a main form bound to tbBUILDING and a subform bound
to tblHISTORY. Each form has its own navigation bar which I think is a
bit confusing. Can I hide the navigation bar to the subform and make it
available on demand?
2. I have the subform setup to show only the most recent entry. What is
a simple way to allow access to earlier entries ie. the tblHISTORY
data.

Jeff


BruceM wrote:
I believe there are ways of using code to write to several different
tables,
but that isn't the way to go here. First of all, make a copy of your
database. After that, make a query based on tblBuilding, containing only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer, Seller)
and
BuildingID. It will be simplest if tblHistory uses the same field names.
Make the query an Append query (Help has more information about that) to
append the data into tblHistory. With the relationship I suggested
between
tblBuilding and tblHistory, and the form/subform as I described, this new
data in tblHistory will show up in the subform. Keep the SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can confirm
with
the form/subform that the tblHistory records contain the information they
should. Once you have verified the data you can delete the fields from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones already in
tblBuilding (Bank information, maybe?) or new ones (Comments, perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo into
tblHistory. Don't worry about HistoryID. Since it is autonumber it will
be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design view, but
that the foreign key (FK) field comes into being because of its
relationship
with the PK field. You don't define the FK in the same way as you do the
PK. Also, note that the FK field is the same data type as the PK field
to
which it is related, unless the PK field is Autonumber, in which case the
FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers and
sellers,
particularly if there are institutional buyers/sellers (real estate
investment companies, for instance). It may look something like this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID fields) as
described previously. For the buyer and seller fields in tblHistory,
create
combo boxes on the subform. You will store BuyerSellerID, but display
the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as needed.
The
main thing for now is to get tblBuilding and tblHistory in order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building data
AND transaction data to tblBUILDING, do I have to separate the data or
can I have the main form send some fields to tblBUILDING and other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as tblTransaction.
Design
the two tables with the one-to-many relationship I originally
suggested
(between the two BuildingID fields). Now build the form/subform as I
suggested. You may look at the latest transaction, or the history of
all
transactions. There is no need to move the Transaction history data
to
another table. Instead you design the form so that only the current
transaction is shown.

Try building it as I suggested. You will have all of the transactions
at
hand, but we can take care of how the information is displayed. I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in my
main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a
uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to the new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the old
data
to automatically be copied to tblHISTORY. tblHISTORY and tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old data
and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory as its
record
source, updating tblHistory is best done through the subform. I
don't
know
what you meant about tblHistory automatically updating when you
update
its
record using the main form, since you wouldn't update a record in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In the
case
of
tblBuilding, that information may include address, number of rooms,
date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the
History?
Are
you planning to move data from one table to another to make room
for
new
data?
If you want you can show just the first record (the most recent
transaction)
in a subform, then click a button to show the rest of the history.
Or
you
can have the subform open to the most recent History item. Or
maybe
you
could make two subforms bound to tblHistory: one for the most
recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I
update
its record using the main FORM. My goal is to have a HISTORY
table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the
HISTORY
table will have all of the prior transactions. My guess is that
certain
fields would have to be copied to the HISTORY table upon an
update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields. Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of
fsubHistory
to
Continuous so that you can see several history items at once.
With
frmBuilding open in design view, drag the icon for fsubHistory
onto
it.
This creates a form/subform based on the related tables. Add
building
information via the main form, and history information via the
subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a
table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY
table
when
the FORM is updated?

Jeff

Part of the answer depends on what you mean by "see". If you set the
Default View on the subform's property sheet to Continuous you will see all
of the history records, although you may need to scroll to do so. Toggling
betweent he form and the table is not a good choice. Users should not be
allowed into the tables. Datasheet view will give you something of the look
of a table, although your formatting options are limited. Toggling between
different views would be difficult. You would need to open the report in
design view, change the Default View, close the form, and open it again.
The user would be prompted to save changes, I think. You may be able to
toggle between two different subforms, but any code that references the
subform would also need to be changed. If you wish to see a report, you can
save your form as a report (rptBuilding), then adjust the formatting as you
would like. A command button on the form (to open the report) would have
something like this as its Click event:

Dim strCriteria As String

strCriteria = "[BuildingID]=" & Me.BuildingID
DoCmd.OpenReport "rptBuilding", acPreview, , strCriteria

For navigation buttons, one option is he
http://www.lebans.com/recnavbuttons.htm

Another version is to create cmdFirst, cmdPrev, cmdNext, cmdLast, and cmdNew
command buttons, and an unbound text box (txtCounter). In the Click event
for the New Record command button:

DoCmd.GoToRecord , , acNewRec

Use acNext, acPrev, acFirst, acLast for the other command buttons.

In the form's current event:

' Inserts current record number and total number of records
Dim strCurrent as String, strTotal as String

strCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
strTotal = Me.RecordsetClone.RecordCount
Me.txtCounter = strCurrent & " of " & strTotal

' Enables/disables command buttons as needed
cmdFirst.Enabled = Not Me.CurrentRecord = 1
cmdPrev.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount
1) _
Or Me.CurrentRecord Me.Recordset.RecordCount
cmdLast.Enabled = Me.cmdNext.Enabled = True


As an alternative to the custom navigation buttons, you could do something
like this in the form's Current event:

' Hide the navigation buttons if there is just one record:
If strTotal = 1 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

However, you would need a way of creating a new record. Tabbing through the
controls is one such way, but not very user-friendly.

As you can see, there are all sorts of approaches. If you need something
more specific, you need to be *very* specific about just what you need. For
instance, you can see all of the history records for a building at any time
by navigating through the records or by diplaying the result as a report.
If you need something other than that, I cannot see what it is.

"Techknownothing" wrote in message
oups.com...
Poorly worded question. Sorry.
I would like to see ALL of the previous transactions for a particular
building upon demand.
Ideally with one click.
For instance, maybe I could allow users to toggle between the form and
the table it is bound to?
Any suggestions on that and hiding the navigation arrows would be much
appreciated.

Jeff
DCide

BruceM wrote:
I got very busy today and did not get back to the newsgroup until I was
almost ready to leave for the day. Quick answers for now, with more
detail
to follow.

1. Yes, you can hide the navigation arrows, or you can create your own.
I
don't have time to go into the details today, but it is not all that
complex.

2. I may be missing something in the question, but you would use the
navigation arrows to move to previous records. However, if you have
subform
data Entry property set to Yes I don't think you will see the previous
records. How have you set it up to see just the most recent record?

"Techknownothing" wrote in message
ups.com...
OK
I have the tables and forms set up per your suggestion and everything
is working fine
Thank you.
A few more questions.
1. Right now I have a main form bound to tbBUILDING and a subform bound
to tblHISTORY. Each form has its own navigation bar which I think is a
bit confusing. Can I hide the navigation bar to the subform and make it
available on demand?
2. I have the subform setup to show only the most recent entry. What is
a simple way to allow access to earlier entries ie. the tblHISTORY
data.

Jeff


BruceM wrote:
I believe there are ways of using code to write to several different
tables,
but that isn't the way to go here. First of all, make a copy of your
database. After that, make a query based on tblBuilding, containing
only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer, Seller)
and
BuildingID. It will be simplest if tblHistory uses the same field
names.
Make the query an Append query (Help has more information about that)
to
append the data into tblHistory. With the relationship I suggested
between
tblBuilding and tblHistory, and the form/subform as I described, this
new
data in tblHistory will show up in the subform. Keep the SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can confirm
with
the form/subform that the tblHistory records contain the information
they
should. Once you have verified the data you can delete the fields
from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones already
in
tblBuilding (Bank information, maybe?) or new ones (Comments,
perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo into
tblHistory. Don't worry about HistoryID. Since it is autonumber it
will
be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design view,
but
that the foreign key (FK) field comes into being because of its
relationship
with the PK field. You don't define the FK in the same way as you do
the
PK. Also, note that the FK field is the same data type as the PK
field
to
which it is related, unless the PK field is Autonumber, in which case
the
FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers and
sellers,
particularly if there are institutional buyers/sellers (real estate
investment companies, for instance). It may look something like this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many
relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID fields)
as
described previously. For the buyer and seller fields in tblHistory,
create
combo boxes on the subform. You will store BuyerSellerID, but display
the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as needed.
The
main thing for now is to get tblBuilding and tblHistory in order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building data
AND transaction data to tblBUILDING, do I have to separate the data
or
can I have the main form send some fields to tblBUILDING and other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as tblTransaction.
Design
the two tables with the one-to-many relationship I originally
suggested
(between the two BuildingID fields). Now build the form/subform as
I
suggested. You may look at the latest transaction, or the history
of
all
transactions. There is no need to move the Transaction history
data
to
another table. Instead you design the form so that only the
current
transaction is shown.

Try building it as I suggested. You will have all of the
transactions
at
hand, but we can take care of how the information is displayed. I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in my
main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a
uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to the
new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the old
data
to automatically be copied to tblHISTORY. tblHISTORY and
tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old
data
and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory as
its
record
source, updating tblHistory is best done through the subform. I
don't
know
what you meant about tblHistory automatically updating when you
update
its
record using the main form, since you wouldn't update a record
in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In
the
case
of
tblBuilding, that information may include address, number of
rooms,
date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the
History?
Are
you planning to move data from one table to another to make room
for
new
data?
If you want you can show just the first record (the most recent
transaction)
in a subform, then click a button to show the rest of the
history.
Or
you
can have the subform open to the most recent History item. Or
maybe
you
could make two subforms bound to tblHistory: one for the most
recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I
update
its record using the main FORM. My goal is to have a HISTORY
table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the
HISTORY
table will have all of the prior transactions. My guess is
that
certain
fields would have to be copied to the HISTORY table upon an
update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields.
Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of
fsubHistory
to
Continuous so that you can see several history items at once.
With
frmBuilding open in design view, drag the icon for
fsubHistory
onto
it.
This creates a form/subform based on the related tables. Add
building
information via the main form, and history information via
the
subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a
table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that
each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY
table
when
the FORM is updated?

Jeff

OK
A lot to chew on but I'm pretty sure I get it.

One (hopefully) last questions on this topic.
As I receive new data:

1. I search for the record in the main form by its address and if I
find it in the database, I update it.
2. If I don't find it, I create a new record.

I would like to remove or automate the first search step. For instance,
I type in the address and an autofill function populates the field with
the matching address (if one exists) and takes me to that record. Can
this be done in a relatively simple fashion?

Jeff

BruceM wrote:
Part of the answer depends on what you mean by "see". If you set the
Default View on the subform's property sheet to Continuous you will see all
of the history records, although you may need to scroll to do so. Toggling
betweent he form and the table is not a good choice. Users should not be
allowed into the tables. Datasheet view will give you something of the look
of a table, although your formatting options are limited. Toggling between
different views would be difficult. You would need to open the report in
design view, change the Default View, close the form, and open it again.
The user would be prompted to save changes, I think. You may be able to
toggle between two different subforms, but any code that references the
subform would also need to be changed. If you wish to see a report, you can
save your form as a report (rptBuilding), then adjust the formatting as you
would like. A command button on the form (to open the report) would have
something like this as its Click event:

Dim strCriteria As String

strCriteria = "[BuildingID]=" & Me.BuildingID
DoCmd.OpenReport "rptBuilding", acPreview, , strCriteria

For navigation buttons, one option is he
http://www.lebans.com/recnavbuttons.htm

Another version is to create cmdFirst, cmdPrev, cmdNext, cmdLast, and cmdNew
command buttons, and an unbound text box (txtCounter). In the Click event
for the New Record command button:

DoCmd.GoToRecord , , acNewRec

Use acNext, acPrev, acFirst, acLast for the other command buttons.

In the form's current event:

' Inserts current record number and total number of records
Dim strCurrent as String, strTotal as String

strCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
strTotal = Me.RecordsetClone.RecordCount
Me.txtCounter = strCurrent & " of " & strTotal

' Enables/disables command buttons as needed
cmdFirst.Enabled = Not Me.CurrentRecord = 1
cmdPrev.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount
1) _
Or Me.CurrentRecord Me.Recordset.RecordCount
cmdLast.Enabled = Me.cmdNext.Enabled = True


As an alternative to the custom navigation buttons, you could do something
like this in the form's Current event:

' Hide the navigation buttons if there is just one record:
If strTotal = 1 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

However, you would need a way of creating a new record. Tabbing through the
controls is one such way, but not very user-friendly.

As you can see, there are all sorts of approaches. If you need something
more specific, you need to be *very* specific about just what you need. For
instance, you can see all of the history records for a building at any time
by navigating through the records or by diplaying the result as a report.
If you need something other than that, I cannot see what it is.

"Techknownothing" wrote in message
oups.com...
Poorly worded question. Sorry.
I would like to see ALL of the previous transactions for a particular
building upon demand.
Ideally with one click.
For instance, maybe I could allow users to toggle between the form and
the table it is bound to?
Any suggestions on that and hiding the navigation arrows would be much
appreciated.

Jeff
DCide

BruceM wrote:
I got very busy today and did not get back to the newsgroup until I was
almost ready to leave for the day. Quick answers for now, with more
detail
to follow.

1. Yes, you can hide the navigation arrows, or you can create your own.
I
don't have time to go into the details today, but it is not all that
complex.

2. I may be missing something in the question, but you would use the
navigation arrows to move to previous records. However, if you have
subform
data Entry property set to Yes I don't think you will see the previous
records. How have you set it up to see just the most recent record?

"Techknownothing" wrote in message
ups.com...
OK
I have the tables and forms set up per your suggestion and everything
is working fine
Thank you.
A few more questions.
1. Right now I have a main form bound to tbBUILDING and a subform bound
to tblHISTORY. Each form has its own navigation bar which I think is a
bit confusing. Can I hide the navigation bar to the subform and make it
available on demand?
2. I have the subform setup to show only the most recent entry. What is
a simple way to allow access to earlier entries ie. the tblHISTORY
data.

Jeff


BruceM wrote:
I believe there are ways of using code to write to several different
tables,
but that isn't the way to go here. First of all, make a copy of your
database. After that, make a query based on tblBuilding, containing
only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer, Seller)
and
BuildingID. It will be simplest if tblHistory uses the same field
names.
Make the query an Append query (Help has more information about that)
to
append the data into tblHistory. With the relationship I suggested
between
tblBuilding and tblHistory, and the form/subform as I described, this
new
data in tblHistory will show up in the subform. Keep the SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can confirm
with
the form/subform that the tblHistory records contain the information
they
should. Once you have verified the data you can delete the fields
from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones already
in
tblBuilding (Bank information, maybe?) or new ones (Comments,
perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo into
tblHistory. Don't worry about HistoryID. Since it is autonumber it
will
be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design view,
but
that the foreign key (FK) field comes into being because of its
relationship
with the PK field. You don't define the FK in the same way as you do
the
PK. Also, note that the FK field is the same data type as the PK
field
to
which it is related, unless the PK field is Autonumber, in which case
the
FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers and
sellers,
particularly if there are institutional buyers/sellers (real estate
investment companies, for instance). It may look something like this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many
relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID fields)
as
described previously. For the buyer and seller fields in tblHistory,
create
combo boxes on the subform. You will store BuyerSellerID, but display
the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as needed.
The
main thing for now is to get tblBuilding and tblHistory in order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building data
AND transaction data to tblBUILDING, do I have to separate the data
or
can I have the main form send some fields to tblBUILDING and other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as tblTransaction.
Design
the two tables with the one-to-many relationship I originally
suggested
(between the two BuildingID fields). Now build the form/subform as
I
suggested. You may look at the latest transaction, or the history
of
all
transactions. There is no need to move the Transaction history
data
to
another table. Instead you design the form so that only the
current
transaction is shown.

Try building it as I suggested. You will have all of the
transactions
at
hand, but we can take care of how the information is displayed. I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in my
main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a
uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to the
new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the old
data
to automatically be copied to tblHISTORY. tblHISTORY and
tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old
data
and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory as
its
record
source, updating tblHistory is best done through the subform. I
don't
know
what you meant about tblHistory automatically updating when you
update
its
record using the main form, since you wouldn't update a record
in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In
the
case
of
tblBuilding, that information may include address, number of
rooms,
date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the
History?
Are
you planning to move data from one table to another to make room
for
new
data?
If you want you can show just the first record (the most recent
transaction)
in a subform, then click a button to show the rest of the
history.
Or
you
can have the subform open to the most recent History item. Or
maybe
you
could make two subforms bound to tblHistory: one for the most
recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when I
update
its record using the main FORM. My goal is to have a HISTORY
table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and the
HISTORY
table will have all of the prior transactions. My guess is
that
certain
fields would have to be copied to the HISTORY table upon an
update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields.
Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and another
(fsubHistory)
based on tblHistory. You may want to set the default view of
fsubHistory
to
Continuous so that you can see several history items at once.
With
frmBuilding open in design view, drag the icon for
fsubHistory
onto
it.
This creates a form/subform based on the related tables. Add
building
information via the main form, and history information via
the
subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called BUILDINGS.
I am tracking the sale history of each building so I made a
table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so that
each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING HISTORY
table
when
the FORM is updated?

Jeff

A combo box will take care of that task. Use the wizard, and choose the
option that starts "Find a record on my form...".

If you build a query based on tblBuilding, and sort by Address (or
whatever), it will help you to find records.

"Techknownothing" wrote in message
ups.com...
OK
A lot to chew on but I'm pretty sure I get it.

One (hopefully) last questions on this topic.
As I receive new data:

1. I search for the record in the main form by its address and if I
find it in the database, I update it.
2. If I don't find it, I create a new record.

I would like to remove or automate the first search step. For instance,
I type in the address and an autofill function populates the field with
the matching address (if one exists) and takes me to that record. Can
this be done in a relatively simple fashion?

Jeff

BruceM wrote:
Part of the answer depends on what you mean by "see". If you set the
Default View on the subform's property sheet to Continuous you will see
all
of the history records, although you may need to scroll to do so.
Toggling
betweent he form and the table is not a good choice. Users should not be
allowed into the tables. Datasheet view will give you something of the
look
of a table, although your formatting options are limited. Toggling
between
different views would be difficult. You would need to open the report in
design view, change the Default View, close the form, and open it again.
The user would be prompted to save changes, I think. You may be able to
toggle between two different subforms, but any code that references the
subform would also need to be changed. If you wish to see a report, you
can
save your form as a report (rptBuilding), then adjust the formatting as
you
would like. A command button on the form (to open the report) would have
something like this as its Click event:

Dim strCriteria As String

strCriteria = "[BuildingID]=" & Me.BuildingID
DoCmd.OpenReport "rptBuilding", acPreview, , strCriteria

For navigation buttons, one option is he
http://www.lebans.com/recnavbuttons.htm

Another version is to create cmdFirst, cmdPrev, cmdNext, cmdLast, and
cmdNew
command buttons, and an unbound text box (txtCounter). In the Click
event
for the New Record command button:

DoCmd.GoToRecord , , acNewRec

Use acNext, acPrev, acFirst, acLast for the other command buttons.

In the form's current event:

' Inserts current record number and total number of records
Dim strCurrent as String, strTotal as String

strCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
strTotal = Me.RecordsetClone.RecordCount
Me.txtCounter = strCurrent & " of " & strTotal

' Enables/disables command buttons as needed
cmdFirst.Enabled = Not Me.CurrentRecord = 1
cmdPrev.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount

1) _
Or Me.CurrentRecord Me.Recordset.RecordCount
cmdLast.Enabled = Me.cmdNext.Enabled = True


As an alternative to the custom navigation buttons, you could do
something
like this in the form's Current event:

' Hide the navigation buttons if there is just one record:
If strTotal = 1 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

However, you would need a way of creating a new record. Tabbing through
the
controls is one such way, but not very user-friendly.

As you can see, there are all sorts of approaches. If you need something
more specific, you need to be *very* specific about just what you need.
For
instance, you can see all of the history records for a building at any
time
by navigating through the records or by diplaying the result as a report.
If you need something other than that, I cannot see what it is.

"Techknownothing" wrote in message
oups.com...
Poorly worded question. Sorry.
I would like to see ALL of the previous transactions for a particular
building upon demand.
Ideally with one click.
For instance, maybe I could allow users to toggle between the form and
the table it is bound to?
Any suggestions on that and hiding the navigation arrows would be much
appreciated.

Jeff
DCide

BruceM wrote:
I got very busy today and did not get back to the newsgroup until I
was
almost ready to leave for the day. Quick answers for now, with more
detail
to follow.

1. Yes, you can hide the navigation arrows, or you can create your
own.
I
don't have time to go into the details today, but it is not all that
complex.

2. I may be missing something in the question, but you would use the
navigation arrows to move to previous records. However, if you have
subform
data Entry property set to Yes I don't think you will see the previous
records. How have you set it up to see just the most recent record?

"Techknownothing" wrote in message
ups.com...
OK
I have the tables and forms set up per your suggestion and
everything
is working fine
Thank you.
A few more questions.
1. Right now I have a main form bound to tbBUILDING and a subform
bound
to tblHISTORY. Each form has its own navigation bar which I think is
a
bit confusing. Can I hide the navigation bar to the subform and make
it
available on demand?
2. I have the subform setup to show only the most recent entry. What
is
a simple way to allow access to earlier entries ie. the tblHISTORY
data.

Jeff


BruceM wrote:
I believe there are ways of using code to write to several
different
tables,
but that isn't the way to go here. First of all, make a copy of
your
database. After that, make a query based on tblBuilding,
containing
only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer,
Seller)
and
BuildingID. It will be simplest if tblHistory uses the same field
names.
Make the query an Append query (Help has more information about
that)
to
append the data into tblHistory. With the relationship I suggested
between
tblBuilding and tblHistory, and the form/subform as I described,
this
new
data in tblHistory will show up in the subform. Keep the SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can
confirm
with
the form/subform that the tblHistory records contain the
information
they
should. Once you have verified the data you can delete the fields
from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones
already
in
tblBuilding (Bank information, maybe?) or new ones (Comments,
perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo
into
tblHistory. Don't worry about HistoryID. Since it is autonumber
it
will
be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design
view,
but
that the foreign key (FK) field comes into being because of its
relationship
with the PK field. You don't define the FK in the same way as you
do
the
PK. Also, note that the FK field is the same data type as the PK
field
to
which it is related, unless the PK field is Autonumber, in which
case
the
FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers and
sellers,
particularly if there are institutional buyers/sellers (real estate
investment companies, for instance). It may look something like
this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many
relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID
fields)
as
described previously. For the buyer and seller fields in
tblHistory,
create
combo boxes on the subform. You will store BuyerSellerID, but
display
the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as
needed.
The
main thing for now is to get tblBuilding and tblHistory in order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building
data
AND transaction data to tblBUILDING, do I have to separate the
data
or
can I have the main form send some fields to tblBUILDING and
other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as
tblTransaction.
Design
the two tables with the one-to-many relationship I originally
suggested
(between the two BuildingID fields). Now build the form/subform
as
I
suggested. You may look at the latest transaction, or the
history
of
all
transactions. There is no need to move the Transaction history
data
to
another table. Instead you design the form so that only the
current
transaction is shown.

Try building it as I suggested. You will have all of the
transactions
at
hand, but we can take care of how the information is displayed.
I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in
my
main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a
uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to
the
new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the
old
data
to automatically be copied to tblHISTORY. tblHISTORY and
tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old
data
and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory
as
its
record
source, updating tblHistory is best done through the subform.
I
don't
know
what you meant about tblHistory automatically updating when
you
update
its
record using the main form, since you wouldn't update a
record
in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In
the
case
of
tblBuilding, that information may include address, number of
rooms,
date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the
History?
Are
you planning to move data from one table to another to make
room
for
new
data?
If you want you can show just the first record (the most
recent
transaction)
in a subform, then click a button to show the rest of the
history.
Or
you
can have the subform open to the most recent History item.
Or
maybe
you
could make two subforms bound to tblHistory: one for the most
recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when
I
update
its record using the main FORM. My goal is to have a
HISTORY
table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and
the
HISTORY
table will have all of the prior transactions. My guess is
that
certain
fields would have to be copied to the HISTORY table upon an
update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields.
Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and
another
(fsubHistory)
based on tblHistory. You may want to set the default view
of
fsubHistory
to
Continuous so that you can see several history items at
once.
With
frmBuilding open in design view, drag the icon for
fsubHistory
onto
it.
This creates a form/subform based on the related tables.
Add
building
information via the main form, and history information via
the
subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called
BUILDINGS.
I am tracking the sale history of each building so I
made a
table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so
that
each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING
HISTORY
table
when
the FORM is updated?

Jeff

THANK YOU!

Jeff

BruceM wrote:
A combo box will take care of that task. Use the wizard, and choose the
option that starts "Find a record on my form...".

If you build a query based on tblBuilding, and sort by Address (or
whatever), it will help you to find records.

"Techknownothing" wrote in message
ups.com...
OK
A lot to chew on but I'm pretty sure I get it.

One (hopefully) last questions on this topic.
As I receive new data:

1. I search for the record in the main form by its address and if I
find it in the database, I update it.
2. If I don't find it, I create a new record.

I would like to remove or automate the first search step. For instance,
I type in the address and an autofill function populates the field with
the matching address (if one exists) and takes me to that record. Can
this be done in a relatively simple fashion?

Jeff

BruceM wrote:
Part of the answer depends on what you mean by "see". If you set the
Default View on the subform's property sheet to Continuous you will see
all
of the history records, although you may need to scroll to do so.
Toggling
betweent he form and the table is not a good choice. Users should not be
allowed into the tables. Datasheet view will give you something of the
look
of a table, although your formatting options are limited. Toggling
between
different views would be difficult. You would need to open the report in
design view, change the Default View, close the form, and open it again.
The user would be prompted to save changes, I think. You may be able to
toggle between two different subforms, but any code that references the
subform would also need to be changed. If you wish to see a report, you
can
save your form as a report (rptBuilding), then adjust the formatting as
you
would like. A command button on the form (to open the report) would have
something like this as its Click event:

Dim strCriteria As String

strCriteria = "[BuildingID]=" & Me.BuildingID
DoCmd.OpenReport "rptBuilding", acPreview, , strCriteria

For navigation buttons, one option is he
http://www.lebans.com/recnavbuttons.htm

Another version is to create cmdFirst, cmdPrev, cmdNext, cmdLast, and
cmdNew
command buttons, and an unbound text box (txtCounter). In the Click
event
for the New Record command button:

DoCmd.GoToRecord , , acNewRec

Use acNext, acPrev, acFirst, acLast for the other command buttons.

In the form's current event:

' Inserts current record number and total number of records
Dim strCurrent as String, strTotal as String

strCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
strTotal = Me.RecordsetClone.RecordCount
Me.txtCounter = strCurrent & " of " & strTotal

' Enables/disables command buttons as needed
cmdFirst.Enabled = Not Me.CurrentRecord = 1
cmdPrev.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount

1) _
Or Me.CurrentRecord Me.Recordset.RecordCount
cmdLast.Enabled = Me.cmdNext.Enabled = True


As an alternative to the custom navigation buttons, you could do
something
like this in the form's Current event:

' Hide the navigation buttons if there is just one record:
If strTotal = 1 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

However, you would need a way of creating a new record. Tabbing through
the
controls is one such way, but not very user-friendly.

As you can see, there are all sorts of approaches. If you need something
more specific, you need to be *very* specific about just what you need.
For
instance, you can see all of the history records for a building at any
time
by navigating through the records or by diplaying the result as a report.
If you need something other than that, I cannot see what it is.

"Techknownothing" wrote in message
oups.com...
Poorly worded question. Sorry.
I would like to see ALL of the previous transactions for a particular
building upon demand.
Ideally with one click.
For instance, maybe I could allow users to toggle between the form and
the table it is bound to?
Any suggestions on that and hiding the navigation arrows would be much
appreciated.

Jeff
DCide

BruceM wrote:
I got very busy today and did not get back to the newsgroup until I
was
almost ready to leave for the day. Quick answers for now, with more
detail
to follow.

1. Yes, you can hide the navigation arrows, or you can create your
own.
I
don't have time to go into the details today, but it is not all that
complex.

2. I may be missing something in the question, but you would use the
navigation arrows to move to previous records. However, if you have
subform
data Entry property set to Yes I don't think you will see the previous
records. How have you set it up to see just the most recent record?

"Techknownothing" wrote in message
ups.com...
OK
I have the tables and forms set up per your suggestion and
everything
is working fine
Thank you.
A few more questions.
1. Right now I have a main form bound to tbBUILDING and a subform
bound
to tblHISTORY. Each form has its own navigation bar which I think is
a
bit confusing. Can I hide the navigation bar to the subform and make
it
available on demand?
2. I have the subform setup to show only the most recent entry. What
is
a simple way to allow access to earlier entries ie. the tblHISTORY
data.

Jeff


BruceM wrote:
I believe there are ways of using code to write to several
different
tables,
but that isn't the way to go here. First of all, make a copy of
your
database. After that, make a query based on tblBuilding,
containing
only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer,
Seller)
and
BuildingID. It will be simplest if tblHistory uses the same field
names.
Make the query an Append query (Help has more information about
that)
to
append the data into tblHistory. With the relationship I suggested
between
tblBuilding and tblHistory, and the form/subform as I described,
this
new
data in tblHistory will show up in the subform. Keep the SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can
confirm
with
the form/subform that the tblHistory records contain the
information
they
should. Once you have verified the data you can delete the fields
from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones
already
in
tblBuilding (Bank information, maybe?) or new ones (Comments,
perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo
into
tblHistory. Don't worry about HistoryID. Since it is autonumber
it
will
be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design
view,
but
that the foreign key (FK) field comes into being because of its
relationship
with the PK field. You don't define the FK in the same way as you
do
the
PK. Also, note that the FK field is the same data type as the PK
field
to
which it is related, unless the PK field is Autonumber, in which
case
the
FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers and
sellers,
particularly if there are institutional buyers/sellers (real estate
investment companies, for instance). It may look something like
this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many
relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID
fields)
as
described previously. For the buyer and seller fields in
tblHistory,
create
combo boxes on the subform. You will store BuyerSellerID, but
display
the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as
needed.
The
main thing for now is to get tblBuilding and tblHistory in order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building
data
AND transaction data to tblBUILDING, do I have to separate the
data
or
can I have the main form send some fields to tblBUILDING and
other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as
tblTransaction.
Design
the two tables with the one-to-many relationship I originally
suggested
(between the two BuildingID fields). Now build the form/subform
as
I
suggested. You may look at the latest transaction, or the
history
of
all
transactions. There is no need to move the Transaction history
data
to
another table. Instead you design the form so that only the
current
transaction is shown.

Try building it as I suggested. You will have all of the
transactions
at
hand, but we can take care of how the information is displayed.
I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in
my
main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a
uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to
the
new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the
old
data
to automatically be copied to tblHISTORY. tblHISTORY and
tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old
data
and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory
as
its
record
source, updating tblHistory is best done through the subform.
I
don't
know
what you meant about tblHistory automatically updating when
you
update
its
record using the main form, since you wouldn't update a
record
in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In
the
case
of
tblBuilding, that information may include address, number of
rooms,
date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the
History?
Are
you planning to move data from one table to another to make
room
for
new
data?
If you want you can show just the first record (the most
recent
transaction)
in a subform, then click a button to show the rest of the
history.
Or
you
can have the subform open to the most recent History item.
Or
maybe
you
could make two subforms bound to tblHistory: one for the most
recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when
I
update
its record using the main FORM. My goal is to have a
HISTORY
table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and
the
HISTORY
table will have all of the prior transactions. My guess is
that
certain
fields would have to be copied to the HISTORY table upon an
update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields.
Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and
another
(fsubHistory)
based on tblHistory. You may want to set the default view
of
fsubHistory
to
Continuous so that you can see several history items at
once.
With
frmBuilding open in design view, drag the icon for
fsubHistory
onto
it.
This creates a form/subform based on the related tables.
Add
building
information via the main form, and history information via
the
subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called
BUILDINGS.
I am tracking the sale history of each building so I
made a
table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so
that
each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING
HISTORY
table
when
the FORM is updated?

Jeff

When I enter addresses, the form takes me directly to the record I am
looking for.
BUT...once there, if I scroll from one record to another, the address
stays the same, replacing other records with the same address?
Any thoughts on how to stop this?

Jeff


BruceM wrote:
A combo box will take care of that task. Use the wizard, and choose the
option that starts "Find a record on my form...".

If you build a query based on tblBuilding, and sort by Address (or
whatever), it will help you to find records.

"Techknownothing" wrote in message
ups.com...
OK
A lot to chew on but I'm pretty sure I get it.

One (hopefully) last questions on this topic.
As I receive new data:

1. I search for the record in the main form by its address and if I
find it in the database, I update it.
2. If I don't find it, I create a new record.

I would like to remove or automate the first search step. For instance,
I type in the address and an autofill function populates the field with
the matching address (if one exists) and takes me to that record. Can
this be done in a relatively simple fashion?

Jeff

BruceM wrote:
Part of the answer depends on what you mean by "see". If you set the
Default View on the subform's property sheet to Continuous you will see
all
of the history records, although you may need to scroll to do so.
Toggling
betweent he form and the table is not a good choice. Users should not be
allowed into the tables. Datasheet view will give you something of the
look
of a table, although your formatting options are limited. Toggling
between
different views would be difficult. You would need to open the report in
design view, change the Default View, close the form, and open it again.
The user would be prompted to save changes, I think. You may be able to
toggle between two different subforms, but any code that references the
subform would also need to be changed. If you wish to see a report, you
can
save your form as a report (rptBuilding), then adjust the formatting as
you
would like. A command button on the form (to open the report) would have
something like this as its Click event:

Dim strCriteria As String

strCriteria = "[BuildingID]=" & Me.BuildingID
DoCmd.OpenReport "rptBuilding", acPreview, , strCriteria

For navigation buttons, one option is he
http://www.lebans.com/recnavbuttons.htm

Another version is to create cmdFirst, cmdPrev, cmdNext, cmdLast, and
cmdNew
command buttons, and an unbound text box (txtCounter). In the Click
event
for the New Record command button:

DoCmd.GoToRecord , , acNewRec

Use acNext, acPrev, acFirst, acLast for the other command buttons.

In the form's current event:

' Inserts current record number and total number of records
Dim strCurrent as String, strTotal as String

strCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
strTotal = Me.RecordsetClone.RecordCount
Me.txtCounter = strCurrent & " of " & strTotal

' Enables/disables command buttons as needed
cmdFirst.Enabled = Not Me.CurrentRecord = 1
cmdPrev.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount

1) _
Or Me.CurrentRecord Me.Recordset.RecordCount
cmdLast.Enabled = Me.cmdNext.Enabled = True


As an alternative to the custom navigation buttons, you could do
something
like this in the form's Current event:

' Hide the navigation buttons if there is just one record:
If strTotal = 1 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

However, you would need a way of creating a new record. Tabbing through
the
controls is one such way, but not very user-friendly.

As you can see, there are all sorts of approaches. If you need something
more specific, you need to be *very* specific about just what you need.
For
instance, you can see all of the history records for a building at any
time
by navigating through the records or by diplaying the result as a report.
If you need something other than that, I cannot see what it is.

"Techknownothing" wrote in message
oups.com...
Poorly worded question. Sorry.
I would like to see ALL of the previous transactions for a particular
building upon demand.
Ideally with one click.
For instance, maybe I could allow users to toggle between the form and
the table it is bound to?
Any suggestions on that and hiding the navigation arrows would be much
appreciated.

Jeff
DCide

BruceM wrote:
I got very busy today and did not get back to the newsgroup until I
was
almost ready to leave for the day. Quick answers for now, with more
detail
to follow.

1. Yes, you can hide the navigation arrows, or you can create your
own.
I
don't have time to go into the details today, but it is not all that
complex.

2. I may be missing something in the question, but you would use the
navigation arrows to move to previous records. However, if you have
subform
data Entry property set to Yes I don't think you will see the previous
records. How have you set it up to see just the most recent record?

"Techknownothing" wrote in message
ups.com...
OK
I have the tables and forms set up per your suggestion and
everything
is working fine
Thank you.
A few more questions.
1. Right now I have a main form bound to tbBUILDING and a subform
bound
to tblHISTORY. Each form has its own navigation bar which I think is
a
bit confusing. Can I hide the navigation bar to the subform and make
it
available on demand?
2. I have the subform setup to show only the most recent entry. What
is
a simple way to allow access to earlier entries ie. the tblHISTORY
data.

Jeff


BruceM wrote:
I believe there are ways of using code to write to several
different
tables,
but that isn't the way to go here. First of all, make a copy of
your
database. After that, make a query based on tblBuilding,
containing
only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer,
Seller)
and
BuildingID. It will be simplest if tblHistory uses the same field
names.
Make the query an Append query (Help has more information about
that)
to
append the data into tblHistory. With the relationship I suggested
between
tblBuilding and tblHistory, and the form/subform as I described,
this
new
data in tblHistory will show up in the subform. Keep the SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can
confirm
with
the form/subform that the tblHistory records contain the
information
they
should. Once you have verified the data you can delete the fields
from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones
already
in
tblBuilding (Bank information, maybe?) or new ones (Comments,
perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo
into
tblHistory. Don't worry about HistoryID. Since it is autonumber
it
will
be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design
view,
but
that the foreign key (FK) field comes into being because of its
relationship
with the PK field. You don't define the FK in the same way as you
do
the
PK. Also, note that the FK field is the same data type as the PK
field
to
which it is related, unless the PK field is Autonumber, in which
case
the
FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers and
sellers,
particularly if there are institutional buyers/sellers (real estate
investment companies, for instance). It may look something like
this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many
relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID
fields)
as
described previously. For the buyer and seller fields in
tblHistory,
create
combo boxes on the subform. You will store BuyerSellerID, but
display
the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as
needed.
The
main thing for now is to get tblBuilding and tblHistory in order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording building
data
AND transaction data to tblBUILDING, do I have to separate the
data
or
can I have the main form send some fields to tblBUILDING and
other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as
tblTransaction.
Design
the two tables with the one-to-many relationship I originally
suggested
(between the two BuildingID fields). Now build the form/subform
as
I
suggested. You may look at the latest transaction, or the
history
of
all
transactions. There is no need to move the Transaction history
data
to
another table. Instead you design the form so that only the
current
transaction is shown.

Try building it as I suggested. You will have all of the
transactions
at
hand, but we can take care of how the information is displayed.
I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale in
my
main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that has a
uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to
the
new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like the
old
data
to automatically be copied to tblHISTORY. tblHISTORY and
tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the old
data
and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses tblHistory
as
its
record
source, updating tblHistory is best done through the subform.
I
don't
know
what you meant about tblHistory automatically updating when
you
update
its
record using the main form, since you wouldn't update a
record
in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity. In
the
case
of
tblBuilding, that information may include address, number of
rooms,
date
built, and so forth. It would not include information about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to the
History?
Are
you planning to move data from one table to another to make
room
for
new
data?
If you want you can show just the first record (the most
recent
transaction)
in a subform, then click a button to show the rest of the
history.
Or
you
can have the subform open to the most recent History item.
Or
maybe
you
could make two subforms bound to tblHistory: one for the most
recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update when
I
update
its record using the main FORM. My goal is to have a
HISTORY
table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and
the
HISTORY
table will have all of the prior transactions. My guess is
that
certain
fields would have to be copied to the HISTORY table upon an
update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID fields.
Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and
another
(fsubHistory)
based on tblHistory. You may want to set the default view
of
fsubHistory
to
Continuous so that you can see several history items at
once.
With
frmBuilding open in design view, drag the icon for
fsubHistory
onto
it.
This creates a form/subform based on the related tables.
Add
building
information via the main form, and history information via
the
subform
as
needed.

"Techknownothing" wrote in message
oups.com...
I have a FORM that draws data from a table called
BUILDINGS.
I am tracking the sale history of each building so I
made a
table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so
that
each
building
has a unique ID and the BUILDING table has a one to many
relationship
with the BUILDING HISTORY table through that unique ID.
How do I program the form to populate the BUILDING
HISTORY
table
when
the FORM is updated?

Jeff

The combo box is unbound, so it needs to be updated as you move to a new
record. If you used the wizard, I believe it added BuildingID (the key
field) on its own if you didn't select it. On the combo box property sheet,
you should see on the Format tab that the Column Count is 2 and the Column
Widths are something like 0";1". On the Data tab, the Bound Column is
probably 1. The combo box itself, though, is unbound (its Control Source is
blank on the property sheet Data tab). In the form's Current event you will
need something like this (in place of cboBuilding use the actual name for
the combo box on your form):

Me.cboBuilding = Me.BuildingID

Street names sometimes change, as to building numbers, so you will want to
use BuildingID to assure continuity. There are several approaches you could
use. The combo box could be in the header, and an Address text box among
the controls in the form's Detail section, so that the Address appears
twice. You could also just put the combo box in the Detail section. You
could add a text box (txtAddress) bound to the Address field, but set its
Visible property to No. In the form's Current event you could add something
like:

If Me.NewRecord Then
Me.cboBuilding.Visible = False
Me.txtAddress.Visible = True
Else
Me.cboBuilding.Visible = True
Me.txtAddress.Visible = False
End If

If you need to edit an existing address you would need a way of running the
same code, maybe from an Edit Record command button. If you want to prevent
the Building record (or some of its fields) from being edited you could use
the same command button to unlock those fields.

The simplest solution is just to use an unbound combo box to select the
record, and have a text box bound to the Address field on the form, but
there are other options.

"Techknownothing" wrote in message
ups.com...
When I enter addresses, the form takes me directly to the record I am
looking for.
BUT...once there, if I scroll from one record to another, the address
stays the same, replacing other records with the same address?
Any thoughts on how to stop this?

Jeff


BruceM wrote:
A combo box will take care of that task. Use the wizard, and choose the
option that starts "Find a record on my form...".

If you build a query based on tblBuilding, and sort by Address (or
whatever), it will help you to find records.

"Techknownothing" wrote in message
ups.com...
OK
A lot to chew on but I'm pretty sure I get it.

One (hopefully) last questions on this topic.
As I receive new data:

1. I search for the record in the main form by its address and if I
find it in the database, I update it.
2. If I don't find it, I create a new record.

I would like to remove or automate the first search step. For instance,
I type in the address and an autofill function populates the field with
the matching address (if one exists) and takes me to that record. Can
this be done in a relatively simple fashion?

Jeff

BruceM wrote:
Part of the answer depends on what you mean by "see". If you set the
Default View on the subform's property sheet to Continuous you will
see
all
of the history records, although you may need to scroll to do so.
Toggling
betweent he form and the table is not a good choice. Users should not
be
allowed into the tables. Datasheet view will give you something of
the
look
of a table, although your formatting options are limited. Toggling
between
different views would be difficult. You would need to open the report
in
design view, change the Default View, close the form, and open it
again.
The user would be prompted to save changes, I think. You may be able
to
toggle between two different subforms, but any code that references
the
subform would also need to be changed. If you wish to see a report,
you
can
save your form as a report (rptBuilding), then adjust the formatting
as
you
would like. A command button on the form (to open the report) would
have
something like this as its Click event:

Dim strCriteria As String

strCriteria = "[BuildingID]=" & Me.BuildingID
DoCmd.OpenReport "rptBuilding", acPreview, , strCriteria

For navigation buttons, one option is he
http://www.lebans.com/recnavbuttons.htm

Another version is to create cmdFirst, cmdPrev, cmdNext, cmdLast, and
cmdNew
command buttons, and an unbound text box (txtCounter). In the Click
event
for the New Record command button:

DoCmd.GoToRecord , , acNewRec

Use acNext, acPrev, acFirst, acLast for the other command buttons.

In the form's current event:

' Inserts current record number and total number of records
Dim strCurrent as String, strTotal as String

strCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
strTotal = Me.RecordsetClone.RecordCount
Me.txtCounter = strCurrent & " of " & strTotal

' Enables/disables command buttons as needed
cmdFirst.Enabled = Not Me.CurrentRecord = 1
cmdPrev.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And
Me.Recordset.RecordCount

1) _
Or Me.CurrentRecord Me.Recordset.RecordCount
cmdLast.Enabled = Me.cmdNext.Enabled = True


As an alternative to the custom navigation buttons, you could do
something
like this in the form's Current event:

' Hide the navigation buttons if there is just one record:
If strTotal = 1 Then
Me.NavigationButtons = False
Else
Me.NavigationButtons = True
End If

However, you would need a way of creating a new record. Tabbing
through
the
controls is one such way, but not very user-friendly.

As you can see, there are all sorts of approaches. If you need
something
more specific, you need to be *very* specific about just what you
need.
For
instance, you can see all of the history records for a building at any
time
by navigating through the records or by diplaying the result as a
report.
If you need something other than that, I cannot see what it is.

"Techknownothing" wrote in message
oups.com...
Poorly worded question. Sorry.
I would like to see ALL of the previous transactions for a
particular
building upon demand.
Ideally with one click.
For instance, maybe I could allow users to toggle between the form
and
the table it is bound to?
Any suggestions on that and hiding the navigation arrows would be
much
appreciated.

Jeff
DCide

BruceM wrote:
I got very busy today and did not get back to the newsgroup until I
was
almost ready to leave for the day. Quick answers for now, with
more
detail
to follow.

1. Yes, you can hide the navigation arrows, or you can create your
own.
I
don't have time to go into the details today, but it is not all
that
complex.

2. I may be missing something in the question, but you would use
the
navigation arrows to move to previous records. However, if you
have
subform
data Entry property set to Yes I don't think you will see the
previous
records. How have you set it up to see just the most recent
record?

"Techknownothing" wrote in message
ups.com...
OK
I have the tables and forms set up per your suggestion and
everything
is working fine
Thank you.
A few more questions.
1. Right now I have a main form bound to tbBUILDING and a subform
bound
to tblHISTORY. Each form has its own navigation bar which I think
is
a
bit confusing. Can I hide the navigation bar to the subform and
make
it
available on demand?
2. I have the subform setup to show only the most recent entry.
What
is
a simple way to allow access to earlier entries ie. the
tblHISTORY
data.

Jeff


BruceM wrote:
I believe there are ways of using code to write to several
different
tables,
but that isn't the way to go here. First of all, make a copy of
your
database. After that, make a query based on tblBuilding,
containing
only
the fields needed for tblHistory (e.g. SaleDate, Price, Buyer,
Seller)
and
BuildingID. It will be simplest if tblHistory uses the same
field
names.
Make the query an Append query (Help has more information about
that)
to
append the data into tblHistory. With the relationship I
suggested
between
tblBuilding and tblHistory, and the form/subform as I described,
this
new
data in tblHistory will show up in the subform. Keep the
SaleDate,
SalePrice, etc. fields in tblBuilding for now, so that you can
confirm
with
the form/subform that the tblHistory records contain the
information
they
should. Once you have verified the data you can delete the
fields
from
tblBuilding.

It may go something like this:

tblBuilding (current structure)
BuildingID (PK)
BuildingAddress
Other building-specific fields
SaleDate
SalePrice
Buyer
Seller

tblHistory
HistoryID (autonumber PK)
BuildingID (FK)
SaleDate
SalePrice
Buyer
Seller
Any other fields specific to the transaction, either ones
already
in
tblBuilding (Bank information, maybe?) or new ones (Comments,
perhaps).

qryTransactionInfo (based on tblBuilding)
BuildingID
SaleDate
SalePrice
Buyer
Seller

Append all of the fields (append query) from qryTransactionInfo
into
tblHistory. Don't worry about HistoryID. Since it is
autonumber
it
will
be
populated automatically when you add the new records.

Note that the primary key (PK) field is defined in table design
view,
but
that the foreign key (FK) field comes into being because of its
relationship
with the PK field. You don't define the FK in the same way as
you
do
the
PK. Also, note that the FK field is the same data type as the
PK
field
to
which it is related, unless the PK field is Autonumber, in which
case
the
FK
is defined in table design view as a Number field.

You would probably do well to create another table for buyers
and
sellers,
particularly if there are institutional buyers/sellers (real
estate
investment companies, for instance). It may look something like
this:

tblBuyerSeller
BuyerSellerID (PK)
BuyerName
BuyerAddress, etc.

Add BuyerSellerID to tblHistory, then create a one-to-many
relationship
betweeen tblBuyerSeller and tblHistory (on the BuyerSellerID
fields)
as
described previously. For the buyer and seller fields in
tblHistory,
create
combo boxes on the subform. You will store BuyerSellerID, but
display
the
BuyerSeller name.

I'm just sketching this out for now, but can expand upon it as
needed.
The
main thing for now is to get tblBuilding and tblHistory in
order.

"Techknownothing" wrote in message
oups.com...
OK
Now I understand.
Thanks for your patience.
Question: Since I already have the main FORM recording
building
data
AND transaction data to tblBUILDING, do I have to separate the
data
or
can I have the main form send some fields to tblBUILDING and
other
fields to tblHISTORY?

Jeff


BruceM wrote:
Rather than calling it tblHistory, think of it as
tblTransaction.
Design
the two tables with the one-to-many relationship I originally
suggested
(between the two BuildingID fields). Now build the
form/subform
as
I
suggested. You may look at the latest transaction, or the
history
of
all
transactions. There is no need to move the Transaction
history
data
to
another table. Instead you design the form so that only the
current
transaction is shown.

Try building it as I suggested. You will have all of the
transactions
at
hand, but we can take care of how the information is
displayed.
I
believe
the results will be to your liking.

"Techknownothing" wrote in message
ups.com...
Let me try to explain another way.
I need to track sales of buildings in a certain region.
When a sale is posted, I enter data pertaining to that sale
in
my
main
FORM.
That FORM records the data into tblBUILDING
I have about 700 records in tblBUILDING.
Each of those records refers to a specific building that
has a
uniqueID
(autonumber).
Some of these buildings are coming up for sale again.
When they do, I would like to update the data pertaining to
the
new
sale. (price, buyer, seller)
BUT... I don't want to discard the old data. I would like
the
old
data
to automatically be copied to tblHISTORY. tblHISTORY and
tblBUILDING
are already linked via the uniqueID.
I want it automated so as to preserve the integrity of the
old
data
and
to save time.
can this be done?

Jeff

BruceM wrote:
Since the subform rather than the main form uses
tblHistory
as
its
record
source, updating tblHistory is best done through the
subform.
I
don't
know
what you meant about tblHistory automatically updating
when
you
update
its
record using the main form, since you wouldn't update a
record
in
tblHistory
by using a form bound to a different table.
A table should contain information about a single entity.
In
the
case
of
tblBuilding, that information may include address, number
of
rooms,
date
built, and so forth. It would not include information
about
transactions.
These belong in another related table.
What do you plan to do when you need to add something to
the
History?
Are
you planning to move data from one table to another to
make
room
for
new
data?
If you want you can show just the first record (the most
recent
transaction)
in a subform, then click a button to show the rest of the
history.
Or
you
can have the subform open to the most recent History item.
Or
maybe
you
could make two subforms bound to tblHistory: one for the
most
recent
transaction and one for the rest.

"Techknownothing" wrote in message
ups.com...
OK
I have done this and its working.
Thank you.
BUT ... I need the HISTORY tbl to automatically update
when
I
update
its record using the main FORM. My goal is to have a
HISTORY
table
that
tells me:
- the date the building was last sold
- what the price
- buyer
- seller

The main FORM will have the most current transaction and
the
HISTORY
table will have all of the prior transactions. My guess
is
that
certain
fields would have to be copied to the HISTORY table upon
an
update
but
I don't know how to do that.

Jeff

BruceM wrote:
tblBuilding
BuildingID (autonumber primary key, or PK)
Fields as needed for building information

tblHistory
HistoryID (PK)
BuildingID (foreign key, or FK)
Fields as needed for history information

Create a relationship between the two BuildingID
fields.
Click
Enforce
Referential Integrity when you do so.

Create a form (frmBuilding) based on tblBuilding, and
another
(fsubHistory)
based on tblHistory. You may want to set the default
view
of
fsubHistory
to
Continuous so that you can see several history items at
once.
With
frmBuilding open in design view, drag the icon for
fsubHistory
onto
it.
This creates a form/subform based on the related
tables.
Add
building
information via the main form, and history information
via
the
subform
as
needed.

"Techknownothing" wrote in
message
oups.com...
I have a FORM that draws data from a table called
BUILDINGS.
I am tracking the sale history of each building so I
made a
table
called BUILDING HISTORY.
The two tables are linked via the ID (autonumber) so
that
each
building
has a unique ID and the BUILDING table has a one to
many
relationship
with the BUILDING HISTORY table through that unique
ID.
How do I program the form to populate the BUILDING
HISTORY
table
when
the FORM is updated?

Jeff