Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional DIM (XP)

    I am using a COM interface to a 3rd party product. I am able to modify object data in the TPP using COM. Each object in the TPP has a data element called 'User Text'. I want the user (in my database) to be able to choose the object they want to deal with and then modify the case of the text for all instances of that object in the target TPP. I have a function that fixes case. My user can select the object from a Combo Box and then I can call a procedure with this value. However, the value that is passed determines how I DIM and SET some things in the procedure. For example:

    If the object chosen is Activities then I want to:
    Dim Coll As SplusServer.Activities
    Dim Obj As SplusServer.Activity
    Set Coll = SPlusApp.ActiveCollege.Activities
    If the object chosen is Modules, I want to:
    Dim Coll As SplusServer.Modules
    Dim Obj As SplusServer.Module
    Set Coll = SPlusApp.ActiveCollege.Modules
    I have 26 objects that can be chosen. I guess I can write 26 procedures but I was hoping that there would be a cunning way of conditionally DIMing things in a single procedure. Conditional Compilation is not the answer as I want this to work differently each time the procedure is called.

    Any ideas?

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional DIM (XP)

    How about using a "Case" statement.
    Attached Images Attached Images

  3. #3
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional DIM (XP)

    Dave,

    Thanks for this. I thought I had tried the Case statement and the compiler objected to the repeated DIM statements. But I will try it again.

    Graeme.

  4. #4
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional DIM (XP)

    Dave,

    Tried that - got a Duplicate Declaration in current scope. That's the problem I have been having - I need to do a conditional declaration. Hmmm.

    Thanks.

    Graeme.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Conditional DIM (XP)

    I don't know what TPP is so don't know if this would work. If the objects in question can be instantiated using the New keyword, you could use something like this, declaring a generic Object variable:

    Public Sub TestCondDim(ByRef intOpt As Integer)
    On Error GoTo Err_Handler

    Dim obj As Object
    Dim n As Integer
    Dim strCmd As String
    Dim strMsg As String

    strCmd = "SELECT * FROM TABLE1;"

    Select Case intOpt
    Case 1
    Set obj = New ADODB.Command
    ' do something
    Case 2
    Set obj = New ADODB.Connection
    ' do something else
    Case 3
    Set obj = New ADODB.Recordset
    obj.Open strCmd, CurrentProject.Connection, adOpenStatic
    MsgBox obj.RecordCount & " records.", vbInformation, "RECORD COUNT"
    obj.Close
    End Select

    Exit_Sub:
    Set obj = Nothing
    Exit Sub

    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub
    End Select

    End Sub

    When tested with option 3, an ADO recordset was opened successfully. Of course this method creates object using late binding, which may be less efficient than early binding, and you get no Intellisense - you'd have to enter object properties/methods "manually" (or use "real" object variable to write correct syntax, then replace with generic Object variable). If the class objects in question do not support instantiation with New, then you may be stuck with writing separate routine for each type of object. The other option would be to use CreateObject function:

    Set obj = CreateObject("strObjType")

    Your sub could specify object type (as string) based on user selection. However, note same restriction applies: if the class that defines the object has its instancing attribute set to PublicNotCreatable, it is visible but can't be instantiated from outside the project using the New keyword or CreateObject function. It can only be instantiated by accessing the property or method of a higher-level object. For example:

    Dim wb as New Excel.Workbook
    Dim pvtbl as New Excel.PivotTable

    The first declaration is valid; Excel allows you to instantiate a new Workbook object. The second declaration is invalid. You'd have to use the Workbook PivotCaches object Add method to create a new pivot table in a workbook. Since I don't know what "TPP" is, I don't know how this will apply in your case.

    HTH

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional DIM (XP)

    Have you tried dimming the objects as Object? Then you could set them to the appropriate object when you know what it is.

    Dim Coll as Object
    Dim Obj as Object

    Then you would need code to determine what was chosen and use that conditional branching to instantiate the objects:

    Set Coll = SPlusApp.ActiveCollege.Activities
    Set Obj = SPlusApp.ActiveCollege.Activitiy

    I'm guessing on the Set statements but you get the idea. You don't conditionally dim the variable, you dim it as a generic object and then set it to the specific object you need.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional DIM (XP)

    Many thanks. Worked a treat. Here is a sample of the code I used.

    Graeme.

    Dim Coll As Object
    Dim Obj As Object
    Select Case Objct
    Case "ACT"
    Set Coll = SPlusApp.ActiveCollege.Activities
    Case "AT"
    Set Coll = SPlusApp.ActiveCollege.ActivityTemplates
    . . . . . . .. . . . . . . . .
    End Select
    For Each Obj In Coll
    If Conv = "M" Then
    Select Case Fld
    Case 1
    Obj.UserText1 = mcase(Obj.UserText1)
    Case 2
    Obj.UserText2 = mcase(Obj.UserText2)

  8. #8
    Star Lounger
    Join Date
    Aug 2003
    Location
    Brisbane, Queensland, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional DIM (XP)

    Thanks Mark. I got a similar suggestion from Charlotte and it works fine. Here is a sample of the code I am using.

    Graeme.

    Many thanks. Worked a treat. Here is a sample of the code I used.

    Graeme.

    Dim Coll As Object
    Dim Obj As Object
    Select Case Objct
    Case "ACT"
    Set Coll = SPlusApp.ActiveCollege.Activities
    Case "AT"
    Set Coll = SPlusApp.ActiveCollege.ActivityTemplates
    . . . . . . .. . . . . . . . .
    End Select
    For Each Obj In Coll
    If Conv = "M" Then
    Select Case Fld
    Case 1
    Obj.UserText1 = mcase(Obj.UserText1)
    Case 2
    Obj.UserText2 = mcase(Obj.UserText2)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •