I see no reason why entering the date in a control bound to the Returned Date
column should not work. As the form's RecordSource you'll have a query
something like this:
SELECT *
FROM [Books]
WHERE [Borrowed Date] IS NOT NULL
AND [Returned Date] IS NULL
ORDER BY [Book Title];
You should only need to test for one of the columns being NULL or NOT NULL
really; examining both is not actually necessary. The ORDER BY clause is not
really relevant, but normally you'd order a query somehow so the records on
the form are in a logical order.
On the form you'll have controls in the detail section bound to the relevant
columns from those returned by the query; you don't have to include them all.
You should be able to simply enter a date in either the Borrowed Date or
Returned Date controls. You can make life a little easier for the user
however:
1. As you don't want dates in both the Borrowed and Returned date columns
simultaneously you can put code in the AfterUpdate event procedure of each
which 'empties' the other if a date is entered, so in the Borrowed date's
event procedure you'd have:
If Not IsNull(Me.[Borrowed Date]) Then
Me.[Returned Date] = Null
End If
and in Returned Date's event procedure:
If Not IsNull(Me.[Returned Date]) Then
Me.[Borrowed Date] = Null
End If
2. If you find you need to enter the current date in these controls
frequently you can enter it automatically when the control is double clicked
by putting the following in the DblClick event procedures of each In Borrowed
Date's:
Me.[Borrowed Date] = VBA.Date
Me.[Returned Date] = Null
and in Returned date's:
Me.[Returned Date] = VBA.Date
Me.[Borrowed Date] = Null
A simple one table design like this doesn't allow you to record the history
of a books being borrowed of course. By having a Borrowings table with
columns RefID, Borrowed Date, Returned Date and BorrowerID (referencing the
key of a Borrowers table) you could do so. data entry would usually be via a
form based on the Books table with a subform within it based on the
Borrowings table. A query to find the books currently 'out' would join the
tables:
SELECT Books.*, [Borrowed Date]
FROM Books INNER JOIN Borrowings
ON Books.RefID = Borrowings.RefID
WHERE [Returned Date] IS NULL;
You could extend this to include the Borrowers table and return the
borrower's name if you wished.
Ken Sheridan
Stafford, England
Engels said:
In my simple 1 table database i have a Form that is bound to a query on the
[Books] table (pk is RefID).
The query "Books Out" shows books from the table that are currently "out"
It does this by looking to see if there is a date in the "Borrowed date"
column (Is Not Null) and to check that there isnt a date in the "Returned"
date column.
My form shows the results of the query (continuous form) along with a few
buttons to link to other forms. I need to be able to click on a button next
to each record to return the book and enter the return date (not necessarily
today) which will return the date back to the original table. I though i
could do this just by entering into the bound data column but that doesnt
seem to work.
help please
