Access super buggy?

[Adrian Parker]

Version: Access 2000

Hello. I have a table with three fields. An autonumbered field which is a
primary key. A Text field called "schoolName", and another text field
called "contactName"

In the table, I set the Display Control of the schoolName to Combo Box (I've
also used Text Box). I make a new form. On this form there is a text box
to show the primary key, a text box to show the contact name, and a combo
box to show the schoolName. The control source for schoolName is schoolName
(which is correct)

When I view the form in form view, I find the Combo Box is filled with the
primary keys! Not the value I wanted.

If I add a Text Box to the same form, and also set it's control source to
schoolName, the text box now shows the primary key, and the combo box shows
the school's name (As it should). When I use the form in this state, and
scroll through records, the schoolName field value for every record is
changed so that it's value becomes that of it's own primary key!

Also, I put a bound text box on the form, pointing to schooName, and then an
unbound combo box. In the combo box's onchange method I put:

txtSchoolName.text = cmbSchoolName.Text

(note: both object names are correct) This returns an error saying you
can't change the property of a control which does not have focus!


What is going on? I know VB quite well, and SQL very well. So generally I
know what I'm doing. But Access seems extremely borked.



Adrian

 

[Joe Fallon]

Access is not super buggy.
It has its regular share.

You are just unfamiliar with it. So I am not sure why you blame the product.

This shows you came from VB: (Access developers rarely use the Text
property.)
txtSchoolName.text = cmbSchoolName.Text

In Access you would write: (check help to see what the default property is
for each control.)
Me!txtSchoolName = Me!cmbSchoolName

 

[StCyrM]

Hi

Access is not super buggy, although it does have it's share of undocumented
features.

Perhaps you would be happier with the other wanna be's in the desktop database
world, namely Filemaker Pro or even Lotus Approach.

Maurice

 

[Adrian Parker]

Access is not super buggy.
It has its regular share.

You are just unfamiliar with it. So I am not sure why you blame the

product.

Ummm, the fact that when you bind a control box to a Text based field, it
instead shows the primary key.

Or when you bind a combo box and a text box to the same field, the values
shown in each are not the same as one another. Etc.


Adrian

 

[Adrian Parker]

Hi

Access is not super buggy, although it does have it's share of undocumented
features.

Perhaps you would be happier with the other wanna be's in the desktop database
world, namely Filemaker Pro or even Lotus Approach.

I've used MySQL for my normal PHP and C++ projects. I haven't that liberty
for this company though.



Adrian

 

[Rick Brandt]

product.

Ummm, the fact that when you bind a control box to a Text based field, it
instead shows the primary key.

You used a Lookup field when you created the table and that's exactly what
happens. it "displays" one thing but actually stores another. When you go
to use that field in other locations you see what is actually stored. Get
rid of the Lookup field and everything will work as expected.

 

[Adrian Parker]

You used a Lookup field when you created the table and that's exactly what
happens. it "displays" one thing but actually stores another. When you go
to use that field in other locations you see what is actually stored. Get
rid of the Lookup field and everything will work as expected.

Even when you explicitly ask for the property (Text) which holds the display
value...



Adrian

 

[Albert D. Kallal]

Even when you explicitly ask for the property (Text) which holds the display
value...

You should realize that the "text" property is complete different in
ms-access as it is compared to VB. In ms-access, we have both the .text
property and the .value property.

The text property IS ONLY VALID when the control has the focus. You can't
use it else where.

So, if you put a button on a form that would display the last name, the
following will NOT work


msgbox "the last name is = " & me!lastname.text

However the default property is the .value. The following will work:

msgbox "the last name is = " & me!lastname.Value

Since as mentioned, the default property is .value, then it is standard fair
to use:


msgbox "the last name is = " & me!lastname

So, the .text property is ONLY of use when the control has the focus. What
this means, is it is generally ONLY OF USE when doing some kind of keypress
processing. (that would mean the on-change event of the control). The
underlying data field will NOT have been yet updated, and thus you need to
use the .text (but, as mentioned, this is only valid when the control has
the focus. If you try and use the .text property otherwise, you will get a
error message about the control not having focus).)

