Updating specific fields in table

[itsmystie]

Hello everybody. I am new to Access and as such I don’t know how to perform the following. Please help me out.

The project is about a library loan system. I have a database consisting of the following tables: member (MemCode, LName, …), book (BkCode, Title, TotalCopies,…), purchase (PurDate, BkCode, CopiesBought,…) and transaction (MemCode, BkCode, RentalDate,ReturnDate,Returned).

TotalCopies is the total no. of copies there are of the book
CopiesBought is the number of copies that have been purchased
RentalDate is the date on which book is being loaned
ReturnDate is the last date on which the book has to be returned
Returned is a Yes/No field to indicate if the book has been returned

In the library a member can take only one book at a time.

Problem 1:
The library may have several copies of a particular book, but all will have the same code. For example, a book, let’s say Harry Potter 6 has code 97. Initially only 1 copy of the book is purchased. When the librarian sees the book has great demand, he decides to purchase 3 more copies. All the copies will have the same code, i.e., 97. Only the TotalCopies will become 4.

BkCode is Autonumber in book table and Number in Purchase table. Relationship is 1:many.

I have a Purchase form to record details of books being purchased. I have a command button “Book form” that opens the book form when the user clicks on it. He will use it if the book being purchased is not already available in the library. What I want is:

(i) If he opens the book form to fill in details of the book, then when he closes the form I want the book details to automatically appear in the corresponding fields on the Purchase form.
(ii) The CopiesBought will have to be used to update the TotalCopies in the book table. How should I do this?

Problem 2:
When the user wants to record a loan, the system will need to check if that member already has a book which he has not yet returned. If it is so, then the system will need to provide a prompt to inform the user of this and consequently blocks the user from completing the transaction. How do I do this?

Thanking you in advance for your help.

 

[gromit]

Hi - and welcome!

All of the things that you describe can be done. If you are new to Access (and new to programming in Visual Basic) then some of it may be a bit complicated to start with.

1. Displaying Book Info
The easiest way to transfer data from one form to another is to get the data into the correct table to begin with. Therefore, make sure that your Book forms (you may have or need more than one version) are based on the book table, and your Purchase form(s) are based on the purchase table.

The Purchase form can include a subform that has details about the book. The key is to establish a parent / child relationship between the two forms. Then when you enter a BookCode in a purchase record, Access will automatically fill in the correct book information.

Back on the Books form, you will (or already) have a New Purchase button. This button will need to have some code with it:

    ' Declare some variables
    Dim stDocName As String
    Dim stLinkCriteria As String

    ' Name of the form to open
    stDocName = "frmPurchases"
    
    ' Set a variable with the criteria for linking the fields
    stLinkCriteria = "[BookCode]=" & Me![BookCode]
    
    ' Use a command to open the indicated form and add a new record (purchase)
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
    
    ' Force the BookCode fields to match (required for new record)
    Forms!frmPurchases!BookCode = Me!BookCode

2. Updating the Total Books
On the Purchases form, I would include a button that is "Finalize Purchase". This button would launch an update query to add the appropriate number of books to the TotalBooks field in the Books table.

The trick is how to pass the correct parameters to the query - I'll have to look into the simplest way to do that.

3. Checking If Books are Out
Probably the easiest thing is to use the DLOOKUP function to test whether there is an item checked out. This would be a field on the Check Out form:

    =DLookUp("[TransactionCode]","tblTransactions","([MemCode] = " & Me!MemCode & " AND [Returned] = No)")

Then you could reference this to display a text box to give the user a warning message. If you really wanted to absolutely block the check out, you would need to write some VBA code to disable the check out button.

4. Other things
It would really, really be helpful if you have primary keys for each table. A primary key uniquely identifies each record. The BookCode field in the Books table could easily be a primary key. You should also have something similar in the Transaction and Purchase tables.

Hope that this helps. Sorry if it is too vague or too detailed or just not coherent enough. Some of these things are complex and it just takes additional time and experience to be able to implement.

If you still have questions, please post them and I'll do my best to help.

Regards,

- g

 

[itsmystie]

Updating fields

Hi!

Thank you very much for your prompt reply and sorry for the delay in replying you. As I said in my previous post, am new to Access and VBA as well. SO basically, i got a bit difficulty understanding the coding you posted even if you have documented it very well. I think perhaps the best way is for me to first learn a bit of VBA. So can you, or anyone, please recommend me any site that offers good VBA for Access tutorials? Thank in advance.