Subform won't allow data entry/display form fields when record is empty

[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.

 

[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.