Access VBADiscuss 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 .
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.
The VB analog for the 'switch' keyword (also used in C#) is the Select Case statement. From the VBA helpfile:
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"
However, I don't see the problem with your code as written.
Dim n(0) As Integer
Dim b As Boolean
n(0) = 1
b = False
If (Not b And (Not IsNull(n(0)))) Then
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"?
The Following User Says Thank You to Bob Bedell For This Useful Post:
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.
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
' 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) = ""
astrArray(intIndex) = fld.Value
intIndex = intIndex + 1
rst.Close: Set rst = Nothing
dbs.Close: Set dbs = Nothing
Then you could re-write your code stub as:
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).
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!