[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

Dynamically assign subforms

GRayL was able to get me to a point to dynamically set certain tabs visible in this question:  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24349651.html  Now I need a way to assign subforms to these tabs based on what client is selected.  Any help is appreciated.
0
nfstrong
Asked:
nfstrong
  • 25
  • 19
  • 3
1 Solution
 
coffeeshopCommented:
Take a look to this thread, there you will find how to change the subform dynamically and some other relating questions:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_24345546.html
0
 
nfstrongAuthor Commented:
coffeeshop-
Thanks for your response, but it's not what I'm looking for.  If you look at the question I reference above, you may get a better idea of what I'm looking for.
0
 
coffeeshopCommented:
I looked at your question, the linked question and the mdb provided. What you asked for is:

>Now I need a way to assign subforms to these tabs based on what client is selected.

That is what described in my link, or one possible way to do this.

1) you add a subform to your tab(s) - random, this act as dummy, you canhide it if you don't want to show it
2) you change this subform accordingly to the selection in your client-combo to the subform you want to show and make it visible, if hidden

If I didn't get what you mean, can you clarify it?

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
nfstrongAuthor Commented:
As the linked question states, I have 26 tabs and about 75 clients.  On any of these tabs there is a possibility of anywhere from 4 to 34 subform options depending on which client is selected.  That is a lot of subforms to load and then set the visible property.  I don't want to load the subform until the tab is selected.  Currently, I am using case statements to set the source object.  I have attached a sample below.  I would like a solution similar to the solution in the linked question to simplify to maintenance.  As GRayL suggested, this will probably require seperate tables to control these.  So I'm looking for help on setting this up and getting it working.
Select Case Me.txtCliNum.Value
    Case "AAC0", "ABC0", "AFC0", "ALF0", "AMFI", "ASFI", "ATF0", "BAAC", "CAL0", "CCL0", "CFF0", "CFGH", "COCC", "COFI", "CON0", "CRE0", "DCC0", "FCF0", "FFC0", "FFI0", "FHC0", "FIM0", "FMF0", "FMI0", "FNC0", "HFC0", "HGC0", "HOM0", "ISPC", "LFC0", "LFS0", "LIB0", "LOB0", "MAC0", "MAF0", "MGM0", "MME0", "MMF0", "MVA0", "NAC0", "NAL0", "NCCI", "PCC0", "PDSF", "PER0", "RFC0", "SAF0", "SAL0", "SFS0", "SMC0", "SOF0", "TAC0", "UACC", "UAI0", "UFI0", "USA0", "WAC0", "WFC0", "WMX0", "AAF0", "HIGH", "HOF0", "PMA0", "RUD0", "WEST", "TRFI", "SCC0", "SAC0", "AFLC"
        Me.subFinSum.SourceObject = "subFinSumA"
        Me.subFinSum.LinkMasterFields = "txtCliNum;cmbDOD"
        Me.subFinSum.LinkChildFields = "ClientNumber;DateOfData"
            If Me.txtCliNum.Value = "ABC0" Then
                Me!subFinSum.Form!txtSeniorDebt.Enabled = True
                Refresh
            End If
    Case "CIG0"
        Me.subFinSum.SourceObject = "subFinSumB"
        Me.subFinSum.LinkMasterFields = "txtCliNum;cmbDOD"
        Me.subFinSum.LinkChildFields = "ClientNumber;DateOfData"
    Case "SOU0"
        Me.subFinSum.SourceObject = "subFinSumD"
        Me.subFinSum.LinkMasterFields = "txtCliNum;cmbDOD"
        Me.subFinSum.LinkChildFields = "ClientNumber;DateOfData"
    Case "REG0"
        Me.subFinSum.SourceObject = "subFinSumE"
        Me.subFinSum.LinkMasterFields = "txtCliNum;cmbDOD"
        Me.subFinSum.LinkChildFields = "ClientNumber;DateOfData"
    Case "CHL0", "PAC0"
        Me.subFinSum.SourceObject = "subFinSumC" 
        Me.subFinSum.Form.Filter = "Company='" & cmbCompany & "'"
        Me.subFinSum.Form.FilterOn = True
        Refresh
