SSTab still giving me trouble

[MarcMellor]

I am trying to use the SSTab control to display different selections of information in MSHFlexGrids from a single database using a single Adodc control. Unfortunately I cannot find a click event that recognises which of the 12 tabs is being selected so that I can attach code to change my selection from the database. What I have currently is this:
Private Sub SSTab1_Click (Previous Tab As Integer)
Select Case PreviousTab
Case 0
Adodc1.RecordSource = "SELECT(*) FROM PupilResultsData_ WHERE Module = '1'"
Adodc1.Refresh
Case 1
Adodc.RecordSource = "SELECT(*) FROM PupilResultsData_
WHERE Module = '2'"
Adodc1.Refresh
etc, etc, etc
End Select
This does work up to a point. The selection on the MSHFlexGrid changes as I shift from the Module 1 tab to the Module 2 tab. Unfortunately it seems to change it to the correct selection for the PREVIOUS tab, not the current one. "Obviously, you twit" I hear you say,"You are using Select Case PreviousTab, what do you expect." So I tried changing the code to:
Private Sub SSTab1_Click (Current Tab As Integer)
Select Case CurrentTab
etc, etc
and it does exactly the same thing!
I have tried getting round the problem by trying to use:
.........
If SSTab1.TabCaption(0) = "Module 1" Then
Adodc.RecordSource = .........etc
but this doesn't work
There's got to be a way. Any ideas anyone?

 

[Foada]

If I Understand what you are saying you should be using
If SSTab1.Caption = "Module 1" Then
Adodc.RecordSource = .......etc

Maybe ??????

 

[MarcMellor]

OK, Foada, but this gives me the same problem. When I move from one tab to the next, it gives me the correct selection for the tab I have just left, not the one I have just arrived at. These tabs are all on the same SSTab control, by the way. It seems to me that the problem is that the click event has this (PreviousTab As Integer) attached to it, and deleting these brackets after it doesn't change what the click event does, which is refer back to where you were, not where you now are.

 

[evoluder]

Have you tried:

Private Sub SSTab1_Click (Previous Tab As Integer)

if sstab1.tab = 0 then
Adodc1.RecordSource = "SELECT(*) FROM PupilResultsData_
WHERE Module = '1'"
Adodc1.Refresh
elseif sstab1.tab = 1 then
Adodc.RecordSource = "SELECT(*) FROM PupilResultsData_
WHERE Module = '2'"
Adodc1.Refresh
etc, etc, etc
end if

 

[Foada]

Just so I am clear on exactly what you have going on.

You have an SSTab1 control with 12 tabs. The captions on the tabs are "Module 1","Module 2" ...,"Module 12"

You are using the SSTab1_Click(PreviousTab As Integer) event

If you use the following code for the event
Private Sub SSTab1_Click(PreviousTab As Integer)
Dim sTmp As String

sTmp = SSTab1.Caption
MsgBox sTmp, vbInformation, "I Am"
End Sub

The Message Box Prompt does not correspond to the tab you just clicked? Anything is possible, the problem is I only have one lifetime.

 

[MarcMellor]

Yes, evoluder, I have, but I just checked it again to have a closer look - very strange! Initially it isn't quite right but if I keep clicking through the tabs in a regular way - 0,1,2,1,0,1,2,1,0 etc it works fine. But as soon as I skip one - 0,2,1 it fouls up again - work that one out!

I tried your little test, foada and it works fine, the message DOES correspond to the tab I've just clicked perfectly, whichever order I do it in! Your description of what I am doing is exactly right, too. Does this mean we're getting somewhere?

 

[Foada]

So Can We Do This

Private Sub SSTab1_Click(PreviousTab As Integer)
Dim sTmp As String

sTmp = SSTab1.Caption
Select Case sTmp
Case "Module 1"
Adodc1.RecordSource = "SELECT(*) FROM
_PupilResultsData WHERE Module = '1'"
Adodc1.Refresh
Case "Module 2"
'Next SQL Blah Blah
'Code
'Code
'Code
End Select
End Sub

Is That what we are shooting for or am I still missing something? Anything is possible, the problem is I only have one lifetime.

 

[MarcMellor]

Tried your suggestion, foada, and got the same problem, so long as I stick to an orderly sequence - Module 1, 2, 3, 2, 1, 2, 3, 2, 1 it is a step behind. If I skip from Module 1 to 3 or the otherway round I can get it right and then its fine while I am back on the orderly sequence. Go off this and I,m back to a step behind. I thought I'd catch it out by sticking the MsgBox back in .... it quite happily gives me the Module 3 selection and sticks its tongue out at me by displaying Module 2 in the MsgBox!

 

[sivav]

Try this out , it should work . I use the same kinda code to change the recordsource.

Private Sub SSTab1_Click(PreviousTab As Integer)
Select Case Me.SSTab1.Tab
Case 0
MsgBox "Zero"
Case 1
MsgBox "One"
Case 2
MsgBox "Two"
Case Else
'do nothing
End Select
End Sub

 

[strongm]

Try the following:

