Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 19th, 2006, 04:44 PM
Authorized User
 
Join Date: Oct 2006
Location: , , .
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.
Reply With Quote
  #2 (permalink)  
Old October 19th, 2006, 05:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 10 Times in 9 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

Reply With Quote
The Following User Says Thank You to Bob Bedell For This Useful Post:
gjgriffith (April 10th, 2012)
  #3 (permalink)  
Old October 19th, 2006, 08:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 10 Times in 9 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

Reply With Quote
  #4 (permalink)  
Old October 20th, 2006, 08:58 AM
Authorized User
 
Join Date: Oct 2006
Location: , , .
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!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 06:44 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.