stop duplicate request

[leah]

I will explain this situation using a scenario. Let's assumed 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 fields (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 every
time the book is loan out. I hope to solve this problem in an effective
way.

I am new with access and i have difficulty to explain it in a much
better way. Sorry, if my description cause any sort of confusion.

These are the tables.
book_info (table1)
-------------------------------
bookTitle ---------- text
ISBN(pkey) ------- text
authorName ------ text
category ---------- text
dateReceived ---- date
publisher ---------- text
status -------------- text

user_info (table2)
--------------------------------
name --------------- text
userID(pKey) ------ text
address ------------ text
tel ------------------ number
hp ------------------ number
occupation -------- text
DOB --------------- date
loan_info (table3)
------------------------------
userID(fKey) ------ text
ISBN(fkey) -------- text
dateReturned ---- date
dateBorrowed ---- date
dateDue ---------- date
remarks ----------- text
Thanks.
Leah

[John Welch]

You could join the book_info table and the loan_info table on ISBN to get
the current UserID of the person who has a particular book checked out. Use
an outer join to get all books, but then only choose books that have a null
value for the UserID. This will give you a list of available books. Then you
shouldn't need the 'status' field (or you can keep it to show if a book is
being repaired, is missing, is circulating, etc.) Your query would look
something like this:

SELECT book_Info.ISBN from book_info left join loan_info on book_info.ISBN=
loan_info.ISBN where loan_info.UserID is null and book_info.status =
'circulating'

hope this helps
-John
"leah" <le******@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>
I will explain this situation using a scenario. Let's assumed 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 fields (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 every
time the book is loan out. I hope to solve this problem in an effective
way.

I am new with access and i have difficulty to explain it in a much
better way. Sorry, if my description cause any sort of confusion.

These are the tables.
book_info (table1)
-------------------------------
bookTitle ---------- text
ISBN(pkey) ------- text
authorName ------ text
category ---------- text
dateReceived ---- date
publisher ---------- text
status -------------- text

user_info (table2)
--------------------------------
name --------------- text
userID(pKey) ------ text
address ------------ text
tel ------------------ number
hp ------------------ number
occupation -------- text
DOB --------------- date
loan_info (table3)
------------------------------
userID(fKey) ------ text
ISBN(fkey) -------- text
dateReturned ---- date
dateBorrowed ---- date
dateDue ---------- date
remarks ----------- text
Thanks.
Leah

[leah]

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

[F6GGR]

Leah,

"ISBN" makes me think you are french like me. For me, you could create a
query that select the books that are availiable for loan. Then you could
use a RechDom function to know if a book is availiable or use this query as
a source of a form that shows theese books.

"leah" <le******@yahoo.coma écrit dans le message de news:
11*********************@i42g2000cwa.googlegroups.c om...

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

[leah]

Thanks for the reply.
I did that but still the problem exist.
The form could not update the data in the combo box instantaneously,
except after closing the form.

Leah

[F6GGR]

Did you try the update command or sendkey ctrl + ' after modifiying ?
"leah" <le******@yahoo.coma écrit dans le message de news:
11**********************@m73g2000cwd.googlegroups. com...

>
Thanks for the reply.
I did that but still the problem exist.
The form could not update the data in the combo box instantaneously,
except after closing the form.

Leah

[leah]

Maybe i didn't. Would you mind to explain more about that?
Where should i place it (the update command)?

Thanks.

leah

[F6GGR]

I tried this thing :

On the property "On clic" of the combo box : a macro with Execute command
"actualize" or an event procedure : DoCmd.RunCommand acCmdRefresh then the
request that feed the combo box is run and the list of choise is update.

It works for me on a data base i build like yours.

Alain
"leah" <le******@yahoo.coma écrit dans le message de news:
11*********************@e64g2000cwd.googlegroups.c om...

>
Maybe i didn't. Would you mind to explain more about that?
Where should i place it (the update command)?

Thanks.

leah

[leah]

Thanks for telling me that, Alain. I think that will work.
I'm going to try that out.

Thanks again.

leah

[leah]

Thanks again Alain.
After trying a few times, it really works.
Many thanks.

leah