Subform won't allow data entry/display form fields when record is empty (1 Viewer)

[Missomissou]

Hi, I have a user input form for project records. It contains a few different subforms. Two are working fine, display data and allow data entry. The third, Grants and Agreements, does neither. It will display all fields within the form, as long as there is data entered in at least one of the fields. But I cannot edit or add new records via this subform. I have played around with the Data Entry property on the form and subform (tried all combinations), and with the relationship join direction, as well as checking to make sure the record source for the form and the control sources for the fields are all correct (they appear to be). I also unhid the PK ID for the Grants and Agreements table, just to see if there was some reason that needed to be visible on the subform. All to no avail. Any ideas?

 

[June7]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

Could try rebuilding form from scratch. Sometimes weirdness is corruption that can't be fixed otherwise.

 

[Missomissou]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

Could try rebuilding form from scratch. Sometimes weirdness is corruption that can't be fixed otherwise.

Hi--here's the cleaned up database. I just noticed as I was scrolling back through records that there is legacy data in the Agreements field within the Grants and Agreements subform. I'm confounded as to why that information is showing up there when it has been deleted (or mostly so) from the table. Something that might be helpful for you to know is that I recently I ran the table analyzer on the projects table. Access split it into 3 tables, a new Projects table, External Partners, and Grants and Agreements. Access also created a query to keep all the existing forms/queries working. So it's possible that legacy data showing up may have something to do with that event. And perhaps also why the grants and agreements subform isn't allowing data input? In any case, thank you for being willing to look at this.

 

[Pat Hartman]

Your schema is incorrect. I don't know how to fix it because I don't understand it. I can tell it is incorrect because I can see pieces of data that can cause conflicts. For example ALWRIPProjectID (Never use an ID suffix except when the ID is an autonumber. It is just confusing) exists in both tblAPolicyReviews and tblASciDProducts but those two tables are related by PolicyReviewID That means that the tables related by PolicyReviewID could each point to a DIFFERENT instance of "new". That is an error.

Another is to have ResearcherID as a single instance in two tables when you also have a m-m relationship. If there are roles that researchers play, they are identified in the m-m table.

I never got to the form problem. Please don't make us open every single form to figure out what form you are having trouble with. At least tell us the name of the form with the problem.

 

[mike60smart]

Hi--here's the cleaned up database. I just noticed as I was scrolling back through records that there is legacy data in the Agreements field within the Grants and Agreements subform. I'm confounded as to why that information is showing up there when it has been deleted (or mostly so) from the table. Something that might be helpful for you to know is that I recently I ran the table analyzer on the projects table. Access split it into 3 tables, a new Projects table, External Partners, and Grants and Agreements. Access also created a query to keep all the existing forms/queries working. So it's possible that legacy data showing up may have something to do with that event. And perhaps also why the grants and agreements subform isn't allowing data input? In any case, thank you for being willing to look at this.

Well it looks like you have not learnt anything from the 2 previous examples I have uploaded.

ALL Tables should have an Autonumber PK. You are using Text DataTypes as a Primary Key.
In your tblAALWRIProjects table you used the following as Primary Keys. See attached screenshot.

To solve your problem I renamed ALWRIProjectID to ALWRIProjectNr
I then added added a New Primary Key Autonumber Datatype named ALWRIProjectID

In your related table tblAGrantsAgreements I carried out the same process as follows:-
I renamed ALWRIProjectID to ALWRIProjectNr
I then added added a New Primary Key Autonumber Datatype named GrantAgreementID

I then created a 1 to many relationship between tblAALWRIProjects_New and tblAGrantsAgreements
using ALWRIProjectID

Study the relationship diagram so that you understand how the two tables are related.

Your original PK which stored just the Text values can now be removed from BOTH Tables.

You will need to redo the relationship to all related tables to tblAALWRIProjects

 

[mike60smart]

The database I uploaded is corrupt in some way and I do not have the time to fault find.

I have attached a New revised version which solves the OP's question.

My comments still apply.

 

[Missomissou]

Well it looks like you have not learnt anything from the 2 previous examples I have uploaded.

ALL Tables should have an Autonumber PK. You are using Text DataTypes as a Primary Key.
In your tblAALWRIProjects table you used the following as Primary Keys. See attached screenshot.

To solve your problem I renamed ALWRIProjectID to ALWRIProjectNr
I then added added a New Primary Key Autonumber Datatype named ALWRIProjectID

In your related table tblAGrantsAgreements I carried out the same process as follows:-
I renamed ALWRIProjectID to ALWRIProjectNr
I then added added a New Primary Key Autonumber Datatype named GrantAgreementID

I then created a 1 to many relationship between tblAALWRIProjects_New and tblAGrantsAgreements
using ALWRIProjectID

Study the relationship diagram so that you understand how the two tables are related.

Your original PK which stored just the Text values can now be removed from BOTH Tables.

You will need to redo the relationship to all related tables to tblAALWRIProjects

@mike60smart why if it's a unique identifier do you think the PK has to be an autonumber? A number of sources I've read don't support this. In all (almost all?) of the other tables, I have an autonumber PK, but in this one, the master table, it's really important that I keep track of the project code, that there's no room for confusion either by the database, or the end user (or for that matter, me, the creator). It seems to me that using an autonumber here just complicates things, making it so that I need to create additional queries to have this information show up clearly in the appropriate places. I understand that @Pat Hartman would prefer if people didn't use the name ID except in cases of auto-numbers. That seems an easy fix, addressed in your naming above Nr instead of ID... or if I just stick with the alphanumeric code, then CD, or something.

