Data entered in tables appears on form but form will not allow me to enter data!!

[PATRICKPBME]

I created a form from 3 tables. When I open the form initially it shows the information from the tables just as I needed it to. However, when I attempt to enter data in the form I am not allowed to do so. I can enter the information in the table and it will appear on the form but I am unable to enter any of the information on the form itself which, of course, defeats the purpose of the form. When I set up the table relationships, I have my "main" table with the contact # as the primary key. In the other two tables, the contract # is the foreign key to which the primary table key is linked. I have formatted table relationships with and without "enforcing ref integrity" with no luck. The connection from the main table to the other two tables is a one to many. One doctor (1st table) to many docs (2nd table) and many events (3rd table).



When I try to enter data on the form itself, I get the "ding" that one would get trying to enter data while in layout view.

Thanks in advance!

[andy49]

Is there a warning at the bottom of the screen saying data cannot be entered?


Sent from my iPhone using Tapatalk

[jwhite]

When editing data your form should have a RecordSource that is limited to returning data from a SINGLE table. I presume yours has all three in a query as the RecordSource. To edit data in other tables related to the main table, add a subform for each child table. Use the subform control Child/Master links to link the main form with the subforms.

[PATRICKPBME]

No warning and the search function works.

[PATRICKPBME]

In design view I opened property sheet, data, record source. Sure enough, as you mentioned it appears all three are listed. I do feel like I am getting a little better with this so I will take a run at putting in sub forms. I did want to ask if there might be another way. Two of the forms (sub) contain information that does not change that often. The master form is the one where data (events) are entered often. Could I just make separate data entry forms for the two other tables? If so, because I have a relationship between the master and the child tables will information I enter on those child forms still appear on the master? Finally, to "fix" the record source error, do I simply delete any information not sourced from the master table?

[jwhite]

You can create separate forms to edit each table. Of course, forms editing child tables will only be able to edit records where the Primary Key exists in the parent table. Regards to the "Fix", yes, removing the child tables from the SQL statement and removing any Controls for fields in the child tables should resolve it. As always when working on new ideas, make a backup of your database first.

[PATRICKPBME]

Thanks. I have backed it up before tinkering with it. So, if my primary table is "physicians" than I should delete anything in the record source not found in that table? Also, when you referenced the primary key, currently the master table has the primary key which is the foreign key in the child tables. Or are you saying it should be the other way around?

[jwhite]

Correct. The master or parent table will hold the Primary Key, and would be the Foreign Key in any child tables. If you will go the mainform-subform route, your Primary Key will be in the Master Link property, and your Foreign Key will be in the Child Link property of the subform control.

[PATRICKPBME]

Thank you very much. I am going to create a separate form and then see if I can figure out how to add it as a sub form to the main form in design view. I will let you know how it goes and appreciate all of your help. I will make solved when I get this thing running!

[Micron]

When editing data your form should have a RecordSource that is limited to returning data from a SINGLE table. I presume yours has all three in a query as the RecordSource. To edit data in other tables related to the main table, add a subform for each child table. Use the subform control Child/Master links to link the main form with the subforms.

Disagree entirely. It is quite common to base a form on a query, and IMHO is preferable to having subforms for singular tables. Subforms are for the many side of a one to many relationship, not for related one to ones. Given that we strive to normalize tables, it pretty much enforces the use of queries to meld together related data, and queries are the prime method for bringing tables together.

There's more than one reason a form can't be updated, one being that a form properties such as Allow Edits has been set to No. Another is the type of query. A Totals, Union or Crosstab query is not updatable IIRC. For me, the mantra is after tables come queries. If you can't gather, edit or delete the assembled data, then there's probably something wrong with the table schema or query design, notwithstanding query types just mentioned.

NOTE I'm not saying a subform isn't the proper solution in this case, give that there are 2 tables with many to many between them and a one to many on the other side. Just disagreeing with the statement that all forms should be based on one table.

[jwhite]

I didn't say all forms should be based on one table...

[PATRICKPBME]

I will take a look again and see if Allow "Edits" might be set to "no". Just to be clear, I have 1 table with names. The two other tables are "events" and "documents". I have a one to many between the Name table and each of the other two tables. Primary key in Name is foreign key in the two other tables.

[Micron]

I didn't say all forms should be based on one table...

Sorry, that was my interpretation of "When editing data your form should have a RecordSource that is limited to returning data from a SINGLE table"

[jwhite]

In the context of this thread where data is being edited, the statement is to say the fields that are selected in the Recordsource should be of only one table.

[PATRICKPBME]

Where you say "Sub forms are for the many side of a one to many relationship, not for related one to ones" are you saying that I need to have subforms when doing a one to many? I ask because no matter what I have done I can't get this to work for me. Here is the detail of what I have attempted. As mentioned, I have 3 tables. One with names (main table) one with events and one with documents. The common field for all three is the contact number. That is the primary in the names table and foreign in the other two. I set the relationship between the name table and the other two as one to many.

Initially, when I was trying to build my form, I went to form design and started dragging fields where I wanted them. While I was able to create the desired form, as mentioned previously it was useless because it would not accept data. My second attempt now has been to create a query with the needed field from each table. The query runs fine but when I try to create a form from it I run into the same problem. I guess I could just add all of the fields from the other tables to the names table, but my understanding has been that it is not recommended to have to may fields in one table.