End Select

Open in new window

0
 
coffeeshopCommented:
But you defined all 26 tabs, you do not add a tab page at runtime?

This is same for the subforms. You cannot add them at runtime to your form. So if you do not want to provide all tabs with a (dummy-)subform, you have to place the maximum count forms you need (the maximum tab pages you show for a specific client) at your form and bring them to the correct tab, change them to the actual form you want to show. But this seems difficult for me: you can move the subforms and change their size, but I don't know how to set one to a specific tab-page.

I would prefer to place one empty subform with no recordsource to all tab-pages, as I suggested. Then you only have to keep your subform-name dynamic:

Me.Controls("subFinSum" & Trim$(YourTabPage)).SourceObject = "subFinSumX"

and you have to set it back before a new client selected.

Me.Controls("subFinSum" & Trim$(PreviousTabPage)).SourceObject = "subDummy"

0
 
GRayLCommented:
nfstrong:  Hi, back again.  I've had a re-think of the problem and with 75 clients, 26 pages, some of which contain subforms, I think we have to re-design the database along the lines of

tblCustomers
=========
CustID - autonumber
CustName - text
etc.

tlbPages
======
PageIndex - autonumber
PageName - text

tblSubForms
=========
SubFormID - autonumber
SubFormname - text
MasterLinkField - text
ChildLinkField - text

tblPagesCusts
==========
PgCustID - autonumber
CustID - fk - from tblCustomers - mandatory
PageID - fk - from tblPages - mandatory
SubFormID - fk - from tblSubForms - optional

With this setup, you can now create a query linked to the combo box on CustomerID which will give you all the associated pageIndexes and where appropriate, the subformNames and both Master and Child Link Fields.

See where I am coming from?  


0
 
GRayLCommented:
If we make PageIndex autonumber, then we cannot get page 0 as autonumber starts at 1.  Perhaps rename the field PageNo and derive the PageIndex as PageNo-1?  Probably the best way.
0
 
nfstrongAuthor Commented:
GRayL:  I do see where you are coming from.  When I build the query and try to run it I get the following error:  Type mismatch in expression.  My query code is below.  I'm not sure what I have wrong.
SELECT tblClients.ClientNumber, tblClients.ClientName
FROM tblSubForm INNER JOIN (tblPages INNER JOIN (tblClients INNER JOIN tblPagesCusts ON tblClients.ClientNumber = tblPagesCusts.CustID) ON tblPages.PageNo = tblPagesCusts.PageID) ON tblSubForm.SubFormID = tblPagesCusts.SubFormID
WHERE (((tblClients.ActiveStatus)="A"))
ORDER BY tblClients.ClientNumber;

Open in new window

0
 
GRayLCommented:
Make sure the data type of the fields used in each ON clause is the same.  While what you are trying to do is not of high-end complexity, it does require that you be painstakingly accurate because everything is so closely linked.  Perhaps pare the table down to the minimum required to support the question, do a compact & repair, and upload the mdb using Attach File below?

ON tblClients.ClientNumber = tblPagesCusts.CustID)
ON tblPages.PageNo = tblPagesCusts.PageID)
ON tblSubForm.SubFormID = tblPagesCusts.SubFormID

Why not use the same field name in the two different tables?  Data type of ClientNumber has to match CustID, etc.
0
 
GRayLCommented:
Because you are only pulling the two customer fields from the customer table, I fail to see what the query will give you, except repeats of CustID and CustName where appropriate.
0
 
nfstrongAuthor Commented:
I went and changed the data types of the fields in tblPagesCusts and I am able to pull data now.  I am pulling ClientNumber, ClientName, SubFormName and PageIndex.  These should be the fields I need, right?  
0
 
GRayLCommented:
TLAR (that looks about right!) ;-)
0
 
nfstrongAuthor Commented:
Ok, can you give a little help on how to use this to set the source object of the tab?
0
 
GRayLCommented:
In a combo box on the main form you use the query as the record source:
SELECT ClientID, ClientName FROM tblClients ORDER BY 1;

