stop duplicate request

[leah_603]

I will explain this situation using a scenario. Let's assume that i'm working
on a library system where i need a loan form that will only show books that
are available for loan. In this case, this form has a main form that shows
library member's details and its subform shows the details of the books that
are loan by library member. In this subform, one of the field (ISBN no)
displays its values using a combo box. Once a value is selected from this
combo box, values in other fields found in the subform will be shown too.
These values are based on a table that contain books info, where only the
value (ISBN no) in the combo box comes from a query. This query will only
show books that are available for loan.

If a library member wants to borrow 'booktitle1' then this book will not be
available to be loan by other members and assumed that there is only one
'booktitle1'.

I tried to update the field manually by changing the status field everytime
the book is loan out. I hope to solve this problem in an effective way.


leah

 

[John Vinson]

I tried to update the field manually by changing the status field everytime
the book is loan out. I hope to solve this problem in an effective way.

Base the Combo box on an "Unmatched" Query selecting only those
records in the Books table which are NOT in a query of current loans:

SELECT Books.ISBN, Books.Title
FROM Books
WHERE NOT EXISTS (SELECT Loans.ISBN FROM Loans
WHERE Loans.DateOut < Date() AND Loans.DateReturned IS NULL AND
Loans.ISBN = Books.ISBN);

The exact syntax will differ depending on your table structure but
that's a start, I hope.

John W. Vinson[MVP]

 

[leah_603 via AccessMonster.com]

Thanks for your reply.

I don't know VB yet, so i am having difficulty with reading VB syntax.
Is there any other way that does not involve VB?
I am still thinking whether, i should learn VB or not, in order to solve this
problem.

Thanks again.


leah_603

 

[John Vinson]

Thanks for your reply.

I don't know VB yet, so i am having difficulty with reading VB syntax.
Is there any other way that does not involve VB?
I am still thinking whether, i should learn VB or not, in order to solve this
problem.

Well, that's not VB - it's SQL, the language of Queries.

Create a new Query with no table. Use the View menu option to view it
in SQL mode (it will be a big white textbox containing just the word
SELECT.

Copy and paste the SQL string in my answer into the textbox. Change
any fieldnames or tablenames to those that you're using. Open the
query and see if it gives you what you want.

John W. Vinson[MVP]

 

[leah_603 via AccessMonster.com]

I am sorry, i didn't know that, the code wasn't VB.
I haven't learned SQL before but I 'll try to figure that out.

Anyway thanks a lot.

leah_603

 

[leah_603 via AccessMonster.com]

Would you mind to explain the SQL code?
I can't figure it out.

Thanks

leah_603

 

[John Vinson]

Would you mind to explain the SQL code?
I can't figure it out.

Sure. Line by line:

SELECT Books.ISBN, Books.Title

Choose whichever fields you want to see (using your own fieldnames of
course, I was guessing)

FROM Books

specify the name of the table containing the books

WHERE NOT EXISTS

The WHERE clause indicates which books you want to see. You want to
see those books which are NOT loaned out - that is, where there does
*not* exist a record in the loans table.

(SELECT Loans.ISBN FROM Loans

You can select any field from the loans table; all the NOT EXISTS
clause needs is some field.

WHERE Loans.DateOut < Date() AND Loans.DateReturned IS NULL AND

This was a pure guess, since I don't know how your table is
structured; typically in a library table you'ld have a loans table
with field for the date checked out and the date returned. If the book
was checked out earlier than today's date, and it has no return date
(yet), then it's presumably checked out.

Loans.ISBN = Books.ISBN);

This does the is-it-checked out test for the current book in the main
query.

John W. Vinson[MVP]

 

[leah_603 via AccessMonster.com]

Thanks for the reply.

If the problem is not in the query part, what else can i do about that.

What i encountered is i need to close the loan form before it can update the
data (details about
books that are available for loan). It's like i have to close the form and
reopen it again,
then the book that is loan out will not be shown in the combo box.

Thanks.


leah_603

 

[John Vinson]

Thanks for the reply.

If the problem is not in the query part, what else can i do about that.

What i encountered is i need to close the loan form before it can update the
data (details about
books that are available for loan). It's like i have to close the form and
reopen it again,
then the book that is loan out will not be shown in the combo box.

Thanks.


leah_603

Requery the Combo Box in the AfterUpdate event of the checkout
subform.

John W. Vinson[MVP]

 

[leah_603 via AccessMonster.com]

How am i able to do that?

thanks again.


leah_603

 

[John Vinson]

How am i able to do that?

"that" being

Requery the Combo Box in the AfterUpdate event of the checkout
subform.

Open the Form you're using as the checkout subform.
View its properties.
Select the "After Update" property on the Events tab.
Click the ... icon by it, and invoke the Code Builder.
Edit the Sub and End Sub lines to

Private Sub Form_AfterUpdate()
Forms![YourFormName]![YourComboName].Requery
End Sub

John W. Vinson[MVP]

 

[leah_603 via AccessMonster.com]

I am sorry for keeping you explaining to me all the time.

The problem with the duplicate request is now solved.
I used macro to solve it.

Thanks for all your replies.
I really do appreciate them.

Thanks again.

leah_603