Solved Trouble adapting Uncle Gizmos Checklist code.

[Uncle Gizmo]

i'm unsure how to link another subform to hold data that pertains to the particular checklist tab.

I have created a very rough test database to demonstrate the technique.(ChecklistMultiAddImages_1a.accdb) download HERE:-

Multi Check List Plus Image Links

This download relates to a specific question on Access World Forums here:-https://www.access-programmers.co.uk/forums/threads/trouble-adapting-uncle-gizmos-checklist-code.312286/The code in this MS Access example database will be of little interest to any casual Observer. However if you've been...

gum.co

The Added Code:-

                    Dim strSELECT2 As String
                    Dim strFROM2 As String
                    Dim strWHERE2 As String
                    Dim strEND2 As String
                   
                    strSELECT2 = "SELECT dataID, dataLinkID, dataSets, picName "
                    strFROM2 = "FROM tblData2 "
                    strWHERE2 = "WHERE (((dataSets)="
                    '& strSetID &'
                    strEND2 = "))"

                    Me.subFrmWinSfrmData2_05.Form.dataSets.DefaultValue = strSetID
                    Me.subFrmWinSfrmData2_05.Form.RecordSource = strSELECT2 & strFROM2 & strWHERE2 & 5 & strEND2

It's "hard coded" and only works on one set of sub-forms, and will need a lot of adjustment to get it working properly. You will need to follow my code design as a guide to pass the SQL Statements to ALL the sub forms.

I would very much like to complete this task, however I should be working a customer's database. The deadline is getting near, and I must devote my attention to that.

The other reason I'm reluctant to complete the code is you are running into a big problem if you carry on with the current design.

As you increase the number of subforms you are increasing the number of times MS Access has to extract information from the tables. This is going to slow your database down considerably, especially once you start adding a lot of data.

The solution is to only send the SQL Statement into the subforms when the tab the sub-form/sub-forms are on is selected.

I reckon this would be a good time to make this change. It really depends how much data you intend adding to it as to what you decide to do.

 

[HillTJ]

Tony, thanks. Will have a go. I think it's best to redo the logic now to avoid a potential problem later. I have no idea how successful this application may be, but if you've, with your experience have identified a problem then i should take heed of your advice. I can always work on the accident/incident recording part. Thankyou again.

 

[HillTJ]

Tony, arnelgp's approach appears much different to yours. You alluded to impending doom if i continued with the current methodology. Will arnelgp's method overcome the eventual issues?. If I could figure how to include the link Child:Master via 'Datasets' on the attachment form then all should work? I tried replicating the 'datasets' field from the checklist table to the main form thinking that it may now be 'visible'. But could not "see" it from the attachment form when trying to link parent:child. I thought this would be the answer! I don't really know what to do next. Please see my latest upload a couple of posts back.
PS - Arnelgp, your version would not let me add a new record. A teething problem?

 

[Uncle Gizmo]

You alluded to impending doom

If you've got small number of Records, you are using a single database on a single machine, then I think you could probably carry on with the approach you are using. However if you intend moving up to a multi-user environment with a split database, it could become slow, considering you have a lot of forms which are all simultaneously connected to the back-end data.

My point was you are planning to add another set of sub-forms, if you're going to have a multi-user environment, if you're going to have tens of thousands of Records, then it might be easier to set up the system to only pass the RecordSource into the sub form when a tab is selected. Really, it's horses for courses, if you've got a single system on a single machine then I suspect it will work OK for the time being.

 

[arnelgp]

you should add New List to tbList, since it is the Master list.

 

[HillTJ]

Tony, thanks for the prompt reply as always. I notice arnelgp's version uses a lot less code (He fires off an append query to capture the Checklist results). I also understand that your intention is to develop a 'drop-in' "Module" which, as a newbie I commend. Realistically, I don't imagine this application getting a lot of use, maybe a few checklists generated/week at a single site on a single PC. Or maybe split,with a few users at most. Cutting to the chase, I'm a bit confused between the two approaches. Which is better in my application? There is a bug in arnelgp's code that prevents a new record being added, but apart from that it works the same as your method, to me. But same problem linking the 'attachments' form. I don't mean to offend anyone.

 

