Solved Using Subforms to Update Visible Data

pooldead

Registered User.
Local time
Today, 15:20
Joined
Sep 4, 2019
Messages
136
I have a form setup with two subforms, as shown below:

1581449746594.png


I am trying to set it up to where the subform on the right, Workgroup Updates, changes to different information when cycling through the records on the left. Just by the look, they are tied together by "Application Role Name" (in this case Test-Role-2). However, this information is showing while Test-Role is selected. I'd like Test-Role to show its own information, then have that form change to Test-Role-2 when the 2nd record is selected.
 
One of the easiest methods is to put a hidden textbox "txtBxLink" on the main form. Link the second subform to this textbox
link Masterfields:[txtbxlink]
lin childefields: Test-Role-2

In the first subforms on current event
Me.Parent.txtbxlink = me. test-Role-2
 
One of the easiest methods is to put a hidden textbox "txtBxLink" on the main form. Link the second subform to this textbox
link Masterfields:[txtbxlink]
lin childefields: Test-Role-2

In the first subforms on current event
Me.Parent.txtbxlink = me. test-Role-2
I apologize if this is a dumb question, but would that update all fields in the "Workgroup Updates" form, or just the Application Role Name? I only ask b/c my goal is to display all information of a workgroup record that is tied to the specified role name.
 
Normally you link a subform to a value in the mainform. This is now linking a second subform to a value in the first subform.
 
So I'm not an expert and have been looking into the master/child fields, but I'm having some trouble. I understand how this would work with the text box containing a single value, but how would I force the textbox value to update based on the selected record?

To be clearer, if I select start with the record for "Test-Role" selected and the textbox reflects the same, how do I tell the textbox to update when I move to the next role "Test-Role-2"?
 
Once the Main Form and Subform are linked the Subform record will always match the record selected in the Main Form, nothing for you to do.
 
can you upload a zip copy of your db. Will fix and send it back.
 
See demo. One line of code only in subform orders.
 

Attachments

See demo. One line of code only in subform orders.
Thank you MajP. That gave me a good start. The "Workgroup Updates" subform on the right is just text boxes filled by a query, based on a dropdown selection. I was able to setup the hidden textbox, but how can I tell it OnCurrent to also re-run the query behind the "Workgroup Updates" subform?
 
I was able to setup the hidden textbox, but how can I tell it OnCurrent to also re-run the query behind the "Workgroup Updates" subform

Maybe I am not understanding, but my assumption is that Workgroup Updates should be linked to the Applications Role subform, by the Role Name. If that is the case then I am suggesting you do not need to requery but link the two subforms using the txtbxlink.
 
Maybe I am not understanding, but my assumption is that Workgroup Updates should be linked to the Applications Role subform, by the Role Name. If that is the case then I am suggesting you do not need to requery but link the two subforms using the txtbxlink.
Nope your understanding is correct, I have no doubt the issue is on my end. I attached a zip of my db for you. I was hoping to figure it out on my own, but clearly I am getting stuck somewhere.
 

Attachments

Is there a reason that the workgroup subform is unbounded. This is my guess at what you want using a bound subfor
 

Attachments

Is there a reason that the workgroup subform is unbounded. This is my guess at what you want using a bound subfor
It's not a good reason....I'm still learning some of the more intermediate things like binding subforms. But yes, this is what I was hoping to achieve. Thank you so much for your help MajP!
 
Before you start and get a nice user interface. I would post your table design and let the group here take a look at it. Without doing a deep dive it looks to me your tables are not well normalized. There looks like a lot of repetitive data. This will cause a lot of down stream problems and make doing things a lot more complicated. It looks to me that you already have a lot of code that is unnecessary. I think your are writing a code to overcome a not so good design. Pay now or pay bigger later.
 
