Which tab is subform opening in? (1 Viewer)

[Steve@trop]

I have a tab control with 8 tabs, one for each hour in a shift. There is a subform on each tab. Some of the subforms are re-used since they are identical to each other (there is a 1,5 form, a 3,7 form and a 2,4,6,8, form). I'm looking for a way for the subform to know which tab it is in when it opens. I have some code that I need to run differently depending on which tab the subform is opening in. in other words, on the 3,7 form I have code that I want to run if it is in the Hour 3 tab during hour 3 but not if it is in the Hour 7 tab.

Everything I've tried seems to run into the same problem, since subforms actually load first, it can't see which tab it is on. If the user clicks another tab, it thinks it's still in the previous tab and the wrong code runs.

I may have to resort to creating duplicate forms for each hour just to accomodate this. That will increase the form count to 8 from 3 and duplicate effort when making changes.

Thanks,

Steve B

 

[spikepl]

Lazy-loading is loading when needed. Remove the reference to the subform from the subform control. Assign the subform to the subform control in the Change event of the tab control.

 

[Steve@trop]

Thanks Spikepl,

I had high hopes for that but it still doesn't work. I tried it in the OnChange event of the tab control and also in the OnClick event for the tabs. I've got a timed refresh that runs every 15 seconds. The initial tab that opens has the correct tab number in the form but when I click tabs after that I have to wait for the timed refresh to run before the tab number updates. I tried using a me.refresh in the onload even on the subform but that hasn't helped.

I might need to re-think how I'm doing this. I had it working fine before when I just enabled/disabled tabs based on time of day. Then I needed to keep some controls usable for all tabs which doesn't work if the entire tab is disabled. I have a loop that runs through the form and disables controls based on a tag I added but I can't get it to work correctly.

Since the subforms aren't related to the main form (not "true" subforms) I'm wondering if I should try using a Navigation form instead of subforms on a tab control. This might give me more control over when the forms open.

 

[Steve@trop]

I may have found a solution but I'm not sure if it is possible or how to do it.

To repeat the issue: I have a tab control with subforms on each tab. Each tab represents an hour during a shift (Hour 1, Hour 2, etc). To simplify things I'll just say that the subforms are the same on all tabs. Can I get a different public function to run in the subform based on which tab the subform is in? The function would disable controls within the form based on the time of day. So if they clicked on Hour 1 and it wasn't 7:00 -7:59 most of the controls would be disabled. But if they clicked on the hour they were in everything would be enabled.

I've been trying to get the subform to figure out which tab it was opened in and then run code based on that. Perhaps I can get the main form to tell the subform to run some code when it opens instead? Is this possible and if so, how do I do it?

I just added an image of the tab control and the subform inside it in case it would be helpful. Each tab/subform is the same sourceobject (but a seperate instance of the form). I'm trying to change the code that runs depending on which tab the form is opened from.

Thanks in advance!

Steve B.

 

[burrina]

Not sure of your Schema. Here is maybe an alternative.


HTH

 

[Simon_MT]

Here is an example of a tabulated Form using this OnChange Event, There is also a Control [PageNo]

Function ArtistsEntry_PageNo()

'       This function is designed to speed up the Artists Forms, by making the History Pages and Consignments On Demand only

    With CodeContextObject
        .[PageNo] = .[TabCtl0]

        If .[ACFlag] <> True And .[PageNo] = 3 Then
            .[ACFlag] = True
            .[Artists Consignments].SourceObject = "Artists Consignments"
        End If
        If .[OHFlag] <> True And .[PageNo] = 4 Then
            .[OHFlag] = True
            .[Artists Originals History].SourceObject = "Artists Entry Originals History"
        End If
        If .[GHFlag] <> True And .[PageNo] = 5 Then
            .[GHFlag] = True
            .[Artists Prints History].SourceObject = "Artists Entry Prints History"
        End If
     End With
End Function

Simon

 

[Steve@trop]

Thanks Simon,