However, even the before update,and after update events, you can, and should
just use the .value property of the control. So, as a general rule, you will
not need to use the .text property of a control. Simply dump your use of
that property. You confusing here is over the issue of the text box having
focus, or not. For sure, whatever the text box displays WHILE EDITING is
going to be shown by the .text propriety, but as mentioned, you RARELY will
need to use the .text property. Further, it is not much use anyway, since
the .text property is ONLY VALUE when the control has the FOCUS!

You have to remember the ms-access has about 2 or 3 times the number of
events for a form. This large increase in complexity means a larger learning
curve then what VB has. However, once you learn the event and object model
for a form, you can develop software at least 3 times faster then you can in
VB, or just about any other platform. That means a $8,000 project in
ms-access will cost you $25,000 in most other platforms (like VB for
example. We of course are assuming a data centric project here!).

You also have to realize that the combo box from VB is VERY LAME compared to
the combo box we have in ms-access. The combo box in ms-access is capable of
displaying multi-columns. Further, you can choose which of the columns the
control is bound to. You also have the limit to list *and* the not in list
event (this not in list event is kind of cool, as again, it saves tons of
code, and is fired when the user types in a value that is NOT in the list.
(so, we can use that event to *easily* allow the user to add more entries to
a combo box. Golly, I wish VB had even half of the things the ms-access has
in regards to events on a form).


There was also a comment, or reference in this thread to using the lookup
feature (and to NOT use it!). Note that comment only was referring to using
the lookup feature AT THE TABLE design level. You most certainly can, and
will use the combo box to lookup values...but avoid that dumb table lookup
feature if possible.


You can read the top 10 bad list for ms-access at:

http://www.mvps.org/access/tencommandments.htm

(that #2 in the above list is referring to those lookup field feature).


By the way, that site is an absolute gold min of code examples. Bookmark it.

Also, you will find that using the wizard to create a combo boxes is a real
nice time saver (don't be afraid to use he wizard)

Note, that to display other columns from that combo box on your form, use
the following as the data source for UN-BOUND text boxes on that form.

=([combo33].[Column](1))

the above would display the 2nd column (the column value is zero based).

So, you can place several un-bound text boxes on the screen, and thus can
display those additional fields.

=([combo33].[Column](2))

You can also setup the form to allow editing of those fields on the form if
you wish. You can read the following article on this if you wish:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html


And, if you are looking to display data in a grid, you don't event need a
grid control.

Check out the following screen shots of ms-access in continues form mode:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

 

[Adrian Parker]

You should realize that the "text" property is complete different in
ms-access as it is compared to VB. In ms-access, we have both the .text
property and the .value property.

VB has the .TEXT and .INDEX, which is essentially the same thing.

What I find most annoying, and poorly designed, is that they're not the
same. It does not seem logical to me that they would change method and
member names.

The text property IS ONLY VALID when the control has the focus. You can't
use it else where.

Oh, that's miserable.

You also have to realize that the combo box from VB is VERY LAME compared to
the combo box we have in ms-access. The combo box in ms-access is capable of
displaying multi-columns. Further, you can choose which of the columns the
control is bound to. You also have the limit to list *and* the not in list
event (this not in list event is kind of cool, as again, it saves tons of
code, and is fired when the user types in a value that is NOT in the list.
(so, we can use that event to *easily* allow the user to add more entries to
a combo box. Golly, I wish VB had even half of the things the ms-access has
in regards to events on a form).

Note, that to display other columns from that combo box on your form, use
the following as the data source for UN-BOUND text boxes on that form.

=([combo33].[Column](1))

the above would display the 2nd column (the column value is zero based).

So, you can place several un-bound text boxes on the screen, and thus can
display those additional fields.

=([combo33].[Column](2))

I wanted both contnrols (textbox and comboBox) to be able to change the
value in the table. The combo box would hold all the current values, the
text box would enable you to add new entries. I know you can add new
entries using the combo box itself, but I remember seeing some odd behaviour
when doing this. I can't recall what it was off hand.

In the end I got it working.

You can also setup the form to allow editing of those fields on the form if
you wish. You can read the following article on this if you wish:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html

I'll read this, thank you.



Adrian

 

[TC]

Surprise! Access controls are not the same as VB controls. They do not have
the same properties (always). Even when they do have properties of the same
name, those properties do not necessarily behave the same.

I have two cars. One of them runs on petrol. The other is an electric car.
What I find most annoying, and poorly designed, is that they are not the
same. It does not seem logical to me that they would make me put petrol in
one, and not in the other. They are both "cars". Why are they different?

TC

You should realize that the "text" property is complete different in
ms-access as it is compared to VB. In ms-access, we have both the .text
property and the .value property.

VB has the .TEXT and .INDEX, which is essentially the same thing.

What I find most annoying, and poorly designed, is that they're not the
same. It does not seem logical to me that they would change method and
member names.

The text property IS ONLY VALID when the control has the focus. You can't
use it else where.

Oh, that's miserable.

You also have to realize that the combo box from VB is VERY LAME

compared

to
the combo box we have in ms-access. The combo box in ms-access is

capable

of
displaying multi-columns. Further, you can choose which of the columns the
control is bound to. You also have the limit to list *and* the not in list
event (this not in list event is kind of cool, as again, it saves tons of
code, and is fired when the user types in a value that is NOT in the list.
(so, we can use that event to *easily* allow the user to add more

entries

to
a combo box. Golly, I wish VB had even half of the things the ms-access has
in regards to events on a form).

Note, that to display other columns from that combo box on your form, use
the following as the data source for UN-BOUND text boxes on that form.

=([combo33].[Column](1))

the above would display the 2nd column (the column value is zero based).

So, you can place several un-bound text boxes on the screen, and thus can
display those additional fields.

=([combo33].[Column](2))

I wanted both contnrols (textbox and comboBox) to be able to change the
value in the table. The combo box would hold all the current values, the
text box would enable you to add new entries. I know you can add new
entries using the combo box itself, but I remember seeing some odd behaviour
when doing this. I can't recall what it was off hand.

In the end I got it working.

You can also setup the form to allow editing of those fields on the form if
you wish. You can read the following article on this if you wish:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html

I'll read this, thank you.



Adrian

 

[Adrian Parker]

Surprise! Access controls are not the same as VB controls. They do not have
the same properties (always). Even when they do have properties of the same
name, those properties do not necessarily behave the same.

This is my point, they should.

I have two cars. One of them runs on petrol. The other is an electric car.
What I find most annoying, and poorly designed, is that they are not the
same. It does not seem logical to me that they would make me put petrol in
one, and not in the other. They are both "cars". Why are they different?

Your analogy is flawed. Imagine that you have the same two cars, except one
steers with a steering wheel, and the other used tank-like levers.



Adrian

TC

Even when you explicitly ask for the property (Text) which holds the
display
value...


You should realize that the "text" property is complete different in
ms-access as it is compared to VB. In ms-access, we have both the ..text
property and the .value property.

VB has the .TEXT and .INDEX, which is essentially the same thing.

What I find most annoying, and poorly designed, is that they're not the
same. It does not seem logical to me that they would change method and
member names.

The text property IS ONLY VALID when the control has the focus. You can't
use it else where.

Oh, that's miserable.

You also have to realize that the combo box from VB is VERY LAME

compared

to
the combo box we have in ms-access. The combo box in ms-access is

capable

of
displaying multi-columns. Further, you can choose which of the columns the
control is bound to. You also have the limit to list *and* the not in list
event (this not in list event is kind of cool, as again, it saves tons of
code, and is fired when the user types in a value that is NOT in the list.
(so, we can use that event to *easily* allow the user to add more

entries

to
a combo box. Golly, I wish VB had even half of the things the

ms-access
has

in regards to events on a form).

Note, that to display other columns from that combo box on your form, use
the following as the data source for UN-BOUND text boxes on that form.

=([combo33].[Column](1))

the above would display the 2nd column (the column value is zero based).

So, you can place several un-bound text boxes on the screen, and thus can
display those additional fields.

=([combo33].[Column](2))

I wanted both contnrols (textbox and comboBox) to be able to change the
value in the table. The combo box would hold all the current values, the
text box would enable you to add new entries. I know you can add new
entries using the combo box itself, but I remember seeing some odd behaviour
when doing this. I can't recall what it was off hand.

In the end I got it working.

You can also setup the form to allow editing of those fields on the

form
if

you wish. You can read the following article on this if you wish:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html

I'll read this, thank you.



Adrian

 

[TC]

This is my point, they should.

I certainly agree that it would be nice if they did. But I can't see any
logical justification for assuming that they should - or even could if they
wanted to!

For example, there must be hundreds of 3rd part controls that have a Text
property. How can it possibly make any sense, to assume that all of those
should work the same? For example, some of them will support rich text -
some of them won't. Should the plain text controls should have a FontBold
property? Should the rich text ones >omit< that property, just for the sake
of consistency? There is just no way that the Text property of those two
controls, could possibly be identical - even if they came from the same
vendor. When you think about it in this wider context, it just doesn't make
sense to expect what you are asking for, IMO.

TC

Your analogy is flawed. Imagine that you have the same two cars, except one
steers with a steering wheel, and the other used tank-like levers.

I say that proves my point completely! There >are< cars that are steered
with levers: those designed for disabled people. Are you surprised that a
car designed for disabled people, have different (unfamiliar) steering
controls to those designed for normally-abled people? They are both cars -
they both have steering - but the steering mechanisms are >not< the same;
and a person who can drive one of those cars, should not assume that the
other one will work identically!

Cheers,
TC

Adrian

TC

Even when you explicitly ask for the property (Text) which holds the
display
value...


You should realize that the "text" property is complete different in
ms-access as it is compared to VB. In ms-access, we have both the .text
property and the .value property.

VB has the .TEXT and .INDEX, which is essentially the same thing.

What I find most annoying, and poorly designed, is that they're not the
same. It does not seem logical to me that they would change method and
member names.


The text property IS ONLY VALID when the control has the focus. You can't
use it else where.

Oh, that's miserable.


You also have to realize that the combo box from VB is VERY LAME compared
to
the combo box we have in ms-access. The combo box in ms-access is capable
of
displaying multi-columns. Further, you can choose which of the

columns
the

control is bound to. You also have the limit to list *and* the not

in
list

event (this not in list event is kind of cool, as again, it saves

tons
of

code, and is fired when the user types in a value that is NOT in the list.
(so, we can use that event to *easily* allow the user to add more entries
to
a combo box. Golly, I wish VB had even half of the things the ms-access
has
in regards to events on a form).


Note, that to display other columns from that combo box on your

form,
use

the following as the data source for UN-BOUND text boxes on that form.

=([combo33].[Column](1))

the above would display the 2nd column (the column value is zero based).

So, you can place several un-bound text boxes on the screen, and

thus
can

display those additional fields.

=([combo33].[Column](2))

I wanted both contnrols (textbox and comboBox) to be able to change the
value in the table. The combo box would hold all the current values, the
text box would enable you to add new entries. I know you can add new
entries using the combo box itself, but I remember seeing some odd behaviour
when doing this. I can't recall what it was off hand.

In the end I got it working.


You can also setup the form to allow editing of those fields on the form
if
you wish. You can read the following article on this if you wish:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html

I'll read this, thank you.



Adrian

 

[Adrian Parker]

I certainly agree that it would be nice if they did. But I can't see any
logical justification for assuming that they should - or even could if they
wanted to!

For example, there must be hundreds of 3rd part controls that have a Text
property. How can it possibly make any sense, to assume that all of those
should work the same?

You're talking about one company making make two products, which are more or
less based on the same syntax and language.

For example, some of them will support rich text -
some of them won't. Should the plain text controls should have a FontBold
property? Should the rich text ones >omit< that property, just for the sake
of consistency? There is just no way that the Text property of those two
controls, could possibly be identical - even if they came from the same
vendor.

I think they call it inheritance.

However, does it make sense for two objects which contain the same data, at
least in part, to have the same properties which actually hold the same
thing. That just makes sense.



Adrian

When you think about it in this wider context, it just doesn't make
sense to expect what you are asking for, IMO.

TC

petrol
in different?

Your analogy is flawed. Imagine that you have the same two cars, except one
steers with a steering wheel, and the other used tank-like levers.

I say that proves my point completely! There >are< cars that are steered
with levers: those designed for disabled people. Are you surprised that a
car designed for disabled people, have different (unfamiliar) steering
controls to those designed for normally-abled people? They are both cars -
they both have steering - but the steering mechanisms are >not< the same;
and a person who can drive one of those cars, should not assume that the
other one will work identically!

Cheers,
TC

Adrian

TC




Even when you explicitly ask for the property (Text) which holds the
display
value...


You should realize that the "text" property is complete different in
ms-access as it is compared to VB. In ms-access, we have both the .text
property and the .value property.

VB has the .TEXT and .INDEX, which is essentially the same thing.

What I find most annoying, and poorly designed, is that they're not the
same. It does not seem logical to me that they would change method and
member names.


The text property IS ONLY VALID when the control has the focus. You
can't
use it else where.

Oh, that's miserable.


You also have to realize that the combo box from VB is VERY LAME
compared
to
the combo box we have in ms-access. The combo box in ms-access is
capable
of
displaying multi-columns. Further, you can choose which of the columns
the
control is bound to. You also have the limit to list *and* the not in
list
event (this not in list event is kind of cool, as again, it saves tons
of
code, and is fired when the user types in a value that is NOT in the
list.
(so, we can use that event to *easily* allow the user to add more
entries
to
a combo box. Golly, I wish VB had even half of the things the ms-access
has
in regards to events on a form).


Note, that to display other columns from that combo box on your form,
use
the following as the data source for UN-BOUND text boxes on that form.

=([combo33].[Column](1))

the above would display the 2nd column (the column value is zero based).

So, you can place several un-bound text boxes on the screen, and thus
can
display those additional fields.

=([combo33].[Column](2))

I wanted both contnrols (textbox and comboBox) to be able to change the
value in the table. The combo box would hold all the current

values,
the

text box would enable you to add new entries. I know you can add new
entries using the combo box itself, but I remember seeing some odd
behaviour
when doing this. I can't recall what it was off hand.

In the end I got it working.


You can also setup the form to allow editing of those fields on

the
form

if
you wish. You can read the following article on this if you wish:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html

I'll read this, thank you.



Adrian

 

[Rick Brandt]

You're talking about one company making make two products, which are more or
less based on the same syntax and language.

But most MS products were acquisitions of products from other companies at
one point and as such they started out with greatly different object
models. While each subsequent Office release might bring them more in
harmony with each other they might not want to do anything that would break
backward compatibility or cause too steep a "re-learning" curve for those
developers who are already familiar with each product.

 

[TC]

(snip)

However, does it make sense for two objects which contain the same data, at
least in part, to have the same properties which actually hold the same
thing. That just makes sense.

It >doesn't< make sense. They are not the same object! A textbox in VB is a
Visual Basic object. A textbox in Access is an Access object. The fact that
they both contain "text", is completely irrelevant. Visual Basic is not
Access! The two textboxes will inevitably have different properties, methods
& events. It is simply not realistic to assume that both textboxes will work
the same way.

I think we must agree to differ on this issue!

TC