Select Case and With Statement

[XelaIrodavlas]

Hi All,

Sorry if this is a dumb question, is it possible to use a select case statement in conjunction with a with statement? Specifically I am trying to test if the control sources of different subforms are null.

I could do this easily with nested Ifs, but would prefer to use select case if possible, as this will have quite a few levels when I'm done.

The purpose of this is to test what subforms have been loaded, as they sit in a tab control, and aren't loaded until the user clicks onto that tab (saves time opening the main form) To illustrate, this is what I'm trying to achieve:

Selects case with Forms!MyForm
  
 case !Subform1.controlsource = ""
   msgbox "Subform 1 is unbound!"
  
 case !subform2.controlsource = ""
   msgbox "Subform 2 is unbound!"
  
 end with
 end select

But running this returns a Type mismatch on the first line 'select case'

Any ideas??

Thanks all

 

[Ranman256]

no.
remove the WITH,
or put it outside the SELECT.

 

[XelaIrodavlas]

Ok yes I see why that part was dumb, so assuming I change the code to something like:

With Me!Forms

 select case
 blah blah blah
  
 end select
 end with

How would I go about testing the control source of the individual subforms?

Thanks

 

[moke123]

i'm having a little trouble wrapping my head around this test. in your illustration the select case is only going to return the first true case and if i read your question right you want to test all the subforms. am i reading this right?

 

[Minty]

How many subforms / tab controls are you looking at? I have 5 tabs with about 7 or 8 subforms, apart from the front tab , I only load the tab sub forms on change. I reset all of them then load the one / two in need.

On the tab change event I simply have

Me.subCallCust.SourceObject = ""
me.subCallNoteEntry.SourceObject = ""
etc... Then
SELECT CASE Me.tabServiceCall.Pages(Me.tabServiceCall).Name

CASE "pgCust"
         Me.subCallCust.SourceObject = "subCustDdetail"
CASE "pgNotes"
        Me.subCallNoteEntry.SourceObject = "subNoteAdd"

CASE "pgOrders"
       etc etc

 

[Frothingslosh]

Honestly, you're looking at it the hard way. Keep in mind the following is air code and hasn't been tested, but I'd approach the issue with something like this:

Private Sub BindCheck()
 
Dim SearchList() As String
Dim x As Long
 
     Select Case Me.tabControlName.Value
        Case 0      'Tab 1
            'Create array with list of subforms on this tab
            SearchList = Split("sfmSubform1Name;sfmSubform2Name;sfmSubform3Name", ";", , vbTextCompare)
        Case 1      'Tab 2
            'See tab 1
            SearchList = Split("sfmSubform4Name;sfmSubform5Name;sfmSubform6Name", ";", , vbTextCompare)
        Case 2      'Tab 3
            'See tab 1
            SearchList = Split("sfmSubform7Name;sfmSubform8Name", ";", , vbTextCompare)
        Case 3
            'etc
    End Select
    
    For x = LBound(SearchList) To UBound(SearchList)
        If Me.Controls(SearchList(x)).ControlSource = "" Then MsgBox Me.Controls(SearchList(x)) & " is unbound!"
    Next x
    
End Sub

That said, Minty's approach is decidedly superior in the long run.

If you really had your heart set on SELECT/CASE with multiple checks, it's a bit of a pain in the rear. You can check out THIS article to get an idea of how to do that.

 

[static]

Ok yes I see why that part was dumb, so assuming I change the code to something like:

With Me!Forms
select case
 blah blah blah
  
 end select
 end with

How would I go about testing the control source of the individual subforms?

Thanks

with Forms!MyForm
    Select case true
        case !Subform1.controlsource = "": msgbox "Subform 1 is unbound!"
        case !subform2.controlsource = "": msgbox "Subform 2 is unbound!"
    end select
 end with

Of course select stops at the first match, so if Subform1.controlsource = "", subform2.controlsource wont be checked.

 

[XelaIrodavlas]

Thanks Static this is just what I needed, I didn't realise you could flip Select Case and use True on the top like that.

Of course select stops at the first match, so if Subform1.controlsource = "", subform2.controlsource wont be checked.

This is actually the other reason why I wanted to use a select query in the first place.

I wasn't going to bore people with the convoluted reasoning, but basically this is part of an on-timer event procedure, which detects when the user has been idle for a certain time, and then loads one of the subforms if they are.

The point is I don't want to load all of them at once, because that could take forever, but by putting them in order of most popular, I can now get Access to check each one and if its not loaded, load that one and then exit sub. If the user is still idle in another 5 minutes it will load the next one, and the next until all of them are loaded.

This way I can load some of the more labour-intensive queries while the user's doing something else, and create the illusion of a much faster database.

Maybe that's being sneaky? Maybe I should charge money for it? All I know is if I did charge money I'd have to give you guys a cut

Thanks again!

 

[Minty]

If you only load them when they select the tab you won't have any overhead until they need the sub form. By removing the record source when they come off the tab you aren't keeping the forms recordset in memory.