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 here:
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.
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?
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.
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.
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.
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.
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
