Inventory Library using VBA in Access 2003

[kimtorvinen]

Hello, wondering if anyone has created a VBA expression to ensure that
once you have lent out a book/print/video that you cannot select this
same item on another order until it is returned. Thanks.

Kim

 

[Ken Sheridan]

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 the sum
of the ColumnWidths.

Ken Sheridan
Stafford, England

 

[kimtorvinen]

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 -

Thanks Ken I appreciate all the information I can get. I'm having
trouble relating my main table & subform to your OrderDetails
table.

My Main Form is Lending.tbl and my subform is LendTransactions. I'm
pretty new to reading code so I interpret that Me.ItemID is the main
form or is OrderDetails? ItemID is my LendTransactions (subform)?

Sorry for the confusion, is there a link to interpreting these
statements? Thanks for all your help.

Kim

 

[kimtorvinen]

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

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 -

Excellent, thanks for all your help Ken! My coworker and I were able
to figure it out with your help.

Kim
Edmonton, Canada