code simplify

[patfee]

hi,

i'm trying to simply / uniform my vb code a little and was wondering if the below is possible.

i'm using a different controls which are linked to strings. the stings are used in a query.

for instance i have a textbox (control name COUNTRY_1) which triggers a sting (called COUNTRY) to update on change.

so now i'm using a "if then" statement like
if Left(ctr.Name, (L - 2)) = "COUNTRY" then COUNTRY = ctr.txt

since i'm using a few more textboxed (i.e. query on resource, or unit name, etc.) is have a couple of if then statements more in the code.

i was wondering if i could not use the (ctr.Name, (L - 2)) string to update the COUNTRY text. So something like:

string(ctr.Name, (L - 2))= ctr.text or
variant(ctr.Name, (L - 2))= ctr.text

a little bit the same as ctr(name_from_variable).value = bla bla but then for a string

Thanks for some sugestions

Patrick

 

[VBslammer]

There are a couple of built-in objects that work well for this kind of problem, the Collection object and the Dictionary object, both similar but the Dictionary object exposes more properties. They can do what you're attempting by housing your variables in an array of named variables. A Collection has an Add method in the form:

myCollection.Add Item, [Key], [Before], [After]

Only the first argument is mandatory, but the Key argument allows you to access the item using a string value, which is almost always necessary. For example, using a collection, if your COUNTRY_1 textbox value was "USA" you could add it to the collection with:

myCollection.Add Me![COUNTRY_1], "COUNTRY_1"

Then to retrieve the value from the collection and add it to a query string, you'd use:

strSQL = "SELECT * FROM CustOrders WHERE [COUNTRY]='" & myCollection("COUNTRY_1") & "'"

which would assign the string as:

SELECT * FROM CustOrders WHERE [COUNTRY]='USA'

It will work well in loops using ctl.Name and ctl.Value so once you write the algorithm it will work no matter how many controls are on the form or what their names are. One trick is to set a pre-determined Tag value for each texbox you want to include in the collection so you can check for it in your loops. Here's and example using a collection:

Private mcolVariables As Collection

Private Sub cmdUpdate_Click()
On Error GoTo ErrHandler
  Dim ctl As Control
  Dim varItem As Variant

  [green]'reset the collection:[/green]
  Set mcolVariables = New Collection
  
  [green]'loop through the controls and find the
  'ones that have a pre-determined Tag value,
  'then add their current values to the collection:[/green]
  For Each ctl In Me.Controls
    If TypeOf ctl Is TextBox Then
      If ctl.Tag = "INCLUDE" Then
        mcolVariables.Add Nz(ctl.Value, "Null"), ctl.Name
      End If
    End If
  Next ctl
  
  [green]'loop through the collection and show
  'each member item:[/green]
  For Each varItem In mcolVariables
    Debug.Print "Item: " & varItem
  Next varItem
  
  [green]'get a member item using its Key:[/green]
  Debug.Print "Original Country: " & mcolVariables("COUNTRY_1")
  
  [green]'replace an existing member item:[/green]
  mcolVariables.Remove "COUNTRY_1"
  mcolVariables.Add "KOREA", "COUNTRY_1"
  
  [green]'get the new member item using its Key:[/green]
  Debug.Print "New Country: " & mcolVariables("COUNTRY_1")
  
  [green]'show the total items:[/green]
  Debug.Print "Total Items: " & mcolVariables.count

ExitHere:
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

The Dictionary is similar, but you can access its Keys collection, check for the existence of a particular key, and RemoveAll items. To use a Dictionary you'll have to reference the "Microsoft Scripting Runtime" library. Its Add method is:

myDictionary.Add Key, Item

...so they reversed the order of the arguments, and both are mandatory, but the extra power you get with the Dictionary makes it more useful in my opinion.

Here's how it looks:

Private mdictVariables As Scripting.Dictionary

Private Sub cmdUpdate_Click()
On Error GoTo ErrHandler
  Dim ctl As Control
  Dim varItem As Variant

  [green]'reset the dictionary:[/green]
  Set mdictVariables = New Scripting.Dictionary
  
  [green]'loop through the controls and find the
  'ones that have a pre-determined Tag value,
  'then add their current values to the dictionary:[/green]
  For Each ctl In Me.Controls
    If TypeOf ctl Is TextBox Then
      If ctl.Tag = "INCLUDE" Then
        mdictVariables.Add ctl.Name, Nz(ctl.Value, "Null")
      End If
    End If
  Next ctl
  
  [green]'loop through the dictionary and show
  'each member item:[/green]
  For Each varItem In mdictVariables.Items
    Debug.Print "Item: " & varItem
  Next varItem
  
  [green]'loop through the dictionary and show
  'each member key:[/green]
  For Each varItem In mdictVariables.Keys
    Debug.Print "Key: " & varItem
  Next varItem
  
  [green]'get a member item using its Key:[/green]
  Debug.Print "Original Country: " & mdictVariables("COUNTRY_1")
  
  [green]'replace an existing member item:[/green]
  mdictVariables.Remove "COUNTRY_1"
  mdictVariables.Add "COUNTRY_1", "KOREA"
  
  [green]'get the new member item using its Key:[/green]
  Debug.Print "New Country: " & mdictVariables("COUNTRY_1")
  
  [green]'show the total items:[/green]
  Debug.Print "Total Items: " & mdictVariables.count
  
  [green]'see if a key exists:[/green]
  Debug.Print "COUNTRY_2 exists? " & mdictVariables.Exists("COUNTRY_2")

ExitHere:
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[patfee]

great sugestion!!

from your code examples, i already know that the dictonary example will work perfect!

a small problem with that is that i cant declare the "Private mdictVariables As Scripting.Dictionary"

i get an error "user var not defined"

i know what it means, but i dont know how to solve it.....

thanks!
Patrick

 

[SJMcAbney]

You need to set a reference to the Microsoft Scripting Runtime.

Open a module and select from the menu:

Tools -> References -> Microsoft Scripting Runtime