I can tell you when I look at a table and do not see an autonumber primary key I am concerned (unless they are a more advanced user using other types of keys). When I see no PK of any type, I pretty much know there is a design issue. Do describe a table here (I would start a new thread in the tables forum). Need to describe your tables and how they are related. Give us
Code:
TableName
  FieldName1 ' datatype Text, number, date, autonumber
  fieldName2
  fieldname 3
Identify which are Primary keys and which are foreign keys. For the foreign keys explain which table the key relates to.
Explain in words the purpose of the tables and how they are used.
 
I can tell you when I look at a table and do not see an autonumber primary key I am concerned (unless they are a more advanced user using other types of keys). When I see no PK of any type, I pretty much know there is a design issue. Do describe a table here (I would start a new thread in the tables forum). Need to describe your tables and how they are related. Give us
Code:
TableName
  FieldName1 ' datatype Text, number, date, autonumber
  fieldName2
  fieldname 3
Identify which are Primary keys and which are foreign keys. For the foreign keys explain which table the key relates to.
Explain in words the purpose of the tables and how they are used.
I will certainly work on posting this. But to make sure I have a clear understanding of what the master/child links are doing, if I wanted to base this off the "Role Owner" field instead of the Role Name, I would modify the OnCurrent for the text box and change the Child Field of "Workgroup Updates" to the Workgroup Name, right?

The intention here that it may be easier to relate a workgroup to a role, and not multiple roles to one workgroup.
 
The intention here that it may be easier to relate a workgroup to a role, and not multiple roles to one workgroup
Conceptually yes, but you really need to fix this data structure and thus unlikely to do either once fixed.

Without seeing real data and understanding the relationship I am guessing here, but I think there might be
TblApplications
TblRoles
TblWorkgroups

I think there is a one to many or many to many relationship between applications and roles. Then a many to many between roles to workgroups. You definitely do not want three tables for the roles. Instead you have a field callled RoleType (TEMP, Existing, New). Never want to have tables with the same fields and different names,.

Even though you are importing from a non normal worksheet, you want to transform the data into a correct structure. When you export back to excel you can then reformat in a non-normal structure.
 
So, this form is tied into a bigger database that performs multiple functions. I assigned the "Workgroup Name" in the "Roles_Workgroup" table as the Primary Key, but I noticed a couple things I'm having trouble pinpointing the cause.

1) After I enter workgroup information then select the application from my dropdown, I get the error "changes weren't successful...they would create duplicate values in the index, primary key, etc. How do I keep this error from occurring?

2) I did notice that duplicate entries are being created in the workgroup table, as well as blank records. Is this normal?
 
So, this form is tied into a bigger database that performs multiple functions.
Does that mean you are stuck with this table design.
I assigned the "Workgroup Name" in the "Roles_Workgroup" table as the Primary Key, but I noticed a couple things I'm having trouble pinpointing the cause.
1) After I enter workgroup information then select the application from my dropdown, I get the error "changes weren't successful...they would create duplicate values in the index, primary key, etc. How do I keep this error from occurring?
2) I did notice that duplicate entries are being created in the workgroup table, as well as blank records. Is this normal?
I could probably give you a solution to fix the symptoms, but that does not fix the cause. I am cautious of making solutions to work around a bad design. It will snow ball from there.
Again I think you are jumping the gun and I would fix your table structure the best you can. If you can fix these tables do that first, then everything will be much easier and clearer. If you absolutely cannot fix it, then still need to understand what can be modified.
Without understanding the relation between applications, roles, workgroups, and workgroup members, I cannot make a good recommendation.
 
I understand. And I would certainly rather address the cause as well.

It's not so much that I'm stuck with the table design necessarily, more that I have never been too sure on how to create a table storing a primary key, as the functions of the database are all separate. It's meant to be kind-of a one stop shop for my team to automate different tasks. But I can look into normalization more and figure that out. I always knew it would pop-up, so seems like now is a great time to learn. Thanks again!
 

Users who are viewing this thread

Back
Top Bottom