Private Sub SSTab1_Click(PreviousTab As Integer)
Adodc1.RecordSource = "SELECT(*) FROM PupilResultsData_ WHERE Module = '" & SSTab1.Tab & "'"
Adodc1.Refresh
End Sub

 

[MarcMellor]

Nope, Sivav and Strongm, both produce the same problem - starts out of step, skip a tab and your in step, do it again and your back out. And yours was so nice and elegant, Strongm .....

 

[sivav]

Chk this out MarcMellor:

Private Sub SSTab1_Click(PreviousTab As Integer)
Adodc1.RecordSource = "SELECT(*) FROM PupilResultsData_
WHERE Module = '" & SSTab1.Tab + 1 & "'"
Adodc1.Refresh
End Sub

 

[strongm]

Odd. I tested the code here with 12 tabs (although I replaced Adodc.recordsource with strTestString since I didn't want to set up a test database) and it worked fine.

 

[snalwala]

Dammit, it works fine here too.

 

[MarcMellor]

The problem with strTestString is that its like the MsgBox suggested by Foada above - and this does indeed work correctly at the very same time as the wrong selection from the database is showing. I have stuck "Module 1", "Module 2" etc into appropriate records in the database to make absolutely sure I'm not making a mistake.
I have found out something interesing though. With this code:
Private Sub SSTab1_Click (PreviousTab As Integer)
Adodc1.Recordsource = "SELECT(*) FROM PupilResultsData_
WHERE Modules = '" & SSTab1.Tab + 1 & "'"
Adodc1.Refresh
End Sub
If I click on the tab labelled Module 2 (from tab, Module 1) there is no change but when I click on tab, Module 3 I get the Module 2 selection. But if I change the code to this:
Private Sub SSTab_Click (PreviousTab As Integer)
Adodc1.Refresh
Adodc1.RecordSource = "...etc. etc"
Adodc.Refresh
When I click on the Module 2 tab, I do indeed get the module 2 selection but as soon as I click on the Module 3 tab , there is no change!

 

[Foada]

Why are we back to this code:

Private Sub SSTab1_Click (PreviousTab As Integer)
Adodc1.Recordsource = "SELECT(*) FROM PupilResultsData_
WHERE Modules = '" & SSTab1.Tab + 1 & "'"
Adodc1.Refresh ^
End Sub ^
This
Instead of:

Private Sub SSTab1_Click (PreviousTab As Integer)
Adodc1.Recordsource = "SELECT(*) FROM PupilResultsData_
WHERE Modules = '" & SSTab1.Tabcaption + 1 & "'" ^
Adodc1.Refresh ^
This
End Sub

I am really getting confused on where this is misbehaiving. I can't get my control to put anything but it's very own caption in the msgbox regardless of which order I select(and I must have done 500 different clicks). I am wondering if you are clicking really fast through the tabs and the adoodc is not refreshing fast enough??? Anything is possible, the problem is I only have one lifetime.

 

[MarcMellor]

SOLVED IT !!!!! (I'm back to that code, by the way, because it is the most elegant and none of the others resolved the problem any better anyway).
It occured to me that if putting a refresh statement before the select statement made a difference to its operation, then perhaps it wasn't getting enough refreshment. So I put 2 refresh statements after the select statement. An absurd idea, but, guess what, it worked! Well for a while ... So, on the grounds that "Too much of a good thing can be wonderful" I added a second repeat select statement to go with the second refresh and lo and behold, it worked ....... for longer, but eventually went wrong. Now I have 3 identical select statements with following refresh statements, and I have been back and forth in every possible combination of ways and I cannot get it to go wrong. AT LAST !
You're absolutely right, Foada, the message statement is always right. I did it backwards and forwards over and over again, never getting the message wrong, but regularly getting the selection wrong at the same time, ie the message would say Module 2 while the selection contained module 3.
As you can probably tell I am a rank amateur on VB, but I can't help thinking that if simply repeating the code 3 times solves the problem (and I think the problem is to do with the connection to the database, so is not shown up by the message box) then there is something wrong with either MY version of the SSTab tool or else this has exposed some problem in the way SSTab works.
It might be a good idea if someone replicated EXACTLY what I'm doing by setting up a simplified database with a single column (text) headed "Modules" and a few 1's, 2's, 3's etc. as records to see if only the 1's show in the MSHFlexGrid on the first tab, the 2's in the MSHFlexGrid on the second tab etc using a single select and refresh statement. OK, OK, I know, who's got time to mess around with problems that have been successfully circumvented! Still, I wouldn't mind knowing I'm not going mad.
I will look on with interest. Thanks to you all, in triplicate, ho, ho!

 

[strongm]

<sigh>

This is the first time you've mentioned the MSHFlexgrid. That's the problem, and is easily fixed thus:

Private Sub SSTab1_Click(PreviousTab As Integer)
Adodc1.RecordSource = &quot;SELECT * FROM PupilResultsData WHERE Modules = '&quot; & SSTab1.Tab & &quot;'&quot;
Adodc1.Refresh
Set MSHFlexGrid1.DataSource = Adodc1 ' rebind hierarchical flexgrid to datasource
End Sub