Now modify your query as per the snippet below.  Note the spelling of tblSubForms.  Just to make sure we are on the same page, can you post the results of the query for any client - the more complex his form needs, the better.  
Create the combo box as above, open the form, select a client, and then run the query.  It should produce a corresponding recordset.  

It now remains to use the recordset in the On Current event of the combo box to populate the form according to the selected client.  If you have use the default page names of Page1, Page2, etc.  did you use 1,2,etc as the page number or 0,1,etc.?



SELECT 
tblClients.ClientNumber, 
(tblPages.PageNo -1) AS frmPageIndex
tblSubForms.Name AS sfmName
tblSubForms.MasterLinkField as frmMasterLinkField
tlbSubForms.ChildLinkField as frmChildLinkField
FROM tblSubForms 
INNER JOIN (tblPages 
INNER JOIN (tblClients 
INNER JOIN tblPagesCusts 
ON tblClients.ClientNumber = tblPagesCusts.CustID) 
ON tblPages.PageNo = tblPagesCusts.PageID) 
ON tblSubForms.SubFormID = tblPagesCusts.SubFormID
WHERE ((tblClients.ActiveStatus="A") AND (tblClients.ClientNumber = Forms!myFormName!cboClients))
ORDER BY tblClients.ClientNumber;

Open in new window

0
 
GRayLCommented:
sorry, lines 5 and 6 should be prefixed with sfm, not frm.  Also I should have use the field names LinkMasterFields and LinkChildFields in tblSubForms.   I will also assume your page numbering is zero based like that of the form.  That of course will spill over into the above query which now becomes:
SELECT 
tblClients.ClientNumber, 
tblPages.PageNo AS frmPageIndex
tblSubForms.Name AS sfmName
tblSubForms.LinkMasterFields AS sfmLinkMasterFields
tlbSubForms.LinkChildFields  AS sfmLinkChildFields
FROM tblSubForms 
INNER JOIN (tblPages 
INNER JOIN (tblClients 
INNER JOIN tblPagesCusts 
ON tblClients.ClientNumber = tblPagesCusts.CustID) 
ON tblPages.PageNo = tblPagesCusts.PageID) 
ON tblSubForms.SubFormID = tblPagesCusts.SubFormID
WHERE ((tblClients.ActiveStatus="A") AND (tblClients.ClientNumber = Forms!myFormName!cboClients))
ORDER BY tblClients.ClientNumber;

Open in new window

0
 
nfstrongAuthor Commented:
I'm working on populating the tblPagesCusts table.  I will get a recordset when I have that completed.

I thought of something else though.  Is it kind of repetitive to have the pages listed in the Client table as well since this is used for the loop you created in the previous question?  Would there be a better way to use this new table to do the same thing?

I appreciate all of your help!
0
 
GRayLCommented:
Proper db design requires a different record for each combination of pageno and source object.  If a client had five pages, two of which had subforms controls on two of the pages, that would be five different records where three of the sets of sourceobject, LinkMasterFields, and LinkChildFields would be Null and two sets would be properly filled in  BTW, make sure you prefix the source object with Table. , Query. , or Form. .  

In the original question, we did not properly address the issue of source objects.  Here, I am trying to rectify that.
0
 
nfstrongAuthor Commented:
I have attached the results for one of my clients.  Let me know if you need anything else.
qrySubForms.doc
0
 
GRayLCommented:
Lets get the fields down properly - see the snippet.

WA = Where Applicable
TQF - you must type in Table, Query, or Form
 
ClientID frmPageIndex sfmName(WA) ControlSource(WA) sfmLinkMasterFields(WA) sfmLinkChildFields(WA)
CHLO      0           subFinSubC  TQF.TQFName       fieldname(s)            fieldname(s)
 
fieldname(s) are comma separated and must be the name of fields, not controls.  frmPageIndex should be ASC not DEC

Open in new window

0
 
nfstrongAuthor Commented:
For the MasterFields I'm using the values in the combo boxes selected on the main form.  As for each client there are multiple date possibilities and in the case of this client multiple Company posibilities.  I'm not sure how using the field names would work here.

