Full Version: Data Entry in Form/table set-up
UtterAccess Forums > Microsoft® Access > Access Forms
roomass
Hi,
I have a problem with entering data into my form (although it may also stem from my tables). Here's the layout of my database. I have a form which is used to enter publication data. I also have a subform which is used to enter the multiple names of the authors for that single publication. I've read about Many-to-Many relationships and I've created a LINK table between my two other tables.
This is how the 3 tables with their PK/FK are connected.
tblDataEntry
PK_ReportNumberID------l

tblLINK
FKReportID-----------------l
FKAuthorID----------l

tblAuthors
PKAuthorNameID---l
AuthorName (is a text field)
Now in the relationship both PKs are autonumbers, both FKs are numbers (Long Interger) and they both enforce referential integrity.
Now my problem...
I want to be able to enter author names into the subform and have it generate in the AuthorName field. Therefore ever author name entered into a each record would be associated with that record. I read that this can be done with a combo box, which would allow me to type in a new authors name but also select from a list of previous authors, if that author has helped write this new report. The problem I have is that
A) It displays "Field cannot be updated" when i try to type in a name.
B) I cannot get the actual names of the authors to display, only their ID#.
C) When have data in my author table, it does not update the FK in the LINK table to establish a link between the records.
I have a feeling that C may be causing some of the other problems. However I have looked at similar Many-to-Many relationships in sample databases, as well as how the subform works for the forms but i still can't get it to work in my database.
Please help, this is the last problem i've come across while building this database and once I get the author section figured out everything else can be finished up.
Thank you!
Jack Cowley
Welcome to Utter Access Forums!!
will assume many authors can be in many publications and many publications can have many authors:
tblPublications
PublicationID (PK and auto)
PublicationName
...other necessary fields...
tblAuthors
AuthorID (PK and auto)
LastName
FirstName
...other necessary fields...
tblPublicationsAndAuthors
PublicationsAndAuthorsID (PK and auto)
PublicationID (FK to tblPublications)
AuthorID (FK to tblAuthors)
DateEntered
...other necessary fields....
Let's assume adding Authors to Publications. A simple way is to create a datasheet view form based on table "tblPublicationsAndAuthors". In this form change the AuthorID field from a Text box to a Combo box and base the combo on "tblAuthors" or a query based on "tblAuthors" so you can sort alphabetically.
Now you have your Authors form so you can now create a form based on "tblPublications". When you have add the necessary fields use the Wizard to add the datasheet view subform you just made. Access will link the forms for you. Now you can select a Publication in the main form and select Authors from the combo box in the subform and you should be good to go.
You can reverse this and make a form based on Authors and a subform on Publishers if you prefer or you can have both...
HAs for adding Authors that are not in the combo box use the combo boxes Not In List event to do that.
hth,
Jack
fkegley
You should have a main form that displays the publication data. You should have a subform control that displays the Author data for a particular publication. That will be your tblLink. The Link Master Fields for the subform control should be PK_ReportNumberID. The Link Child Fields for the subform control should be FKReportID. Now the two should sync up so that navigating in the main form, causes fetching of the corresponding records in the tblLink. The tblAuthors should be used as the row source of a combo box on the subform.
believe the problem is that you are trying to type a piece of text, the author name, into the bound column of the combo box which is more than likely the PKAuthorNameID. That's not going to work, especially if the PKAuthorNameID is AutoNumber, which it should be.
What you should do is provide the user with an Add New button on the subform. This button should open a form based on the tblAuthors to a new record, a command button on this form can then be clicked that will append the new record to the tblAuthors, and requery the combo box on the subform to put the name in the author combo box.
roomass
Thank you sooooo much! I really appreciate the help from both of you. I got everything to work and can now add multiple authors to my publications and have them all link up
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.