Access is slower programming than Excel. No Intellisense

[Webtechie]

Hello,

I am used to developing in Excel. I am trying to develop in Access. It is
taking forever. I'm trying to use VBA to do simple things.

with Excel, if I need to get a value from a userform, it is

frmEmployee.txtFirstname.text (intellisense kicks in when I press the ">.")

with Access, I am seeing examples of Forms!FrmEmployee!txtFirstname.value
with no intellisense.

Am I missing something or is this the proper way to reference a control's
value on another form? Why can't you use intellisense to see the controls on
another form?

Thanks.

 

[Doctor]

You can just type Me.txtFirstName to reference a control. You do not need to
put .text after that to get what is typed into the box, but you can if you
want to.

 

[Dirk Goldgar]

You can just type Me.txtFirstName to reference a control. You do not need
to
put .text after that to get what is typed into the box, but you can if you
want to.

You shouldn't. A control's Text property (if it has one) is not the same as
its Value property, and is only available when the control has the focus.
For most normal purposes, the Value property is what you want to use.
That's the default property of any data-bound control, and that's what you
get when you refer to the control without specifying any particular
property.

 

[John W. Vinson]

You can just type Me.txtFirstName to reference a control. You do not need to
put .text after that to get what is typed into the box, but you can if you
want to.

..Text is actually a bit different in Access than in other sorts of Visual
Basic: it applies ONLY if the control has the focus, and returns what the user
has typed into it. If you want the contents of the control in other contexts
you need to use the default property, .Value.

 

[Webtechie]

You might have missed it, but I'm trying to get the value of a control on
ANOTHER form.

I don't think I can use me.txtEmployee.value to get the value of a control
on anothe form. This VBA programming is slower than programming with Excel
VBA.

Tony.

 

[Dirk Goldgar]

Hello,

I am used to developing in Excel. I am trying to develop in Access. It
is
taking forever. I'm trying to use VBA to do simple things.

with Excel, if I need to get a value from a userform, it is

frmEmployee.txtFirstname.text (intellisense kicks in when I press the
">.")

with Access, I am seeing examples of Forms!FrmEmployee!txtFirstname.value
with no intellisense.

Am I missing something or is this the proper way to reference a control's
value on another form? Why can't you use intellisense to see the controls
on
another form?

As you probably know, Access Forms are very different from Excel/Word
UserForms. I'm not too familiar with the use of userforms in Excel, but I
suspect that you only have one instance of any given form, and the forms
always exist and are always in scope. In Access, you can have multiple
instances of any given form, and the form doesn't even exist as an object
until an instance has been created.

When the form instance has been created and is in scope, intellisense
operates. However, the bang operator doesn't provoke intellisense; only
the dot operator. If I am programming an event procedure in a form's class
module, for example, I can write

Me.

.... and get full intellisense, including not only the form's properties and
methods, but also all the controls on the form and the fields in the form's
recordsource.

If I have an object reference to a Form object, it gives me intellisense for
the properties of an Access Form; so I can write:

Dim frm As Form

frm.

.... and intellisense will appear after I type the dot (.). Or I can write
this:

Forms("frmEmployee").

.... and again, I'll get intellisense for an Access form -- but not the
specific controls and fields of frmEmployee. This is one case where using
the bang, while more efficient (they tell us), is disadvantageous, because
typing:

Forms!frmEmployee.

.... will not give intellisense.

Note: If your form has a class module, then you can declare an object
reference to that specific class, and get full intellisense. For example,

Dim frm As Form_frmEmployee

Set frm = Forms!frmEmployee

frm.

.... will give you intellisense including fields, controls, and public
methods specific to that form. Be aware, though, that forms only have class
modules if they include VBA code.

 

[David H]

Out of curiosity, why do you need to get the value from one form and use it
in the code for another?

 

[Webtechie]

Thank you very, very, very much.

I am forcing myself to keep programming in Access and not revert back to
Excel. I have a book that teaches using Excel as the presentation layer and
using Access as a backend. But for what I am doing, I think that would be
overkill.

I just needed a good explanation to point me in the right direction. Your
explanation points me in the right direction.

Again, Thank you!

Tony

 

[Doctor]

Thanks for the tutorial. Do you use .text to test some criteria before it
becomes .value? Or do .text and .value equal the same thing as long as the
control has focus?

 

[Jack Leach]

..Text is not necessarily the same value as .Value. In fact, I would never
count on it myself. Say a control has a Value of "thisval"... then the user
tabs to that control and types in "thatvalue". Before moving away from the
control, its Value is still "thisval" (because the update hasn't been run
yet), but the Text is "thatvalue".

When the use moves away from the control and the control is updated, the
Value is then "thatvalue".

The .Text value doesn't have nearly as much place in standard code as the
..Value value. I use it for reading an unbound search box in the OnChange
event, to read the text of the control and update a search SQL, etc. But for
most stuff, .Value is what you want to be using. A more standard use of the
value of the .Text property is to run a validation on the BeforeUpdate of a
control.