Also, the sfrmName - is that the Subform control name?
Then the ControlSource would be the name of the actual subform to populate the control, correct?
0
 
GRayLCommented:
The Source Object would be the name of the actual subform to populate the control.
0
 
nfstrongAuthor Commented:
Is this better?
qrySubForms.doc
0
 
GRayLCommented:
I haven't given up.  However, I'm discovering that one cannot use the value of a form control such as a combo box as a Link field or fields in a subform.  I'm playing with using the value of the combobox as the Filter of a main form, which is where I think you have to go.

I see you have not prefixed the SourceObject with Table., Query., or Form. as I tried to show you at http:#a24400117.  I would rename the field SourceObject to sfmSourceObject so the two cannot be confused, as the actual property name is SourceObject.
0
 
GRayLCommented:
any way you can pare the mdb down to say half a dozen dummy clients and some dummy data, do a compact & repair, and upload the mdb using Attach File below.  We would need the tables, queries and forms applicable to those half dozen, so you could also drastically reduce the number of pages in the tab control.  Once you get it working for a few, enlarging it to 26 pages and 75 clients is just a matter of scale.

Alternatively, if nothing is compromised by giving us the whole works, by all means upload it all.  Remember the mdb has to be less than 5 meg, else it needs to be zipped.
0
 
nfstrongAuthor Commented:
I have pared down the mdb and attached it below.  Let me know if you need anything else.

Thanks!
EE.zip
0
 
GRayLCommented:
A picture is worth 10,000 words.  I see from your work that you are not using any subform controls per se.  The form frmSpreads has 27 pages, each of which contains a single form whose name is prefixed with 'sub'.  I've always been of the impression you wanted to work with a single subform control on each page in which the code embeds a single subform based on the client id.  In fact I could not find any subform control, just your controls on the top of the form under which you have the 27 page tab control on which you would want to see several 'sub' forms which differ according to the client id, correct?  In all cases, the client id, and date of data in each 'sub' form link to the client id and date of date in the combo boxes at the top of the form.  In several records I see you want to further restrict the recordset by by adding a third field.

I know you have done a lot of work on the project but I see a lot of duplication of forms except for one field.  I think I calculated  179 separate forms - way more than what is needed if you were to use queries and code correctly, IMHO.  When you do a count of unique subFormName's, I get 19 - probably a more realistic number.  I also notice you have a lot of Update queries, rather than using the recordsets created by equivalent Select queries.  That may very well be related to performance - I don't know.

Was it your intent to avoid the use of subform controls and stick to embedding a specific form on each page depending on the client combobox at the top of the page?

My biggest fear is the work that is going to be created by trying to maintain 48 tables, 260 queries, and 179 forms in a single Access database.  Comments?



0
 
nfstrongAuthor Commented:
There are actually subform controls on each page.  The code on the tab control change event sets the source object for each subform control based on the client id.  For example, on the Financial Summary page, the subform control is subFinSum and the source object would be subFinSumA, subFinSumB, etc.  The naming convention may not be correct and that is what is confusing.

There does seem to be a lot of duplication of forms, but I wasn't sure the best way to create these as each client has the potential of having different fields on each form.  For example, AAC0 may have a set of fields on the FinSum form, while ABC0 has the majority of the same fields, but just a couple are different.

I agree with you that it will be a monster to maintain.  I am open to any and all suggestions to simplify.
0
 
GRayLCommented:
What date range must be available in the DOD combo box?  As I understand it, if the user is given the option of selecting a date, it must a month-end date, correct.  I have an easy method of displaying all month end dates for any number of years in combo box.  I think you have the calendar button in case the user wants a date that isn't in the range.  I say get rid of the button and give him/her all the 'possible dates.  Comments?
0
 
nfstrongAuthor Commented:
The date range available in the DOD combo box are the dates where records are available for that client.  This is pulled from the tblExecSum table.  If the date the user would like to enter is not in the drop down, then they can select the date from the calendar.  Each client may have a different date range available.  Also, there may be a very rare instance when we have to enter data for a date which is not a month end.

