Eliminated evil lookup field in table by creating separate junction table, now confounded by form field. (1 Viewer)

[mike60smart]

OK Can you upload a copy of your current version of the database?

 

[Pat Hartman]

If the subform is not updateable, it is most likely because the query it is bound to is not updateable

 

[mike60smart]

Here it is!

OK Pardon me, but I was fed up with having to scroll within the Form to View the Subforms, so I rearranged the Controls.

I recreated the All Authors Form.
This form should only be based on the table named tblJbctPRsAllAuthors.
It did not need the table tblCLstAllRIStaff

I set the form as a Continuous Form vice Datasheet View

See if this now does what you want.

 

[Missomissou]

OK Pardon me, but I was fed up with having to scroll within the Form to View the Subforms, so I rearranged the Controls.

I recreated the All Authors Form.
This form should only be based on the table named tblJbctPRsAllAuthors.
It did not need the table tblCLstAllRIStaff

I set the form as a Continuous Form vice Datasheet View

See if this now does what you want.

It's a work of art. Thank you so much. I'm going to slowly walk through the design with my up-all-night brain now adn hope to be able to apply what you've taught me to the other input forms. Thank you again, so much!

 

[mike60smart]

I'd like to import the form into my existing database. Are there supporting pieces, such as macros or modules that I should also import?

No You can just Import as is

 

[mike60smart]

The subform you are using in your screenshot is not the subform I created.

 

[Missomissou]

The subform you are using in your screenshot is not the subform I created.

Right you are. Fixed. Thanks again!

 

[Missomissou]

I just created another subform like this for associated RPA codes in the main project table. I tried to replicate your code, and all properties from the subform you created, modifying tables/fields as appropriate. It's not quite working. The ProjectID and primary key fields from the junction table are showing up in the subform, but the associated RPAs (many already entered into the junction table) are not. The dropdown menu isn't populated, so there is nothing to select. And when I try to manually type in the code, there is an error message that says "The text you entered isn't an item in the list". The code I used, adapted from yours, was "SELECT TblCLstRPAs.RPA;AS RPA FROM TblJnctALWRIProjectsLinkedRPAs ORDER BY TblCLstRPAs.[RPA];"

Here is a screenshot of the relationship:
View attachment 112466

Thank you again. O.

Also, how did you get your subform to display as a continuous form with records as rows? When I select continuous form, it displays the authors as individual records. The closest I can come is by selecting datasheet for the default view. Not nearly as slick.

 

[mike60smart]

Also, how did you get your subform to display as a continuous form with records as rows? When I select continuous form, it displays the authors as individual records. The closest I can come is by selecting datasheet for the default view. Not nearly as slick.

Can you upload the db again?

 

[mike60smart]

Here you go! Thank you.

Here you go

Again I changed the structure of the table "tblJnctPRsRPA"
I added a Primary Key named PRID
I then linked PolicyReviewID PK to PolicyReviewID FK in "tblJnctPRsRPA"
I then created a Continuous Form based on "tblJnctPRsRPA"

 

[Missomissou]

Here you go

Again I changed the structure of the table "tblJnct{RsRPA"
I added a Primary Key named PRID
I then linked PolicyReviewID PK to PolicyReviewID FK in "tblJnct{RsRPA"
I then created a Continuous Form based on "tblJnct{RsRPA"

Thank you so much @mike60smart ! I'll spend some more time walking through the changes you made here and see if I can understand them a bit better/use them for the next subform.

 

[mike60smart]

No problem
Please note I have corrected the spelling of the table name

 

[mike60smart]

Our org is blocking the active content so I can't see the changes you made. I tried pasting it into a new folder--no luck. Any ideas?

You should be storing the database in a Trusted Location

 

[mike60smart]

@


It's in the same folder as the last edited version you sent.

When you get the message about Blocked active content you can click OK and it should open OK

 

[mike60smart]

Hi
In your original table you had RPA and PolicyReviewID with both set as PK's