Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 19th, 2006, 04:44 PM
Authorized User
 
Join Date: Oct 2006
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Switch Case statements

Is there a way to do a switch case statement in VB like you can in C++ or do I need to write a bunch of If Else statements? I tried to do this line and it doesn't look like it's actually looking at the object that strDevice(iIndex) represents. I tried brackets around it and that doesn't work. I tried ['" & strDevice(iIndex) & "'] as well as without the brackets to no avail. strDevice(iIndex) is an array of CS, CS-A, CS-B, etc.

If (Not bLoop And (Not IsNull(strDevice(iIndex)))) Then...

If there's a way to do it in one line like I attempted here, that'd be awesome, but if not, I'd like to do it somewhat less efficiently in a switch case statement.
All help is appreciated! Thanks in advance.
 
Old October 19th, 2006, 05:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

The VB analog for the 'switch' keyword (also used in C#) is the Select Case statement. From the VBA helpfile:

Code:
Dim Number
Number = 8    ' Initialize variable.
Select Case Number    ' Evaluate Number.
Case 1 To 5    ' Number between 1 and 5, inclusive.
    Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8    ' Number between 6 and 8.
    Debug.Print "Between 6 and 8"
Case 9 To 10    ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else    ' Other values.
    Debug.Print "Not between 1 and 10"
End Select
However, I don't see the problem with your code as written.

Code:
Sub Test()
     Dim n(0) As Integer
     Dim b As Boolean
     n(0) = 1
     b = False

     If (Not b And (Not IsNull(n(0)))) Then
        Debug.Print "Run"
     End If
End Sub
The above creates an array with a single element, initializes that element to an Integer, accesses the element at its index, evaluates for null, and the Debug statement runs. Seems like a perfectly valid line of code.

What do you mean by "it doesn't look like it's actually looking at the object that strDevice(iIndex) represents"?

HTH,

Bob

The Following User Says Thank You to Bob Bedell For This Useful Post:
gjgriffith (April 10th, 2012)
 
Old October 19th, 2006, 08:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Come to think of it, I don't think String arrays can store Null values in VBA anyway, so evaluating their elements for Null wouldn't make any sense. In VBA, I believe the only type of array elements you could assign Null values to would be elements of a Variant array.

Seems to me you would want to do your Null check elsewhere, i.e., when assigning values to the String array elements in the first place. Then if the value you are attempting to assign to the element is a Null, replace it with an empty string.

Code:
Sub Test()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim intIndex As Integer
    Dim astrArray(1) As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenSnapshot)

    intIndex = 0

    With rst
        .MoveLast
        .MoveFirst

        ' If value of Field object is Null,
        ' place empty string in array element.
        For Each fld In rst.Fields
            If IsNull(fld.Value) Then
                astrArray(intIndex) = ""
            Else
                astrArray(intIndex) = fld.Value
                intIndex = intIndex + 1
            End If
        Next fld

    End With

    rst.Close: Set rst = Nothing
    dbs.Close: Set dbs = Nothing

End Sub
Then you could re-write your code stub as:

Code:
If (Not bLoop And Len(strDevice(iIndex)) > 0) Then...
Might want to check your data, but I would be extremely surprised if you are actually loading any Null values at all into your array (assuming its a string array, as your code suggests).


HTH,

Bob

 
Old October 20th, 2006, 08:58 AM
Authorized User
 
Join Date: Oct 2006
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

strDevice = Array("CS", "CS-A", "CS-B", "CS-C", "CS-D", "CS-E", "CS-F", "IO", "IO-A", "IO-B")

Each value of the array (CS, CS-A, etc.) is a field (combo box) in form A and also a column in table A. However, when they transfer to Form B (and table B), they will all be in the format CS?### or CS?###-? under one column titled devicename and the value found in the combo box will be under another column labeled type. I'm trying to verify that the type field (combo box) from Form A was null. I have a feeling the only way to do this is with the select case.
Thank you very much for your quick and helpful response Bob!





Similar Threads
Thread Thread Starter Forum Replies Last Post
CASE Statements in T-SQL atcs2152 SQL Server 2000 3 April 28th, 2006 10:53 AM
Case Statements cfriedberg SQL Server 2000 1 September 7th, 2005 08:46 PM
ASP switch(variable){} and case: JohnD Classic ASP Basics 3 September 18th, 2004 09:05 PM
case switch thtrance BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 0 August 26th, 2004 02:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.