@Pat Hartman it's difficult for me to decipher your intended meaning above. Apologies for "making you" open so many forms. I uploaded the dummy database in response to a request from @June7 who I thought wanted to see how it was set up, so I included everything that I felt relevant.

@mike60smart and @Pat Hartman it's does no one any good for you to berate people on forums. People come here to find wisdom. My supervisor would prefer this whole project remain in a series of Excel spreadsheets. I thought it would make more sense to import it into Access, though I only had a basic understanding of the application. Over the past month of working on this project, I have learned a ton, often from people like yourselves. No one except for the two of you has been rude.

 

[mike60smart]

@mike60smart why if it's a unique identifier do you think the PK has to be an autonumber? A number of sources I've read don't support this. In all (almost all?) of the other tables, I have an autonumber PK, but in this one, the master table, it's really important that I keep track of the project code, that there's no room for confusion either by the database, or the end user (or for that matter, me, the creator). It seems to me that using an autonumber here just complicates things, making it so that I need to create additional queries to have this information show up clearly in the appropriate places. I understand that @Pat Hartman would prefer if people didn't use the name ID except in cases of auto-numbers. That seems an easy fix, addressed in your naming above Nr instead of ID... or if I just stick with the alphanumeric code, then CD, or something.

@Pat Hartman it's difficult for me to decipher your intended meaning above. Apologies for "making you" open so many forms. I uploaded the dummy database in response to a request from @June7 who I thought wanted to see how it was set up, so I included everything that I felt relevant.

@mike60smart and @Pat Hartman it's does no one any good for you to berate people on forums. People come here to find wisdom. My supervisor would prefer this whole project remain in a series of Excel spreadsheets. I thought it would make more sense to import it into Access, though I only had a basic understanding of the application. Over the past month of working on this project, I have learned a ton, often from people like yourselves. No one except for the two of you has been rude.

My apologies if you thought my pointing out your error was rude.

The idea of using an Autonumber in every table is just that it is used to uniquely identify each record. You can still retain your ProjectCode as a separate field in the table.

It is far easier to add records to a related table if you have an Autonumber in the Main Form and a related LongInteger DataType as the Foreign Key in the Subform.

All of your Subforms did not work but the examples I have uploaded do work. It is up to you whether or not your use the examples provided.
I would, and most others would suggest that you do.

 

[Gasman]

So in your project table you have have your project number, however, I would still use an autonumber. They are called surrogate keys, and exist purely to link records. Should never be used as data. Then if anything ever changes, they remain constant.
As you can tell, I am a big fan of surrogate keys. Also when I see ID, I also expect that to be an autonumber, in Access at least.

They are not being rude, but trying to steer you away from future potential issues.
If your skin is that thin, I feel for you.

 

[Pat Hartman]

The autonumber vs natural PK is an old argument. There are very few situations where a single, text field can be used as a PK. If you have one, then OK, use it. Just be aware that you have to use Cascade Update CONSISTANTLY if your PK is not controlled by you and therefore might change. It is only when you end up with multi-column PKs that things get awkward. When I was doing mainframe development in the previous century, we always used natural keys because there were never any tools that allowed the users to get at the data so all work was done by programmers and so when you are writing embedded SQL strings in COBOL it makes little difference if your join is 1 column or 8 columns The part of the advice that people who insist on autonumber PKs frequently miss is the part that says, if you replace a single or multi-field unique natural key with a surrogate key, then you STILL need a unique index on the combination of natural fields that define uniqueness for the particular business object you are modeling..

As to how this affects users, the answer is -- not at all. The users should never see autonumbers. They should only ever see the "natural" key. The reason that you want to use the natural key is because you keep looking at the tables rather than the interface so rather than using a a query with a join, you just look directly at table data. This is a crutch that you can train yourself to not need but this is not the hill I am willing to die on. If you have a unique single-field natural key, I don't care if you use it instead of an artificial surrogate key.

There is some discussion on efficiency. Long integers are 4 bytes. Text keys are always longer. Therefore, the joins may take less time to execute for autonumbers but you've got to get a goodly number of rows before this is ever a consideration.

it's does no one any good for you to berate people on forums

I'm sorry you directed that comment to me. Did I call you names and tell you that you're stupid and worthless? Simply telling you that something is wrong is what it is. Correcting an error. Some things are just plain wrong and if an expert tells you that something is wrong, there's a good chance the thing is wrong. Not everyone who gives advice here is actually an expert. If you follow my advice, you will find that I rarely make such a pointed statement and that is because much of what we do comes down to personal preferences. I do get pointed about validation and consistency though. Therefore my comment might be more like, I wouldn't do that, I would do this, usually with a reason because if I am going to spend my time trying to help you, I will try to educate you in the process so that eventually, you can help yourself and others. I don't have the time or frankly the inclination to be wishy-washy, gentle so I don't hurt anyone's feelings. I can write a paragraph to beat around the bush to avoid saying that awful word that seems to have offended you or I can be direct and unequivocal. Therefore, you can easily tell the difference between the things that you must fix and the ones that I don't like but don't need "fixing". I don't know what this application is all about. I couldn't recognize any business process that I am familiar with so that means I would need to dig a whole lot deeper into what this application is all about before I could even offer a suggestion for how to change the schema to remove the relationships that can cause anomalies.