It depends how you are recording the return of an item, but lets say you have
an OrderDetails table with columns OrderID, ItemID, DateRemoved, DateReturned
then in the BeforeUpdate event procedure of the control (most probably a
combo box) bound to the ItemID column in a subform within an Orders form
you'd put something like this:
Const MESSAGE = "This item is currently unavailable."
Dim strCriteria As String
Dim blnIsRemoved As Boolean
strCriteria = "ItemID = " & Me.ItemID & " And IsNull(DateReturned)"
blnIsRemoved = Not IsNull(DLookup("ItemID","OrderDetails",strCriteria))
If blnIsRemoved Then
MsgBox MESSAGE, vbInformation, "Warning"
Cancel = True
End If
You could in fact indicate currently available/unavailable items in the list
of available items by setting the RowSource property of the bound combo box
on your form to:
SELECT ItemID, Item,
IIF(EXISTS
(SELECT *
FROM OrderDetails
WHERE OrderDetails.ItemID = Items.ItemID
AND DateReturned IS NULL), "Unavailable", "Available")
FROM Items
ORDER BY Item;
You would need to requery the combo box in the subform's AfterUpdate event
procedure to update the list. Its other properties would be along these
lines:
BoundColum: 1
ColumnCount: 3
ColumnWidths: 0cm;3cm;3cm
ListWidth: 6cm
Experiment with the ColumnWidths property to get the best fit, but the first
dimension must be zero to hide the ItemID. The ListWidth should be thesum
of the ColumnWidths.
Ken Sheridan
Stafford, England
- Show quoted text -
Kim:
The important thing is not the forms as such, but the underlying tables. A
relational database is a model of part of the real world in terms of entity
types and the relationships between them. Each entity type is represented
by a table.
In a case like yours what you have are firstly entity types Borrowers and
Items. You also have an entity type Orders which is related to Borrowers in
a many-to-one relationship (each borrower might make one or more orders).
So translating this into tables would mean you'd have three tables named
something like Borrowers, Orders and Items. The Orders table would include a
foreign key column such as BorrowerID which references the primary key
column, also BorrowerID of Borrowers. So the structure of these three tables
would be along these lines:
Borrowers
----BorrowerID (primary key)
----FirstName
----LastName
----AddressLine1
----AddressLine2
<and so on>
Orders
----OrderID (primary key)
----OrderDate
----BorrowerID
Items
----ItemID (primary key)
----Item
The table names can be whatever you wish of course, but its best to make
them as close as possible to a real language name for the entity type being
modelled, and similarly column names should be as close to a real name for
the attribute type the column represents.
Each of the columns in the above tables represents an 'attribute' of the
entity type which the table represents. Each attribute should be specific to
the entity type (in the jargon its said to be functionally dependent solely
on the whole of the key of the table). This eliminates 'redundancy'.
Redundancy is when the same piece of information is contained more than once
in the database. This can lead to inconsistent data as there's nothingto
stop the instances of each 'fact' contradicting each other (I found three
versions of my own name as author of technical papers in one database!).
You'll have noticed that with the above tables there is no way of showing
which items a borrower has taken out. This is because the relationship
between Orders and Items is not a simple one-to-many one which can be
represented by a foreign key column in one of the tables. Its a many-to-many
relationship (each order can be for one or more items, and each item can be
included in one or more orders, though not simultaneously in your case). A
many-to-many relationship is modelled by creating another table, OrderDetails
say. This has two foreign key columns each of which reference the primary
key of one of the other tables, in this case OrderID and ItemID. Although
this table is modelling a relationship type between Orders and Items its also
modelling an entity type, because a relationship type is really just a
special kind of entity type. So it will have its own attributes in addition
to the OrderID and ItemID.
When it comes to the attributes of OrderDetails, and hence the columns in
the table, there are two possible alternatives here. It depends on whether
all items in an order are borrowed at the same time. If so you don't need a
DateBorrowed column in OrderDetails as you already have an OrderDate column
in Orders. Assuming they can be returned at different times you'd havea
DateReturned column however, so for the the table would be structured like
this:
OrderDetails
----OrderID
----ItemID
----DateReturned
So the relationships would be like this, with the < and > signs representing
the 'many' side of each relationship:
Borrowers---<Orders---<OrderDetails>---Items
If, however, each item in an order might be borrowed on different dates then
you would also need a DateBorrowed date in OrderDetails. In fact this
probably rules out the need for an Orders table completely as it adds no
additional information (unless there are other attributes of each order as a
whole), so the model can be:
Borrowers---<OrderDetails>---Items
in which case the OrderID column in OrderDetails would be replaced by
BorrowerID.
Whichever model is appropriate the set-up I'd envisage for data entry would
be either an Orders form or a Borrowers form (in single form view) with a
subform (in continuous form or datasheet view) based on the OrderDetails
table, or if its in a Customers rather than and Orders form on a query onthe
OrderDetails which sorts rows by DateBorrowed in descending order, so the
latest are shown first in the subform. The code I gave you would be inthe
subform's module.
As for links I'd suggest you Google 'Access +Tutorial'. You'll find many
links to online tutorials.
Ken Sheridan
Stafford, England
- Show quoted text -
The Trans_In_Out column can be used in much the same way as a Null
DateReturned value in my example. Its not quite clear what the value of the
column would indicate, but if we assume that True (Yes) means the item has
been lent out then you can indicate in the combo box's list if an item is
available or not by setting the RowSource property for the Print_ID combobox
to:
SELECT Print_ID, Title, Author,
IIF(EXISTS
(SELECT *
FROM LendTransactions
WHERE LendTransactions.Print_ID = [Print Table].Print_ID
AND NOT Trans_In_Out), "Unavailable", "Available")
FROM [Print Table]
ORDER BY Title, Author;
in which case the other properties of the combo box would be something like
this:
BoundColum: 1
ColumnCount: 4
ColumnWidths: 0cm;3cm;3cm;3cm
ListWidth: 9cm
Alternatively you could restrict the list to available items only by means
of a RowSource property such as:
SELECT Print_ID, Title, Author
FROM [Print Table]
WHERE NOT EXISTS
(SELECT *
FROM LendTransactions
WHERE LendTransactions.Print_ID = [Print Table].Print_ID
AND Trans_In_Out)
ORDER BY Title, Author;
If you do the latter you would have to requery the combo box in its
AfterUpdate event procedure so that the combo box's list no longer shows the
item when you select from it in another row of the subform. This creates a
problem, however. Because you are using hidden surrogate keys, Print_ID etc,
you would not be able to see the selected Title in any existing rows in the
subform. The values of the underlying Print_ID column would be unchanged;
you'd just see empty combo boxes. There is a solution to this, which is to
use a hybrid control of a text box overlaid on a combo box so that it appears
to the user as a single control. You'll find an example of this at the
following link. It uses the local administrative units of Parish, District
and County in my area, but the principle is similar:
http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&ts....
I suspect that your current skill level might not be quite up to
implementing this, however, so my recommendation would be that you use the
easier first option, showing 'available, or 'unavailable' in the list. You
should still requery the combo box in its AfterUpdate event procedure, but
this would only change the value from 'available' to 'unavailable', not blank
out the control.
You'd need to validate the user's selection still, this time in the combo
box's BeforeUpdate event procedure, the code now being:
Const MESSAGE = "This item is currently unavailable."
Dim strCriteria As String
Dim blnIsRemoved As Boolean
strCriteria = "Print_ID = " & Me.Print_ID & " And Not Trans_In_Out)"
blnIsRemoved = Not IsNull(DLookup("Print_ID"," LendTransactions",
strCriteria))
If blnIsRemoved Then
MsgBox MESSAGE, vbInformation, "Warning"
Cancel = True
End If
You'd do similarly for the Video_ID and Audio_ID combo boxes
That should work, but I'd point out that your Lending table is not properly
normalized. By having the L_First_Name, L_Last_Name columns in this table we
are being told that these are the names of the lender in every row in which
they lend items. So if a lender lends items to three borrowers then we'd
redundantly be told the lender's name three times. You should just have a
LenderID foreign column which references the LenderID primary key of a table
Lenders in which there is one row per lender with their names etc. Similarly
you should just have a Borrower ID referencing a Borrowers table. If lenders
can be borrowers and vice versa then you just need a single People table of
course. This could be related to Lenders and Borrowers tables in one-to-one
relationships so you could have attributes (columns) specific to lenders and
borrowers in each. This would be a Type/Sub-type model, which is aboutthe
only time you use a one-to-one relationship.
I note that your lending and return dates are in the Lending table. That's
fine if all related items in LendTransactions are lent and returned at the
same time. The former is going to be true I'd guess, but if individualitems
might be returned, legitimately or otherwise, on different dates then the
return date should be a column in LendTransactions, not in Lending.
You also should have an Authors table with primary key AuthorID and
reference this from [Print Table] etc with a foreign key AuthorID column
(remember the three instances of me!). Don't use names as keys; names can be
duplicated.
For sending emails take a look at the SendObject method in Help.
Ken Sheridan
Stafford, England
[email protected] said:Thanks Ken. We have been working on this Inventory database for
sometime. We have linked the mainform to a subform. The Main Form
table looks like this:Lending_ID - AutoNumber (PK)
Borrower_ID - Number
L_First_Name (Lender's First Name)
L_Last_Name
C_Date (current date)
Lending_Date
To_Be_Returned_Date
Return_Date
B_First_Name (borrower's first name)
B_Last_Name
B_email
B_phone
Position
Department
Organization
LendTransactions_ID
Lending_ID
Print_ID
Video_ID
Audio_ID
Condition_Out
Condition_I
Trans_In_Out - Data Type Yes/No Field *(if the material has been lent
out or not) ** I thought this would be the indicator that would affect the status
on the listOther Linking tables:(1) Print Table
These have all linked successfully. We just needed that formula you
have given us to ensure when material is lent out it doesn't show up
on the list. I think there is some redundancy with the "condition" on
both Transactions, Print, and Video. It seems to be getting quite
difficult. We also added a link to email as a friendly reminder to
bring back materials the day before they are due. Thanks so much for
your help. Any other suggestions are greatly appreciated!Kim
Edmonton, Canada- Hide quoted text -
- Show quoted text -
Kim:
You don't need a Trans_In_Out column in the Print Table. If you look at the
SQL statement:
SELECT Print_ID, Title, Author,
IIF(EXISTS
(SELECT *
FROM LendTransactions
WHERE LendTransactions.Print_ID = [Print Table].Print_ID
AND NOT Trans_In_Out), "Unavailable", "Available")
FROM [Print Table]
ORDER BY Title, Author;
You'll see that the reference to the Trans_In_Out column is in a subquery
with the LendTransactions table in its FROM clause. What the query is saying
is:
"Give me the Print_ID, Title and Author columns from the Print Table and,if
there exists a row in the LendTransactions table where the value of the
Print_ID column in any row in the LendTransactions table equals the valueof
the Print_ID column in a row in the current row of the Print Table and the
value of the Trans_In_Out column is False, say "Unavailable", otherwise say
"Available".
The subquery here is what's known as a 'correlated subquery' because it is
correlated with the outer query (which is based on the Print Table). The
subquery in effect runs separately for every row returned by the outer query.
If it returns any rows then the EXISTS predicate is True, if not its False.
The IIF function then returns "Unavailable" or "Available" on the basis of
whatever the EXISTS predicate has evaluated to for the current row returned
by the outer query, i.e. for each book.
Don't forget that I've assumed that if Trans_In_Out is False (No) that means
a book is currently lent out. If I've got this the wrong way round just
delete the NOT before Trans_In_Out.
Ken Sheridan
Stafford, England
[email protected] said:Thanks Ken. I'm a bit confused with the first option; as I don't have
a Field Name of Trans_In_Out on Print_ID table. I have this on my
LendTransactions table. I put this on the LendTransaction table, but
I'm still seeing the list of titles when they're inactive.- Hide quotedtext -
- Show quoted text -