Sync Two related subforms (1 Viewer)

[ngsabath93]

I have a main form with multpile tabs - each tab containing a different subform.

Link Master Field: ClientID (field in Master Form)
Link Child Field: Client ID (field in all subforms)

There is another field that all the subforms (continuous type) have: ObligorName. All the subforms are based off of a huge table (subtable) with fields: Obligor Name, Address, Zipcode, City, DOcuments Required, Bank Account Number, etc......

Essentially, the user will fill in the 10 obligor names associated to ONE client on the first subform on the first tab + address + zip code + city.

When the user clicks on the second tab to fill out the next subform, I want all 10 obligor names to be there already, so then they can fill out Documents Required + BAnk Account Number.

If I fill out the first subform and then exit out of the form and then reopen it, the other subforms autofill.
HOWEVER, I dont want to have the user have to do this. It wastes a lot of time. What can I do about this so that the table is automatically updated right away. They are all based on the same table so I do not see why there is so many issues.


PS: please don't write in here telling me that it should all be in one form. The form is VERY VERY VERY VERY complicated and this example is "dumbed" down to make it easier to explain. Thanks in advance guys! This forum is very helpfull

 

[GinaWhipp]

You might try a Save and then Requery when switching Subforms. If that doesn't work, is there any code in the On_Close or On_Unload event of your Form?

 

[vbaInet]

All the subforms are based off of a huge table (subtable) with fields: Obligor Name, Address, Zipcode, City, DOcuments Required, Bank Account Number, etc......

Obviously a normalisation or form design problem here. I have hunch that I've called this out before.

 

[GinaWhipp]

@vbaInet

Because of the last time the OP made I was leaving that alone.

 

[vbaInet]

In that case I suppose if he still wishes to keep his db in bad shape but still solve the current problem at hand, he shouldn't be using subforms.

 

[GinaWhipp]

Probably not but I have not seen the file.

 

[ngsabath93]

You might try a Save and then Requery when switching Subforms. If that doesn't work, is there any code in the On_Close or On_Unload event of your Form?

how do I do a save a then requery?
Also I do not currently have any code in the on_close event of my form

 

[ngsabath93]

View attachment 5.accdb


Here is a simplified example of what I have

 

[GinaWhipp]

Will be looking at the file shortly...

 

[GinaWhipp]

Adding this in the On_Change event of your Tab Control will work...

Private Sub TabCtl3_Change()
    Me.[Client Info subform1].Requery
    Me.[Client Info subform2].Requery
End Sub

You will need do that for each Subform you have on each Tab that you want the changes to show up in. Sample file attached.

 

[ngsabath93]

Thank you so much! That works perfectly!!! I have another quick question. I want a "homepage" where the user can put in the client ID and if it exists, it will open open the Client form and display existing data. However, if the Client ID they enter does not exist, it opens a blank form. I made another form (homepage) with the "ClientID" field on it with it as a combobox and row source is ClientID. and the button on the page opens the Client Form to "display existing records". When I put an ID in that doesnt exist in the database, it takes me to a blank form. However, I want the Client ID field on the Client Form to already be filled with the ID that was typed into the homepage. I attached what I did so far. Any advice how i would do this? I attached the new DB below



View attachment 5.accdb

 

[GinaWhipp]

Glad that worked!

Hmm, just looked at your sample and now I have to ask a question, is that really your Table set-up? I didn't say anything earlier because I thought that was just a stripped down version to upload but now I have to wonder.

 

[ngsabath93]

Yeah, for the most part. But the form is made up of about 10 tabs with ten subforms. 5 of the subforms have the same underlying table (hence what I asked about), but the other 5 subforms each have 5 different underlying tables. However, each table has the foreign key, ClientID. In relationships, the ClientID of the main form has a one-to-many to each Client ID field in each subtable (with enforce refertial integrity, cascade update related fields, and cascade delete related records checked). It is a very complicated form that I was asked to do on access.

I have a strong feeling it isn't "normalized" but I am not an expert at access at all and have only learned how to do it through online help.

 

[GinaWhipp]

The table that only contains the Client ID is that the entire table. Where do you add new Clients? And it's really not that complicated so no worries there but it does not appear to be normalized correctly.

 

[ngsabath93]

That is not the only thing on that table. There are about 7 other fields in the main table along with clientID. for example, one field is: Payment DueDate. I did this because on one of the tabs, there is a subform where all the obligors are entered (continous form, like above), but the Payment DueDate is the same for all 10 obligors of ONE client. So instead of putting this field on the contiuous subform, I placed the field outside of it on the tab. That way the user would only have to enter it once for ONE client, and not 10 times for EACH obligor. Also, I have "Last Updated field".

 

[GinaWhipp]

They could still do that as long as there was one table that held all the Clients information. So, when you say you don't want them to open a blank form if the Client ID does not exist, what you want is not to enter a new Client but to only select from existing ones, correct?

 

[ngsabath93]

They could still do that as long as there was one table that held all the Clients information. So, when you say you don't want them to open a blank form if the Client ID does not exist, what you want is not to enter a new Client but to only select from existing ones, correct?

I guess ideally, I want there to be a homepage. And when the user types the ID, it will either take them to an existing form or a brand new one. On the new blank one, all fields will be blank except the Client ID, which will be filled in with the ID typed in from the main page. Does that make sense? So once a US fills out that field in the main page, it will either dispaly an old recold (client) or add a new record

 

[GinaWhipp]

Kind of but if you only want them to select from the available Client ID's I would suggest using a Combo Box and set Limit to List as Yes.

 

[ngsabath93]

ok I slightly changed my mind. I am ok with a completely blank form popping up if a nonexistant ClientID is typed. However, I want the combobox to requery (or refresh?) itself so that if I exit out of the pop up Client Form, the typed ID will show up in the dropdown menu of the homepage field without having close and reopen the homepage form

 

[GinaWhipp]

Which Form are calling the Homepage? I mean if the Form I see the Client ID Text Box is changed to a Combo Box with Limit to List set to Yes then nothing to do. The User will get a message and the Form can either be blank or undo but either way they don't have to close and reopen.