If there is a way to allow for non month end dates in your method, I'm all for it.  I think it would definitely be easier having the date already available for the user.  If we can show month end dates from 1/31/1990 - the most recent month end 1 year from the current date?
0
 
GRayLCommented:
These page names do not have a subform control.

Delq Calc
Interco
Outstandings by State
P&&L Chargeoff
Repo Delq
Sales Report by Lot
Total CO

Was that intentional?
0
 
nfstrongAuthor Commented:
I don't have the subforms built for those pages yet, but they will have a subform control once I complete those.
0
 
GRayLCommented:
Current date 2009-05-28.  Do you want 2008-05-31 or 2008-04-30?  Will the not-end-of-month dates wind up in tblExecSum?
0
 
nfstrongAuthor Commented:
Let's go with 2008-05-31.  The not-end-of-month dates will most likely end up in the tblExecSum.
0
 
GRayLCommented:
I will put dummy subform controls on those pages.  BTW each subform control can have the same name.   I will rename them all sfmControl.  Then we can get rid of the subFormName field in table tblSubForms.  BTW I added the 's' to the table name.  I should have something for you tomorrow or Monday.  
0
 
GRayLCommented:
I got that wrong.  VBA won't let me use the same name twice even though they are on separate pages.
0
 
GRayLCommented:
Try this.  I had to change a few things - most notably deleting the TabCtl3_Change() function as it was humongous, and stopped me from changing tabs once I had selected a client, and date.  In addition I added a one liner in place of the function that calculated the end of month date.  
WFPCDB.zip
0
 
GRayLCommented:
Month end dates from 1990-01-31.  You need a small table Nums with the integer field num containing 1 to 300 or so.  

SELECT Distinct DateSerial (Year(Date()), Month(Date())-10-Num,0) as EndMonthDate FROM Nums50
WHERE DateSerial (Year(Date()), Month(Date())-10-Num,0) >= #1990-01-31#
ORDER BY 1 Desc;
0
 
GRayLCommented:
The silence is deafening ;-)
0
 
nfstrongAuthor Commented:
Sorry, I have downloaded your solution, but haven't had a chance to look at it yet.  I plan on looking at it tomorrow and will let you know if I have any questions.  I appreciate your time in working on this.  Thanks!
0
 
GRayLCommented:
Thanks, hope it works out.
0
 
nfstrongAuthor Commented:
Wow, that works really great!  Much shorter and easier than the long TabCtl3_Change() function.

There are a few things I noticed.  The msgBox that pops up after you hit View Data, always says 75 records.  I know that not all the clients have that number of records.  Also, I'm not sure the Link Fields are working because when I select a Company from the drop down, the data doesn't change in the form.  There are also a few textboxes that I would like to enable based on which client is selected on certain forms.  How would I go about adding that here?  That was in the TabCtl3_Change() function.

Just a few (hopefully small) fixes.  I appreciate all your work!  Thanks!
0
 
GRayLCommented:
I was just doing what the question asked,  dynamically assign a source object to subforms based on the client selected.  Populating those forms based on your table is not a small fix.  I just had to be sure that my approach to assigning the source object and making selected tabs visible was going to satisfy your requirement.
0
 
nfstrongAuthor Commented:
Yes, but you also removed a function in my db that was doing more than just assigning the source objects.  I feel if you remove something, you should replace it with sometihng that completes the same funciton.  Also, if you reference the original question noted above you will find the request in post #24245421.
0
 
GRayLCommented:
I replaced something that didn't work with something that did.  I'm sorry you feel the question was not answered.  Perhaps someone else can.  Good luck.
0
 
nfstrongAuthor Commented:
My function worked just fine.  I was merely trying to simplify the maintenance by having the form more table driven.
0
 
GRayLCommented:
Thanks, glad to help.  I have been doing some additional work on the form and found several areas where code cleanup would help.  How are you doing with setting Master and Child links?  If you have opened another question can you place the link here?
0
 
nfstrongAuthor Commented:
I appreciate that!  I have been trying to get the Master and Child links to work, but keep receiving an error when Company, Branch, etc. are added.  Maybe it would be easier to set those as filters?

Here's the question I opened:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24476245.html
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 25
  • 19
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now