Solved My data appears in the FORM but ONE field is not saved in the TABLE. (1 Viewer)

[bmaccess]

Hi there. When I run my application the data entered appears in my FORM but not all fields are saved in my TABLE. The field in that are not saved is my BookTitle field which is derived from the combox StudentBookIssue_ID.
[BookTitle]
Control Source : = StudentBookIssue_ID .Column(1) .

The FORM:

The TABLE: LearnerBookIssue

Please any suggestions how to solve the problem or even a solution will be appreciated.
I am attaching my database if anybody would like to have a look at it. Thanks so much for all your help.
Am I making basic fundamental errors which I am not aware. Any suggestions for any good books to become better in Access will be a great help. Thanks.

 

[GaP42]

Before trying to fix the functionality of the combo there are some fundamental issues in the database design you need to address.
1. There appear to be some issues with the construction of the tables that hold the data (database normalisation) eg. the table Books contains a mixture of the book title type information and the numbers of books held in stock including items that are probably calculated.
2. Probably a more immediate issue is that you have set the Primary Key of a number of tables inappropriately - eg Student Name (what happens if two students have the same name?), Book title (same issue) ... While these may be seemingly good natural keys (identifiers for the entity in question) there can arise circumstances where they do not suffice. You can establish a surrogate key as the primary key (where you let the database manage the assigned unique integer value (autonumber) to each record in the table. (this is common practise). BookTitle and Book Number both appear in two tables and neither table is directly linked - ie no direct relationship - so there is redundant information that would need to be managed /maintained in two tables independently of each other - this is clearly counter to good database design.
3. there is more - but design / normalise first.

Try to get a handle in database normalisation:
Various resources: database normalization - Google Search
text / youtube / and levels of sophistication.
Or use the suggested reading list here: UtterAccess.com

and apply to your problem space. You also might look for sample data models for library / book borrowing, school library etc to see what others have thought about that - and adapt as needed.

Solving your stated problem would only be a temporary fix unless you fix the fundamentals first.

 

[bmaccess]

Thanks for the advise. Much appreciated.

Before trying to fix the functionality of the combo there are some fundamental issues in the database design you need to address.
1. There appear to be some issues with the construction of the tables that hold the data (database normalisation) eg. the table Books contains a mixture of the book title type information and the numbers of books held in stock including items that are probably calculated.
2. Probably a more immediate issue is that you have set the Primary Key of a number of tables inappropriately - eg Student Name (what happens if two students have the same name?), Book title (same issue) ... While these may be seemingly good natural keys (identifiers for the entity in question) there can arise circumstances where they do not suffice. You can establish a surrogate key as the primary key (where you let the database manage the assigned unique integer value (autonumber) to each record in the table. (this is common practise). BookTitle and Book Number both appear in two tables and neither table is directly linked - ie no direct relationship - so there is redundant information that would need to be managed /maintained in two tables independently of each other - this is clearly counter to good database design.
3. there is more - but design / normalise first.

Try to get a handle in database normalisation:
Various resources: database normalization - Google Search
text / youtube / and levels of sophistication.
Or use the suggested reading list here: UtterAccess.com

and apply to your problem space. You also might look for sample data models for library / book borrowing, school library etc to see what others have thought about that - and adapt as needed.

Solving your stated problem would only be a temporary fix unless you fix the fundamentals firs

 

[GaP42]

Happy to review / discuss your progress on the design. Keep posting - outline what you are trying to achieve with the db. Do you have users to talk thru what is wanted? Work out the entities - subjects - about which you need to hold data and then think about the data that needs to be associated with each to meet your needs and how it will be sourced and maintained. Develop a normalised data model and post it for review (with a statement of the scope).

 

[bmaccess]

Hi GaP42.
I reverted back to my original ERD as follow.

What I am trying to do is scan or enter in the BookNo then the BookTitle must be automatically be entered in the LearnerBookIssue. I started the application not with this idea in mind. I am trying to change my application to achieve this. Ok, I think is not the correct way to go about this, but I need this to happen otherwise I need to redo my application and redo lots of forms and reports. I will appreciate any suggestions or any help. Thanks
I will keep you up to date and discuss the progress.

 

[GaP42]

What you are trying to do, as in scan (read a barcode) into "BookNo" is not the focus of your what you application is about. I would guess, from what is shown, that your db application attempts to track the borrowing of books by students. The scanning process is simply a mechanism by which you can enter data in Book No.
First consider what your central subjects are: Student and Book. A student can borrow multiple books, a book can be issued to multiple students (over time). Because this is a many-to-many relationship, it cannot be directly represented in a RDBMS - a junction table representing the issue of a single book to a student - is needed, as you have represented in LearnerBookIssue. However a closer look is needed.

Student: A student has a firstname, lastname. If you are not concerned with the family you may also assign a homeaddress to the student, and a contactphoneno. But what if there is more than one, what about email or other contact methods. Do you need to store and maintain that information? I do not know what "cemis" is so cannot offer any advice.
A student belongs in a grade and stream... but are these attributes of the student? Do you need to know the class/ grade of a student? But students may be enrolled in multiple classes / grades (Are Class and Grade the same thing in your school structure?) and classes/grades also have multiple students enrolled. That implies again a Many-Many relationship that is resolved through an EnrolledinGrade table between Student and Grade. A stream may consist (I think of multiple grades or classes)

Set up each table with a Primary Key as follows StudentID for the Student table, GradeID for the Grade Table, StreamID for the Stream table, EnrolledInID for the EnrolledinGrade table. Depending on other decisions you make you may also need a table for Student ContactDetails.
Define attributes (fields) that belong to each of these tables.

Books: You have two tables currently: Books and Book Number - Why? Do you track individual books. If you have multiple copies of a book are you able to tell which is which? (eg is there a Dewey Dec No with a suffix that indicates the copy?) If they are physically individually identifiable then you Book table does not seem viable - there would be no need to have fields to hold number of copies, instock (as a number), totalinstock, issued (each as a number). These numbers, generally appear to values that can be calulated on the fly based upon the trasanctions that occur when books are issued and returned
BookNumber - is this the assigned BookNumber (like a DeweyDecimal number)
If you are tracking individually identifiable books then the Book table will have a PK of BookID set as an autonumber field by the Access / Jet RDBMS. However you have Author and Publisher. An Author may produce/write multiple books, and a book may be co-authored. So you may need to create and Authors table (with AuthorID and AuthorName) and a junction table - BookAuthor (BookAuthorID). A publisher publishes many books and a particular book is printed by only on publisher. So a Publisher Table is needed with PublisherID and PublisherName. Assign the attributes to these tables.

(PS Just in case - any names for tables, or fields or other objects that you create in Access should be assigned names in CamelCase - at least avoid spaces in the names.)

Once you have set up the tables consider how the tables relate to each other - some of the PK will be used as foreign keys in the junction tables. The Access relationships window will then be used to define the relationships between the entities - tables.

Look in more detail at the LearnerBookIssue table attributes. Some changes will be needed so that the PK identifier for the sstudent and Book are used rather than student name and book title. Is "Returned" needed if you have date returned?

Get through this and show what you have then.

 

[Pat Hartman]

BookTitle is not guaranteed to be unique. You need to use an ID as the primary key.

 

[GPGeorge]

I have two copies of one book (purchased by accident). I can 100% confirm that the titles are not unique.

 

[bmaccess]

What you are trying to do, as in scan (read a barcode) into "BookNo" is not the focus of your what you application is about. I would guess, from what is shown, that your db application attempts to track the borrowing of books by students. The scanning process is simply a mechanism by which you can enter data in Book No.
First consider what your central subjects are: Student and Book. A student can borrow multiple books, a book can be issued to multiple students (over time). Because this is a many-to-many relationship, it cannot be directly represented in a RDBMS - a junction table representing the issue of a single book to a student - is needed, as you have represented in LearnerBookIssue. However a closer look is needed.

Student: A student has a firstname, lastname. If you are not concerned with the family you may also assign a homeaddress to the student, and a contactphoneno. But what if there is more than one, what about email or other contact methods. Do you need to store and maintain that information? I do not know what "cemis" is so cannot offer any advice.
A student belongs in a grade and stream... but are these attributes of the student? Do you need to know the class/ grade of a student? But students may be enrolled in multiple classes / grades (Are Class and Grade the same thing in your school structure?) and classes/grades also have multiple students enrolled. That implies again a Many-Many relationship that is resolved through an EnrolledinGrade table between Student and Grade. A stream may consist (I think of multiple grades or classes)

Set up each table with a Primary Key as follows StudentID for the Student table, GradeID for the Grade Table, StreamID for the Stream table, EnrolledInID for the EnrolledinGrade table. Depending on other decisions you make you may also need a table for Student ContactDetails.
Define attributes (fields) that belong to each of these tables.

Books: You have two tables currently: Books and Book Number - Why? Do you track individual books. If you have multiple copies of a book are you able to tell which is which? (eg is there a Dewey Dec No with a suffix that indicates the copy?) If they are physically individually identifiable then you Book table does not seem viable - there would be no need to have fields to hold number of copies, instock (as a number), totalinstock, issued (each as a number). These numbers, generally appear to values that can be calulated on the fly based upon the trasanctions that occur when books are issued and returned
BookNumber - is this the assigned BookNumber (like a DeweyDecimal number)
If you are tracking individually identifiable books then the Book table will have a PK of BookID set as an autonumber field by the Access / Jet RDBMS. However you have Author and Publisher. An Author may produce/write multiple books, and a book may be co-authored. So you may need to create and Authors table (with AuthorID and AuthorName) and a junction table - BookAuthor (BookAuthorID). A publisher publishes many books and a particular book is printed by only on publisher. So a Publisher Table is needed with PublisherID and PublisherName. Assign the attributes to these tables.

(PS Just in case - any names for tables, or fields or other objects that you create in Access should be assigned names in CamelCase - at least avoid spaces in the names.)

Once you have set up the tables consider how the tables relate to each other - some of the PK will be used as foreign keys in the junction tables. The Access relationships window will then be used to define the relationships between the entities - tables.

Look in more detail at the LearnerBookIssue table attributes. Some changes will be needed so that the PK identifier for the sstudent and Book are used rather than student name and book title. Is "Returned" needed if you have date returned?

Get through this and show what you have then.

Hello GaP42.

This part is 100% correct what you said.
"What you are trying to do, as in scan (read a barcode) into "BookNo" is not the focus of your what you application is about. I would guess, from what is shown, that your db application attempts to track the borrowing of books by students. The scanning process is simply a mechanism by which you can enter data in Book No."

I can see you really passionate about databases and very thorough in your analogy. Great to know you and all those out there who love Access.

The scanning part was never a focus of my program. The important part is the tracking of books in our school.
I am just trying to throw in this scanning part in to make it a bit easier when issuing books.
I made 3 videos on how the system works. I did put it on Youtube but removed it again. When I put it back on Youtube I will send the link and if you have time you can have a look at it. It would be great if people can review it and give their honest opinions. The database I am posting is only a part of it.
Thanks to all.

 

[bmaccess]

What you are trying to do, as in scan (read a barcode) into "BookNo" is not the focus of your what you application is about. I would guess, from what is shown, that your db application attempts to track the borrowing of books by students. The scanning process is simply a mechanism by which you can enter data in Book No.
First consider what your central subjects are: Student and Book. A student can borrow multiple books, a book can be issued to multiple students (over time). Because this is a many-to-many relationship, it cannot be directly represented in a RDBMS - a junction table representing the issue of a single book to a student - is needed, as you have represented in LearnerBookIssue. However a closer look is needed.

Student: A student has a firstname, lastname. If you are not concerned with the family you may also assign a homeaddress to the student, and a contactphoneno. But what if there is more than one, what about email or other contact methods. Do you need to store and maintain that information? I do not know what "cemis" is so cannot offer any advice.
A student belongs in a grade and stream... but are these attributes of the student? Do you need to know the class/ grade of a student? But students may be enrolled in multiple classes / grades (Are Class and Grade the same thing in your school structure?) and classes/grades also have multiple students enrolled. That implies again a Many-Many relationship that is resolved through an EnrolledinGrade table between Student and Grade. A stream may consist (I think of multiple grades or classes)

Set up each table with a Primary Key as follows StudentID for the Student table, GradeID for the Grade Table, StreamID for the Stream table, EnrolledInID for the EnrolledinGrade table. Depending on other decisions you make you may also need a table for Student ContactDetails.
Define attributes (fields) that belong to each of these tables.

Books: You have two tables currently: Books and Book Number - Why? Do you track individual books. If you have multiple copies of a book are you able to tell which is which? (eg is there a Dewey Dec No with a suffix that indicates the copy?) If they are physically individually identifiable then you Book table does not seem viable - there would be no need to have fields to hold number of copies, instock (as a number), totalinstock, issued (each as a number). These numbers, generally appear to values that can be calulated on the fly based upon the trasanctions that occur when books are issued and returned
BookNumber - is this the assigned BookNumber (like a DeweyDecimal number)
If you are tracking individually identifiable books then the Book table will have a PK of BookID set as an autonumber field by the Access / Jet RDBMS. However you have Author and Publisher. An Author may produce/write multiple books, and a book may be co-authored. So you may need to create and Authors table (with AuthorID and AuthorName) and a junction table - BookAuthor (BookAuthorID). A publisher publishes many books and a particular book is printed by only on publisher. So a Publisher Table is needed with PublisherID and PublisherName. Assign the attributes to these tables.

(PS Just in case - any names for tables, or fields or other objects that you create in Access should be assigned names in CamelCase - at least avoid spaces in the names.)

Once you have set up the tables consider how the tables relate to each other - some of the PK will be used as foreign keys in the junction tables. The Access relationships window will then be used to define the relationships between the entities - tables.

Look in more detail at the LearnerBookIssue table attributes. Some changes will be needed so that the PK identifier for the sstudent and Book are used rather than student name and book title. Is "Returned" needed if you have date returned?

Get through this and show what you have then.

Hi GaP42. I hope you are doing fine. I have been sitting the last few days figuring how to solve my problem.
I used my original ERD as intended.
I decided to make a new Form to as shown below which will update my previous form when the records are updated.
1) Because only the BookTitle field did not want to save, I decided to update the a complete record using the following code.

For Each varItem In [Forms]![frmIssueBooksBarcode].List45.ItemsSelected
rs.AddNew
rs!BookTitle = [cmbBookNoTitle].[Column](1)
rs!Surname_Name = [List45].[Column](0)
rs!StudentBookIssue_ID = [cmbBookNoTitle].[Column](0)
rs!IssuedLearner.Value = True

rs.Update

Next varItem

2) The scanning works as well. The scanning will be in the unbounded text box. So I scan the book barcode which is now registered in the BookNumber table. It gets picked up. Display the name and number of the book which I extracted with
rs!StudentBookIssue_ID = [cmbBookNoTitle].[Column](0)
rs!BookTitle = [cmbBookNoTitle].[Column](1)

and updated the whole record.
Thanks for all you help out there.