Access Visual Basic: Create a string array to search through when needed

Question

So I am trying to create a string array in microsoft access visual basic that will hold a certain number of States names. I also will have a form that will allow people to do data entry on. If they select a one of the states that happen to be in this array from a drop down list of ALL the states on the form, I want it to activate a msgbox that will popup and tell them a specific message.

So far all I've been able to find is code somewhat like this:

Dim State(3) As String
Dim Str As String
State(0) = "California"
State(1) = "Florida"
State(2) = "New Hampshire"
State(3) = "Illinois"
For Each Str In State
  If State.Contains(Str) Then
    MsgBox("Found " & Str & " at index " & State.IndexOf(Str))
  End If
Next

Basically what my main issue is is that right now the code has the equivalent of if the entered data = this state or if it = this state or if it = this state etc. all with inline continuations, then do the msg box. The problem that gives is that you can only effectively have 24 continuations in access vb, and I now have over 25 states that I need to check for. Also, I inherited this mess of code and am unable to completely trash it and rewrite so I figured using a string array might be a workable fix.

@Steve Not sure if vba or not but all the used syntax is present in vb.net — Mederic
– Mederic, Commented May 4, 2017 at 13:24

Andre · Accepted Answer · 2017-05-04 13:25:15Z

1

Hardcoding a list of states is a bad idea.

You have the power of a database and Access forms at your disposal, why not use them?

Add a Yes/No column to your states table, for the flag that prompts the message.

Add this column to the row source of the (I assume) combobox where the users select a state, with width 0 so it is invisible.

Then in the AfterUpdate() event of the combobox, you do

If Me.cboStates.Column(1) = True Then
    MsgBox "flag!"
End If

This assumes that the state name is in the first column (0).

answered May 4, 2017 at 13:25

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mederic Over a year ago

Nice one! upvote I didn't think of using an extra column original idea I like it!

mooch81 Over a year ago

This seems like the best answer, and I didn't even think of modifying the table initially. My biggest issue with this is that it's a junky in production data entry database that was created by someone I don't know a while ago, and my boss barely will let me do anything to it because she's scared everything will fall apart. I'll definitely look into this option and see if I can get it to work. It seems like it would be really simple to implement. Thanks again!

Andre Over a year ago

:) Adding the column and greatly simplifying the code most probably won't break anything. @mooch81

Mederic · Accepted Answer · 2017-05-04 13:24:51Z

0

Not sure if I understood well but If your trying to make short version to show the index chosen state in MsgBox you could do this:

Public Sub GetStates() As List(Of String)
    Dim CurrentStates As New List(Of String)
    For each result in your sql query like: "SELECT Name, From States"
        CurrentStates.Add(result)
    Next
End Sub

Then you could something like:

Dim States As New List(Of String)
States = GetStates()
If States.Contains("UserInput") Then
    MsgBox("Index: " & States.IndexOf("UserInput")
Else
    MsgBox("State is Unknown")
End If

edited May 4, 2017 at 13:24

answered May 4, 2017 at 13:19

Mederic

2,0545 gold badges23 silver badges37 bronze badges

Collectives™ on Stack Overflow

Access Visual Basic: Create a string array to search through when needed

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related