A separate SoldItems table would in fact be a better design, but you would
not move data from the Items table to it. SoldItems is actually a sub-type
of Items, and creating this table would protect data integrity because it
allows you to do it at table level, rather than merely at form level. The
latter alone does not protect against invalid data being entered by some
other means, so the possibility of an unsold item being given a selling price
etc still exists. Having a single table also means that the columns relating
to sold items will be Null. Null is semantically ambiguous as, being the
absence of a value and not a value per se, has no intrinsic meaning, so can
cause problems of interpretation (as an example think about what a Null
credit rating for a customer would mean – it could mean 'no credit', it could
equally mean 'unlimited credit'; there is no way of knowing).
It does require a little more work to achieve the better model, however, so
I'll leave it to you to decide whether you do want to go that far, but if you
do then this is what's required:
1. In the Items table create a unique index on the table's primary key
column, ItemID say, and the SoldItems column. The index is on both columns
in conjunction, not as individual columns. This might sound odd as the
primary key column is already indexed uniquely of course in its own right by
virtue of being the primary key, but the index on the two columns is
necessary before you can create an enforced relationship with the SoldItems
table as described below.
2. Create the SoldItems table with columns ItemID, SoldItem, SellingPrice,
SoldDate, PickUpDate, etc. But do not include any other columns which
correspond to those in the Items table such as ItemDescription. The SoldItem
column in both tables should be of Boolean (Yes/No) data type.
3. Make the ItemID and the SoldItem columns the composite primary key of
SoldItems table.
4. Set the Defaultvalue property of the SoldItem column in SoldItems to:
True.
and set the ValidationRule property of the SoldItem column in SoldItems to:
=True
This means that the SoldItem column in SoldItems has a value of True inserted
automatically when a new row is inserted and that this cannot be changed to
False.
5. Create a relationship between Items and SoldItems on both the ItemID and
SoldItems columns and enforce referential integrity. You now have a solid
model which means that (a) an item cannot be given sold data such as a price
and date unless its marked as sold in Items and (b) an item cannot be
unmarked as sold in Items until ant matching row in SoldItems is deleted.
Consequently the integrity of the data is protected.
6. For data entry use a form based on Items, and within it a subform based
on SoldItems, linking the form to the subform on ItemID. Hide the subform
until an item is marked sold with:
Me.SoldItemsSubform.Visible = Me.SoldItem
in the form's Current event procedure, and put the same line of code code in
the AfterUpdate event procedure of the SoldItem control in the main parent
form. You might also wish to put some validation code in the BeforeUpdate
event procedure of the SoldItem control in the main parent form:
Const conMESSAGE = _
"You must delete the record from the Sold Items table " & _
"before marking an item unsold."
Dim strCriteria As String
strCriteria = "ItemID = " & Me.ItemID
If Not Me.SoldItem Then
If Not IsNull(DLookup("ItemID ", "SoldItems", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
Me.Undo
End If
End If
The above might sound a bit long-winded but its probably taken me longer to
type it than it would have to implement it.
Ken Sheridan
Stafford, England
That makes sense. Thanks. As far as having the additional information in a
new table. I would like it to be seperate or have it show only if the
checkbox is marked sold. (not sure how I would do that either, or if it is
possible)
So for instance if this pc was sold, data entry would mark it as sold then
additional fields would pop up that need to be filled in (selling price, sold
date, pick up date, etc...)
Does that help you make a determination in the best way to help?
[quoted text clipped - 27 lines]
but without knowing what the additional information is and what you
intend to do with it, I won't comment more on that.
