Use form button to change value in table

[StrangePowers]

Hi there,

I'm building my first database, to manage a small library.

There are multiple copies of certain books, which is represented in the books table in the copies field, which has a certain number in each record.

In the new loan form (which is bound to the loan table) want the save button to automatically subtract 1 from the copies field for the corresponding record. I suspect this can be with a macro, but since I'm totally new to this I'm not quite sure.

Any help would be greatly appreciated.

 

[Ranman256]

Make an update query,that will add/reduce the qty.
The button will execute the query,via macro:

Docmd.openquery "quMyQuery"

 

[bob fitz]

Hi there,

I'm building my first database, to manage a small library.

There are multiple copies of certain books, which is represented in the books table in the copies field, which has a certain number in each record.

In the new loan form (which is bound to the loan table) want the save button to automatically subtract 1 from the copies field for the corresponding record. I suspect this can be with a macro, but since I'm totally new to this I'm not quite sure.

Any help would be greatly appreciated.

Saving the result of a calculation in a table is not usually recommended. Better to do the calculation in a query/form/report whenever and wherever it is required.

 

[jdraw]

My guess is your table structure is not fully reflecting your requirement. The fact that you have copies of books and loan them, indicates you could/should have a copyNo or similar in your table to uniquely identify each item in the table..
Perhaps you could show us your table set up. There are several articles for Database Planning and Design in the link in my signature.

 

[StrangePowers]

My guess is your table structure is not fully reflecting your requirement. The fact that you have copies of books and loan them, indicates you could/should have a copyNo or similar in your table to uniquely identify each item in the table..
Perhaps you could show us your table set up. There are several articles for Database Planning and Design in the link in my signature.

It seems I'm in waaaay above my head here. I first had my database the way you described it, but then someone here on the forum explained to me why I should change my database so it would have the calculated field... (https://www.access-programmers.co.u...form-has-book-duplicates.310829/#post-1685635)

To be clear: for our library, it isn't important which member has which copy, since it's only a small library exclusively for school employees.

Saving the result of a calculation in a table is not usually recommended. Better to do the calculation in a query/form/report whenever and wherever it is required.

Taking the above into account, would it be reasonable to have field in the Books table like "TotalCopies", as to then substract and add somewhere else each time a book is loaned? Or would that be overly difficult?

 

[isladogs]

What you suggest wouldn't be overly difficult.
However, I wouldn't recommend saving the copies in the books table either.
Even if its a small library, surely you want to have a record of who has borrowed what.
Each book should have a unique ID and when borrowed that is saved in a separate table with the name or ID of the person borrowing it together with a date.
Then you can use a query to identify how many of each book are on loan or the number still available.

Its a little more effort now but will be much more useful later

 

[Gasman]

It seems I'm in waaaay above my head here. I first had my database the way you described it, but then someone here on the forum explained to me why I should change my database so it would have the calculated field... (https://www.access-programmers.co.u...form-has-book-duplicates.310829/#post-1685635)

Unless I misread that thread, Plog only suggested that you held the number of copies in tblBooks and subtracted the sum of those loaned out?
That is not a calculated field, but standard DB process.?

So I believe you did not undertstand what Plog was writing?

 

[StrangePowers]

Unless I misread that thread, Plog only suggested that you held the number of copies in tblBooks and subtracted the sum of those loaned out?
That is not a calculated field, but standard DB process.?

So I believe you did not undertstand what Plog was writing?

Oh man... can't believe I totally my database just because I misread that post.... :-( Oh well: at least I'll get some practice reworking it once again.

Even if its a small library, surely you want to have a record of who has borrowed what.
Each book should have a unique ID and when borrowed that is saved in a separate table with the name or ID of the person borrowing it together with a date.
Then you can use a query to identify how many of each book are on loan or the number still available.

Like I said, for us knowing that there are 3 copies of Book X, and that persons A, B, and C all have a copy of Book X suffices. Of course, all loans are stored in a seperate table, where BookID, BorrowerID, and the date are stored.