[HillTJ]

arnelgp, I was responding to Tony, when I saw your response. Don't Quite follow you.

 

[arnelgp]

you have a Master List in tbList, then you have a tblData which has the records of tbList.
tblData is used in your Pipemaker workplace inspection list.
in order to Add new List, you add it to tbList.

otherwise, if you want to add it to tblData, remove the Master/Child link and instead
Change recordsource of subform each time you change tab.

whatever you Add to tbData must be added to tblList also.

 

[HillTJ]

arnelgp/Tony. Thanks, got it all sorted & have included a sub form attachments related to each tab. Looks great. If you'd like to see a copy, then I'd be happy to upload it once I make a few more tweaks. Thanks for your patience.

 

[Uncle Gizmo]

Why readdress this Now?
The reason I am readdressing your problem now is I just came across a piece of code (I've had for a long time) originally from MarkK here:-

Tabs Not Cooperating

MS Access SLOW!
I can see a problem developing if you try and load the all the sub-forms with a Record Source, it's going to slow your application down. Even in the Mock-up I've got with very few records, I'm already noticing a slight delay in loading.

Load each Tab Control Page Individually
The solution is to only load the record source into the Subform/Subreport Control Forms, which are on the Selected Tab Control Page. The trouble is, you've got lots of tab pages and subforms. To write the code to address each Tab Control Page individually would be a complete nightmare, even if you kept your code simple so that you could just cut and paste and make one or two minor changes.

Aspire to Object Oriented Methods
It's exactly through being forced to do this many times that you start to notice other people's methods, object oriented methods that use a lot less code to address the issue. You see a more advanced method which you aspire to, and if you take it on, you level up your VBA coding skills.

Change the Way you Think About VBA
Tabs Not Cooperating
The interesting part of MarkK's VBA Code is the Property that captures the Tab Control Page Name. To write this code you need to change the way you think about the problem. The laborious method, the procedural method, writing Code to access each individual Tab Control Page individually, well, as I said, it's a right pain, although It does provide one advantage, you can see exactly what you are doing. This is why when you first start to develop something the procedural method is actually the best approach.

Get the procedural Code Working
The procedural way of coding provides a useful specification. Get the procedural Code working on one or two Tab Control Pages and with one or two sub-forms. This exercise his will point the way to applying the object-oriented approach.

The Steps - (First Draft)
You need the name of the Tab Control Page selected on the Tab Control - Hence seeing MarkK's code reminded me of your problem, and pointed the way to a Solution...

First Question
What is the selected Tab Control Page name?

Second Question
Which Subforms are on this Tab Control Page? The parent of an Object on a Tab Control Page is the actual page, but how does this help you? You can't write code to say I have this page, this page is open, tell me what controls are on it, that doesn't work. You need to take a different approach. Each Subform/Subreport Control will know which Tab Control Page it's on because that Tab Control Page is its parent. Therefore can ask the Subform/Subreport Control WHAT IS YOU PARENT'S NAME?

Third Question
What is your parent? I suggest you add a Command Button to the form and add some code similar to this and you will see what I mean:-

Private Sub Command40_Click()
        MsgBox " >>> " & subFrmWinSfrmData_06.Parent.Name
End Sub

Using the above code snippet develop a For Each Statement to examine every single subform on your Form, ask Each subform what Tab Control Page are you on? If the Subform/Subreport Control is on the page that you have selected then you can update that Subform/Subreport Controls FORMS Record Source.

 

[HillTJ]

Tony, Thanks for remembering me! I'll do as you suggest. I understand the principle, but it will take me a while to digest. Learning all the time....... Appreciate it.

 

[Uncle Gizmo]

. I understand the principle, but it will take me a while to digest

I was just reviewing one of my videos and and I got to here:- Time index 01:10...

9) - Class Module Update - Get Associate Ctrl Label - Nifty Access

This shows the technique I'm talking about very nicely. In this case the code routine is looking for the label of a text-box. It's exactly the same technique you need to employ on the the Tab Control / Tab Page...