Get value from textbox and validate it from another textbox

[rockyjr]

Hi everyone,

Using : Access 2007

I have built a floor plan for my company. These's about 200 different desk locations on a form. Each of them are named from desk701001 to desk801223. Anyways, that doesnt matter.

What I would like to do is to build a search button that looks at all those textboxes on that form, find to one that = to "txtsearch" (which is going to be whatever the user inputs in a textbox) and change the color of the font to red for that it is easy to find.

I know how to do it manually by checking each and everyone at a time, but that would be too long. There should be a way to do this a little easier. I've been checking around and cant seem to find exactly what I'm looking for.

I tried this (dont laugh):

Dim ctl As Control
 
For Each ctl In Me.Controls
If ctl.Control = Me.txtsearch Then
ctl.ForeColor = vbRed
Else
ctl.ForeColor = vblack
ctl.SetFocus
Exit For
End If
Next ctl

But it keeps giving me the error:
Run time error 438
Object doesnt support this property or method

Help please!

Thanks in advance.

Luc

 

[pbaldy]

On what line? Off the top, this would likely need to be:

If ctl.Name = Me.txtsearch Then

Or perhaps the caption property, if that's where the value is.

 

[rockyjr]

I added "thedesknumber" under Control Source of the textbox if thats helps.

I tried what you gave me (ctl.Name = txtsearch) but that doesnt too. One good thing is that it doesnt gave me an error anymore

So, ya... not returning what I hoped for.

Luc

 

[pbaldy]

Your code will drop out of the loop as soon as it hits a control that doesn't match; is that really what you want? It may be working "as designed", just not doing what you want.

 

[rockyjr]

No, I dont want it to stop. I would like it to check all of the textboxes and loop until it checked all the textboxes.

 

[pbaldy]

So take out

ctl.SetFocus
Exit For

 

[rockyjr]

Ok.... that was dumb!!!... outch!!

Ok... have a little something again......

Here's the code:

Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.Name = Me.txtsearch Then
    ctl.ForeColor = vbRed
    Else
    ctl.ForeColor = vblack
    End If
Next ctl

....I'm getting an error at ctl.ForeColor = vblack. Same error as before ...Runtime 438.

Any ideas... I dont know how to pass the ctl to the text field to change the color.

 

[pbaldy]

Because you're looping all controls, you're probably hitting one that doesn't have a ForeColor property. I would either restrict the loop to text boxes or use the Tag property of the controls to limit the loop to the desired controls. Those would look like:

If ctl.ControlType = acTextBox Then

If ctl.Tag = "IncludeMe" Then

 

[rockyjr]

That's awesome...

This is what I finished with :

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
    If ctl.OldValue = Me.txtsearch Then
    ctl.ForeColor = vbRed
    Else
    ctl.ForeColor = vblack
    End If
End If
Next ctl

It didnt like the If ctl.Name......
So I changed it to :
If ctl.OldValue = Me.txtsearch Then

It works fine. I dont know if this is correct but it works fine!

Thanks a million pbaldy!

Luc

 

[pbaldy]

Odd, as ctl.Name should return the name of the textbox. OldValue contains the value in the textbox, which I wouldn't have thought was what you wanted. Anyhow, glad it's doing what you want now.

 

[rockyjr]

Using the same funtion... is there a way to find duplicates??

 

[pbaldy]

If you mean to find out if there are more than one equal to a given value, sure. To simply compare them all to see if there are any duplicates at all, more tricky. What exactly are you trying to accomplish? Are the values in a table?