Passing variables (1 Viewer)

[David Mack]

I’ve been building an application to do word searches in those puzzle grids for my youngest son. You know, the kind where words are placed backwards, diagonally, forward, vertical, horizontal? Well , the app works fine and locates everything. My dream was to have the app find the word and then highlight it on a form.

Background. The data is entered in a 27x27 (any bigger is beyond the Access control limit) grid in an Excel (easy data entry). The worksheet is linked into an A2000 db. I have a search form which starts up my search module. I have also built a form on the fly which makes the 729 little label controls and makes the caption the value in the linked table. The code works with an array, and the location of the first letter of the word is passed back with an x,y coordinate. I want the respective label control on the form to change color. When the form on the fly is built, the name of every label control is given a Name of the assigned x,y coordinate separated by a dash. Left upper corner would be 0-0 and the lower right corner would be 28-28.

The point is I need to reduce code by passing the x-y coordinate into the control name but I am having trouble. The string looks fine and I thought I could use the Eval command. Could someone help?

--This works from my module
Forms![frmWordSearch]![2-0].BackColor = 123123

--Tried many variations of this and other concatenations to no avail
Eval ("Forms![frmWordSearch]![" & InStr(1, strForward, strSearchWord, vbBinaryCompare) & "-" & y & "].BackColor = 123123")

Thank you,

Dave

 

[Travis]

Try this

Forms![frmWordSearch].Controls(InStr(1, strForward, strSearchWord, vbBinaryCompare) & "-" & y ).BackColor = 123123

or if the code is on frmWordSearch

Me.Controls(InStr(1, strForward, strSearchWord, vbBinaryCompare) & "-" & y ).BackColor = 123123

 

[David Mack]

That works like a champ. Thanks Travis! I never thought about working the controls collection. So, what's the Eval function useful for? I thought it takes a string and evaluates it.

Once again,

Thank you!

Dave

 

[Travis]

According the MSDN:

You can use the Eval function to evaluate an expression that results in a text string or a numeric value.

You can construct a string and then pass it to the Eval function as if the string were an actual expression. The Eval function evaluates the string expression and returns its value. For example, Eval("1 + 1") returns 2.

If you pass to the Eval function a string that contains the name of a function, the Eval function returns the return value of the function. For example, Eval("Chr$(65)") returns "A".

 

[David Mack]

I guess I gave the Eval function a little more credit than it was due.

Thanks!

Dave