Delete record button - move from one form to the other button

[annietn34]

Okay - I read tons - something like 50 books or more a year and sometimes I've had a book so long I will go to the store and buy it again not remembering that I've read it before until I get into the book for a while then realize hey, I've read this and yes I then find it on my library shelf!!

So I decided to set up a database - on my switchboard it shows view library, view wish list, view currently reading - all of which works fine - forms set up for everything, etc.

also on the switchboard are some admin functions such as add record to library and add record to wish list which also works fine thanks to help on here

Now I am trying to add a delete record button to delete from the library or deliete from the wish list/current reading list

I would also like to add a button that would move a book from my currently reading list to the library once I am finished with that book, etc.

Any way I can accomplish this without using code (I am no programmer but at this point would love to learn to make this easier)!

Thanks again and I'm sorry for so many questsions - I am just so new to this but I am learning so much!

Annie

 

[Rich]

Simply add a yes/no field to the table, then filter the results depending on wether you've read it or not, much easier than moving records around.

 

[Parker]

Now I am trying to add a delete record button to delete from the library or deliete from the wish list/current reading list

I would also like to add a button that would move a book from my currently reading list to the library once I am finished with that book, etc.

Any way I can accomplish this without using code (I am no programmer but at this point would love to learn to make this easier)!

Thanks again and I'm sorry for so many questsions - I am just so new to this but I am learning so much!

Annie

Hi Annie

It's a bit quiet here today and I'm not an Access Guru but I will try and help where I can.

First, if you want an easy delete record option I would include a command button on the form that you use for input. You can do this by adding a command button in design view and using the wizard that pops up as you add the button. If you go to 'record operations' in the wizard there is an option to delete a record. So that would solve that one.

However, you don't say if you have a search facility included in your forms. If the library is quite large, which I suspect it may be, it could take you some time to find the record to delete. If this is the case you may like to get into a little bit of code. It's really not that difficult and using VBA you can add all sorts of useful utilities.

Your second problem, moving a record from one table to another, is a little more involved.

Lets breakdown what is actually necessary to achieve this. Basically you need to do three things.

1. Find the record to be moved
2. Add this record to another table
3. Delete this record from its original table

This could be achieved in a number of ways but I think that a routine in code would be more efficient and easier to handle. However, I am assuming, from your post, that you own all the books that you read?

If this is the case then consider this:

If you have purchased a book then it already belongs in your library. It may be better to add the book to your library list and then tell the db that you are reading it.

I would add a control to your form to say that you are reading a particular text. Then base your reading list on a query. This would also remove the necessity to delete a record from the reading list because this list would then be dynamic and only reflect information supplied elsewhere. (i.e. tell the library that you are no longer reading a book and it will not appear on the reading list).


The other thing that I can see you may need to do is to tell your wish list that you have purchased a book. Again this could be done by providing a control on the appropriate form and using that control to fire a routine to catalogue the book in the library and remove it from the wish list. This can be done with a simple append routine to first add to the library list followed by a delete record in the wish list. Again though, this would be much better in code.

Hope this helps a bit.

 

[Pat Hartman]

You should have only a single table for books. You should not have three separate tables. The one table will simply have a status field. In library, currently being read, on wish list, on loan, missing, etc. If you use the on loan status, you can also add a field to identify who borrowed the book and when they borrowed it. If you want to keep history of loans, you'll need a separate table.

By using a status field, all you need to do is to update the status field. There is no worry about adding a record to one table and deleting it from another. Your queries should make use of the status field to restrict the rows returned.

 

[annietn34]

You should have only a single table for books. You should not have three separate tables. The one table will simply have a status field. In library, currently being read, on wish list, on loan, missing, etc. If you use the on loan status, you can also add a field to identify who borrowed the book and when they borrowed it. If you want to keep history of loans, you'll need a separate table.

By using a status field, all you need to do is to update the status field. There is no worry about adding a record to one table and deleting it from another. Your queries should make use of the status field to restrict the rows returned.

Sorry if I am being stupid but I don't understand how I can put all of this in one Table - I have this tables:

1) Current - Book name, Author, Began, Finish, No. Pages

2) Library - Book name, Author, No. Pages, Comments

3) Wish - Book name, Author, comments

So how can I do this with only one - I will have to change everything then too if I change that, right?

 

[Pat Hartman]

Please don't quote everything when replying. Choose the Reply button on the left, below the last entry rather than the reply button inside the post.

Yes, everything goes into a single table. Some of the fields will be empty. It is far better to have a few empty columns than to have three tables. Don't forget to add the status field.

BTW, a book may have many authors and so the Author info belongs in a separate table. There are examples of book databases that you can download as templates. You might start with one of them to see how to properly structure the tables.

 

[annietn34]

Another Question

Sorry - I have seen Access for a number of years - used databases that were created in Access and maybe updated a table here and there but that database was already made - I never attempted to make one but now I am for the very first time only 1 week ago - I don't think I'm doing too bad when you take into consideration I've only been doing it for a week - I've got the skeleton of my database set up.

This table thing is SO CONFUSING me - What is a status field? I have one author say Stephen King - I have his book Misery on my library list with info - then I have his book say the new Darktower book on my wish list with some of the info that is on the library table but also some different info - then another book on my current reading book by Stephen King say Cujo - with different library fields for this one also -

I don't understand how I can get all of this into one table with each table needing to show different fields and information and I can't find anywhere what a status field is.

I am like a kindergartener here

Oh and sorry about hitting the wrong reply button - I promise I will never do it again! ha ha

Annie

 

[Parker]

This table thing is SO CONFUSING me - What is a status field?
Annie

You might use a checkbox as a field to indicate the status of an item. I.e. you could use a check box to indicate that you are reading a book.......... If it is checked then that book is being read if unchecked it is not. You can also group controls like this to indicate a number of different things. Get the idea?

What Pat is basically saying is that you could rethink the structure of your db. There are, again as Pat said, a few examples available that you could use either as a template or just to study and learn from. But I will try and explain a little bit about how your structure could look.

Concentrate first on the information you want to store about each book. It makes little difference as to whether you are reading a book, storing it or just dreaming about owning it.

Every book has an author.
Every book has a publisher.

Each author may have written many books.
Each Publisher will have published more than one author.

So; both publishers and authors should have their own tables. That way you only need store the information about each author and each publisher once.

So authors need their own table as do publishers.
You then will want to store information about each book such as title, date published, category maybe number of pages and other such information.

So now we have 3 tables

Authors
Publishers
Books

These tables are linked together with a system of 'keys' so that each book is linked with an author and a publisher.

The linking of these tables needs a little careful thought because:

Each author may have written many books they may also have been published by more than one publishing house.

Each Publisher will have published many books by many different authors

But, each book will have only one author and one publisher.

I have not, deliberately, introduced other possible complications for the sake of clarity but hopefully this will be enough to give you an idea of how it works.