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

mike60smart

Registered User.
Local time
Today, 08:34
Joined
Aug 6, 2017
Messages
1,917
OK Can you upload a copy of your current version of the database?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:34
Joined
Feb 19, 2002
Messages
43,587
If the subform is not updateable, it is most likely because the query it is bound to is not updateable
 

mike60smart

Registered User.
Local time
Today, 08:34
Joined
Aug 6, 2017
Messages
1,917
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

Member
Local time
Today, 01:34
Joined
Jan 30, 2024
Messages
51
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

Registered User.
Local time
Today, 08:34
Joined
Aug 6, 2017
Messages
1,917
The subform you are using in your screenshot is not the subform I created.
 

Attachments

  • All Authors.png
    All Authors.png
    9.4 KB · Views: 25

Missomissou

Member
Local time
Today, 01:34
Joined
Jan 30, 2024
Messages
51
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

Registered User.
Local time
Today, 08:34
Joined
Aug 6, 2017
Messages
1,917
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

Registered User.
Local time
Today, 08:34
Joined
Aug 6, 2017
Messages
1,917
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"
 
Last edited:

Missomissou

Member
Local time
Today, 01:34
Joined
Jan 30, 2024
Messages
51
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

Registered User.
Local time
Today, 08:34
Joined
Aug 6, 2017
Messages
1,917
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

Registered User.
Local time
Today, 08:34
Joined
Aug 6, 2017
Messages
1,917
Hi
In your original table you had RPA and PolicyReviewID with both set as PK's
 

Attachments

  • RI.png
    RI.png
    17.1 KB · Views: 24

Users who are viewing this thread

Top Bottom