The SourceObject is the same for all the tabs. Each tab simply opens a different record (either a new record if there isn't already one for that hour or an existing record if that hour has been filled out). I've got that part working OK but I'm having trouble making sure the user can't edit hours they've already completed or jumping ahead to hours that haven't occurred, they do need the ability to view the records though. I could simply disable the tabs based on the time of day but I also need them to be able to set previous hours to "downtime" if they missed filling out the form and to add comments to the record. If I disable the entire tab, they can't work with any of the controls on that subform.

I'm actually thinking of surrendering and making a seperate form for each hour instead of re-using them. This will make changes at this stage of development more labor intensive but seeing as I've already fought with this problem for a couple days now, it might be a wash. This way I can vary the code based on the hour number of the form.

 

[Simon_MT]

Somewhat the same principle.

Create a Flag and increment it as each time segment is processed and show the SourceObject progressively on subsequent Tabs.

Simon

 

[Steve@trop]

Flags are a new concept for me. I don't see it as a property on a form at all. I'll have to research how to use them.

 

[Steve@trop]

OK, I see that a flag is just a variable that is set in VBA. Can I set a variable that changes with the OnClick event on the individual tabs? I could then use that in the subform to determine which tab was clicked as long as the variable changes before the subform opens.

 

[Simon_MT]

Exactly!

In the OnChange Property:

Function PageNo()
    With CodeContextObject
        .[Page No] = .[TabCtl0]
    End With
End Function

Simon

 

[vbaInet]

Perhaps you can give us a better picture of what code you're trying to run?

Also, consider this scenario, if you're on tab 1 and hour 1 at the time and making some changes. Then 2 minutes later the hour moves to hour 2, what will happen at this point?

 

[Steve@trop]

Thanks all who have given suggestions. I've decided to use a seperate subform for each hour. It's more forms to manage but I have better control over what becomes active during each hour without regard for which tab the form is on. In cases where the forms are virtually identical, I just need to copy the original and comment/uncomment blocks of code based on the intended hour number of the form.

Thanks again.

Steve B.

 

[Steve@trop]

I thought I'd answer vbaInet. The code I'm trying to run is a loop that goes through and unlocks/enables controls based on a tag and the hour of the day. If the control has no tag, it is left enabled. The code below is for Hour 1 (7AM, 3PM or 11PM):

Dim ctl As Control
    For Each ctl In Controls
        If ctl.Tag <> "" Then
        ctl.Enabled = False
        ctl.Locked = True
        End If
    Next
If (DatePart("h", Now()) = 7) Or (DatePart("h", Now()) = 15) Or (DatePart("h", Now()) = 23) Then
    For Each ctl In Controls
            If ctl.Tag <> "" Then
            ctl.Enabled = True
            ctl.Locked = False
            End If
        Next
End If

The problem is, when I use seperate instances of the same subform, I need it to be able to tell which tab (hour number) it's opening on so that it uses the correct hour number.

As to your second question, I have a timer event that periodically checks the time and changes tabs if the hour has changed. We're still deciding how often the timer should run. If they haven't completed the form on the tab for that hour when it changes, the record is saved as is.

 

[vbaInet]

Ok at which point do you need to run the code?
And can you show me how you are referencing the Page number and from where (i.e. subform or main form?). Perhaps the full code with the sub included will suffice.

 

[Steve@trop]

I've come up with a different way to approach this problem. Instead of using the loop to lock/unlock the controls when each subform opens I'm going to enable/disable the FORM within the tab (instead of the tab itself) from the main form based on the time of day and place the controls I need to always be usable on the tab but outside of the subform. This allows me to re-use the subforms without having to make eight basically identical subforms (one for each hour in a shift). The right controls are disabled and I can add controls that I want to always be available as needed.

The hardest part is going to be getting the control references correct but I'm starting to get the hang of that.

Thanks for all the input.

Steve B.

 

[vbaInet]

I thought the point of looping through the controls was to lock/unlock specific controls based on some criteria? With your new idea you will have to write a lot of code to manage absolutely everything, from saving to synchronising to deleting etc.

If you're going to use one form then you can only open one instance of it and if the user is allowed to use only one form at one time, I don't see the need of tabs? If you want to display some information on the tab's caption you can still do it on a label.

I think we should work on your design first. Anything is possible in Access.

 

[Steve@trop]

The point of the tabs is so that the user can see what was entered during previous hours. It also mimicks the paper form they are using now which has a space for each hour of their shift they are supposed to be taking readings for. I actually tried to eliminate the tabs (they are a PITA) but that is not under my control. The belief is that they help the user see what has been done and what is left to be done (like the paper form).

I have only two controls that need to be "open" all the time. They are related to downtime. If for example, the user missed an hour because the line was down, we need them to be able to enter that so that they don't get penalized for missing those readings. At first I was disabling the entire tab and trying to figure out how to keep the downtime controls available (the loop I was using before), then I realized I could simply lock the subform with all the controls inside it and keep the downtime controls outside the subform on the tab, thus keeping those available.

I've done some experimentation on this idea and think it will work. Each instance of the form is on a tab. When they click the tab the form is locked/unlocked based on the hour of the day and the date. The rowsource of the form is a query that figures out if there is already a record for that hour of day and date, if there is, that data is displayed, otherwise they get a new record. The downtime controls are the only ones that will be outside of the form but will be in the same tab as the instance of the form. Those controls will be linked to hidden controls inside the form so that anything entered in them will be reflected in the proper record.

 

[vbaInet]

Now it's clear.

You could also house those downtime controls in another subform and link it to your parent form, thus allowing Access to handle the navigation and data maintenance.

 

[Steve@trop]

I'm about 90% to where I want to be on this but I've got a new problem. When the form loads the controls are empty until I press F5 on the keyboard. After that, the data that should be there appears. it's exactly what I'm looking for except I don't want the users to need to hit F5.

I've tried using Me.refresh and Me.requery in the OnLoad and OnCurrent events but so far it's not working. It actually gets stuck if I use Me.requery in the OnCurrent event. The recordsource is a query that returns either one record or no records.

Which command and which event should I use to make this work?

Thanks,

Steve B.