Also keep in mind that Me.ControlName actually refers to the control itself,
as an object. Access defaults to the .Value property of the control only
when it can't be applied as an object.

Therefore, Me.ControlName and Me.ControlName.Value are a slight bit
different, though most of the time there's no issues with useing plain old
Me.ControlName, but if you want to see the difference Dim a variable as a
variant and try to apply the value of the control using Me.ControlName....
you might not get what you expect.


Ex.
Dim var As Variant
var = Me.ControlName

might produce a different result than

var = Me.ControlName.Value



--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[David H]

What exactly features in Excel are you using to present the data?

 

[Webtechie]

I use userforms a lot.

I've written several major applications using userforms with complex
controls. Using Excel is so easy. For example, to show a Excel userform,
you use

myForm.show

I had to search a book I bought to begin developing in Acces to find

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFindFiles"

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Dirk Goldgar]

I use userforms a lot.

I've written several major applications using userforms with complex
controls. Using Excel is so easy. For example, to show a Excel userform,
you use

myForm.show

I had to search a book I bought to begin developing in Acces to find

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFindFiles"

DoCmd.OpenForm stDocName, , , stLinkCriteria

That's the code the wizards build, because the wizards include boilerplate
code so that they can easily and programmatically manipulate it. But you
don't actually need all that junk. This is enough, and all I would use just
to open a form:

DoCmd.OpenForm "frmFindFiles"

The help file would have told you that, without needing to search in a book.

I think you're being unfair in your comparisons between programming Access
and programming Excel. You spent some time learning Excel's object model,
and now that seems very natural to you. Access has a different object
model, and you haven't learned it yet. Your complaint about intellisense
was just, in a limited way, because there are circumstances in which it
would be reasonable to expect intellisense and you can't get it -- though
those circumstances are limited and haven't affected me as a developer in
any noticeable way. But Access is not Excel, and expecting to program it in
exactly the same way is unreasonable. If you're going to work with Access,
spend a little time learning how to do things the Access way before you
complain about it.

 

[David H]

The value in the .value property is not updated to the value of the .text
property until after the control is updated. So if you change the type
'David' and then check the two properties, you'll see that .text is 'David'
and .value is 'John' or whatever the original value was. The control is then
updated using Shift+Enter, tabbing out of it, clicking away from it, or by
any other means out there, then the both will be the same.

Personally, I've only ever used .Value and .OldValue properties.

http://msdn.microsoft.com/en-us/library/aa172955(office.11).aspx

 

[David H]

You'll find that although Excel may seem easier, that it isn't neccessarily
more powerful. The decision to develop something in Excel or Access *must* be
based on a full understanding of the capabilities of each as well as any
limitations inherent in either.

 

[David W. Fenton]

I don't think I can use me.txtEmployee.value to get the value of a
control on anothe form.

You could use any of these:

Form!MyOtherForm!txtEmployee
Form!MyOtherForm.txtEmployee
Form!MyOtherForm!txtEmployee.Value
Form!MyOtherForm.txtEmployee.Value

They are all equivalent. But the point is, to get the value of the
other form, you need to specify that form, as the Me keyword refers
to the object attached to the code module you are typing the code
into.

 

[David W. Fenton]

I have a book that teaches using Excel as the presentation layer
and using Access as a backend. But for what I am doing, I think
that would be overkill.

Sounds like a silly book. It's much easier to build a UI for a
database using a development environment that is designed for
building the UI for a database. That would be Access. Excel is
really wholly unsuited to that purpose, though it's extremely useful
as a tool for analyzing and graphing data stored in your database.

 

[David W. Fenton]

Using Excel is so easy.

Because you already know how to use it.

I would not find it easy because its forms lack so much
functionality that I've come to depend on in Access.

 

[David W. Fenton]

But Access is not Excel, and expecting to program it in
exactly the same way is unreasonable.

I think this is one of the downsides of introducing the VBE in
Access, instead of having an Access-specific IDE. To me, it makes
people coming from Excel and Word think "this is familiar, so it
will work the same" when it doesn't work the same at all.

MS has created a situation where appearances deceive the user into
developing incorrect expectations.

 

[Douglas J. Steele]

You could use any of these:

Form!MyOtherForm!txtEmployee
Form!MyOtherForm.txtEmployee
Form!MyOtherForm!txtEmployee.Value
Form!MyOtherForm.txtEmployee.Value

<picky>
You inadvertently left off the s in Forms:

Forms!MyOtherForm!txtEmployee
Forms!MyOtherForm.txtEmployee
Forms!MyOtherForm!txtEmployee.Value
Forms!MyOtherForm.txtEmployee.Value

They are all equivalent. But the point is, to get the value of the
other form, you need to specify that form, as the Me keyword refers
to the object attached to the code module you are typing the code
into.

You could also use any of

Forms("MyOtherForm")!txtEmployee
Forms("MyOtherForm").txtEmployee
Forms("MyOtherForm")!txtEmployee.Value
Forms("MyOtherForm").txtEmployee.Value
Forms("MyOtherForm").Controls("txtEmployee")
Forms("MyOtherForm").Controls("txtEmployee").Value