Why it doesn't work???

[QWERTY]

I was trying to run this code in VBA and it doesn't work. I got an error saying "Object doesn't support this property or method" (it stops in very first line of For Each statement. I tried to do it in Visual Basic and it worked. Do you have any ideas why it doesn't work in VBA?
Here is the code:

Private Sub txtFirstWestOne_Change()
Dim stText As String
stText = txtFirstWestOne.Text

For Each Control In Me
If TypeOf Control Is TextBox Then
If Control.Text = stText Then
MsgBox "Something"
End If
End If
Next
End Sub


Thanks, and happy Thanksgiving!!!

------------------
Visual Basic Programmer (at least I want to be one)
------------------
PolComSoft
You will hear a lot about it.



[This message has been edited by QWERTY (edited 11-25-1999).]

[chrisjk]

You would be using VBA because it's the language of MS Access (and Word 97 and Excel 97 etc). Before I got VB6 I used to use Access to write stuff, and beleive it or not I did use VB sites to get code. The majority of it is transferable, but VBA has some extra bits, and some bits taken off, so they are in effect the same langauge, just with slight modifications.

Anyway - the answer is this. You need to declare the controls as a variable of type Control. Replace what you typed with this code...I'm assuming you are using Access, I don't know about Word or Excel but for Access this works.

Dim stText As String
stText = txtFirstWestOne
Dim ctlFormControls As Control

For Each ctlFormControls In Me
If TypeOf ctlFormControls Is TextBox Then
If ctlFormControls = stText Then
MsgBox "Something"
End If
End If
Next

You cannot use the text property in VBA/Access, it doesn't support it unless the control has focus.

Regards,

------------------
- Chris
chris.kilhams@btinternet.com


[This message has been edited by chrisjk (edited 11-25-1999).]

[This message has been edited by chrisjk (edited 11-25-1999).]

[Yonatan]

First, perhaps you should declare Dim Control As Control in the beginning of txtFirstWestOne_Change.
Also, it must be in a code module of a UserForm.
If you have the above two and it still doesn't work, change For Each Control In Me to For Each Control In XYZ (replace XYZ with the name of the UserForm).

------------------
Yonatan
Teenage Programmer
E-Mail: RZvika@netvision.net.il
ICQ: 19552879 (http://www.icq.com/19552879)
AIM: RYoni69

If VBA.Quality = Pathetic Then
Form1.Print "Macro's suck" & String(4,"!")
End If



Macro's suck!!!!

[chrisjk]

If you want to be pedantic then you could do the following

Dim Sun As BrightThing
Dim Cows As FarmAnimals
Dim Aliens As IntelligentLifeforms
Dim MobilePhone As Annoying Thing That People Always Answer "Where am I? I'm In [Insert store name], buying some clothes for Aunt Fanny. There are lots of people, I'm in a queue and my legs hurt"

The Me keyword is used to replace the name of the form that the code is currently being executed from. It allows transfer of code between said forms with minimum of fuss.

If it ain't broke -don't fix it! :). I think I might put that in my signiture, cool!

Regards,

------------------
- Chris
chris.kilhams@btinternet.com

[chrisjk]

Just checking my new signiture!

------------------
- Chris
chris.kilhams@btinternet.com
If it ain't broke - don't fix it :)

[QWERTY]

Thanks for you reply.
I agree Macros suck

------------------
Visual Basic Programmer (at least I want to be one)
------------------
PolComSoft
You will hear a lot about it.

[HeSaidJoe]

Visual Basic and VBA are not the same language.

[JohnDoe13579]

VBA is a cheap editor for Visual Basic... Why would you be using vba, anyway(email me)