SQL question (hard one, I think)

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

In PC FAND (long time ago) we had table declarations as follows:
File (table) declaration:
1) stored fields
2) key declaration section #K
3) calculated fields section #C

Key declaration and calculated fields sections could be declared more than
once.
-------------------------------------------------
lookup table LookSU (superior table):
SU: A,3; (dbText, 3)
SUname: A,30; (dbText, 30)
Vrozp: B; (dbBoolean)

#K @ SU; (unique primary key)
-------------------------------------------------
data table Data (subordinate table):
dDate: D; (dbDate)
SU: A,3; (dbText, 3)

#K @ *Date; (non-unique primary key)
SU (@) *SU; (secondary non-unique key)
DateSU (@) *dDate;SU; (secondary non-unique key)
LookSU ! SU; (foreign key with referential integrity)
(or LookSU SU - without referential integrity)

#C SUname:=LookSU.SUname: A,30;
Vrozp:=LookSU.Vrozp: B
-------------------------------------------------
Edit form could contain both stored and calculated fields, eg.:
EditData
#_Data (based on table Data)
dDate, SU, SUname, VRozp;
__.__.____ ___ ______________________________ _ (edit masks)

-------------------------------------------------
In forms it was possible to edit ONLY table's stored field, ie. it was
impossible to change data in foreign tables (except foreign key fields in
subordinate tables with referential integrity - it was done automatically
like in MS Access).
All "foreign" values (calculated fields based on superior tables) were
available immediatelly after user's input of foreign-key-field value. If
user deleted a record in data table no other records in superior tables were
affected, ie. those records could not be deleted from an edit form as shown
above.
New record was edited sequentially (field by field, from "left to right"),
ie. it was impossible to skip an unedited field.
===========================
In MS Access I have:

lookup table LookSU (superior table):
SU dbText, 3
SUname dbText, 30
Vrozp dbBoolean

unique primary key SU
-------------------------------------------------
data table Data (subordinate table):
dDate dbDate
SU dbText, 3

non-unique key dDate (dDate)
non-unique key dDateSU (dDate, SU)
non-unique key SU
-------------------------------------------------
query qryData (RecordsetType = 1):
SELECT Data.*, LookSU.SUname, LookSU.Vrozp
FROM Data INNER JOIN LookSU ON Data.SU = LookSU.SU;
-------------------------------------------------
a form based on qryData with these fields:
dDate TextBox
SU ComboBox
SUname TextBox
VRozp CheckBox (invisible)

-------------------------------------------------
What I need in a form:
1) LookSU.Vrozp value immediatelly after Data.SU input. I need this value
for logical controls (validations) in SU_BeforeUpdate event procedure.
2) Leave LookSU table untouched when I delete a record in Data (bear in mind
possible multiple record selection).
3) Display SUname without using a combo-box based on Data.SU.
4) Display SUname immediatelly after SU input.

Is anything possible in MS Access without DLookup in calculated fields?

What I'd like in a form:
5) Sequencial input (from "left to right") similar to PC FAND... ;-)

Thank you very much in advance.

Vlado

P.S. This example shows only a small part of what I need to do. In my
project (my particular form) there are six "calculated" fields based on
foreign keys. I would like to avoid DLookups to speed the form up.
P.P.S. Excuse me for my poor English. I hope you've got what I've tried to
explain.

 

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

P.P.P.S: Data source in my form is more complicated and (probably) it's the
reason why I can't make it work as expected:
SELECT [Denik].[RokUd] & [Denik].[MesUD] & " " & [Denik].[DokUD] AS
CisDokl, Denik.*, [qryCis3Uc08-1].Typ AS Typ08, Cis3Uc14.TYP AS Typ14,
qryCis3Uc04.Vrozp, (Left([Denik].[Pol],1)>="1") And
(Left([Denik].[Pol],1)<="4") AS prijem, (Left([Denik].[Pol],1)>="5") And
(Left([Denik].[Pol],1)<="7") AS vydej, Left([Denik].[Pol],1)="8" AS finan
FROM ((Denik LEFT JOIN qryCis3Uc14 ON (Denik.AU = qryCis3Uc14.AU) AND
(Denik.SU = qryCis3Uc14.SU)) INNER JOIN qryCis3Uc04 ON Denik.SU =
qryCis3Uc04.SU) INNER JOIN [qryCis3Uc08-1] ON Denik.POL =
[qryCis3Uc08-1].Pol
ORDER BY Denik.ID_Denik;

 

[Michael Gramelspacher]

P.P.P.S: Data source in my form is more complicated and (probably) it's the
reason why I can't make it work as expected:
SELECT [Denik].[RokUd] & [Denik].[MesUD] & " " & [Denik].[DokUD] AS
CisDokl, Denik.*, [qryCis3Uc08-1].Typ AS Typ08, Cis3Uc14.TYP AS Typ14,
qryCis3Uc04.Vrozp, (Left([Denik].[Pol],1)>="1") And
(Left([Denik].[Pol],1)<="4") AS prijem, (Left([Denik].[Pol],1)>="5") And
(Left([Denik].[Pol],1)<="7") AS vydej, Left([Denik].[Pol],1)="8" AS finan
FROM ((Denik LEFT JOIN qryCis3Uc14 ON (Denik.AU = qryCis3Uc14.AU) AND
(Denik.SU = qryCis3Uc14.SU)) INNER JOIN qryCis3Uc04 ON Denik.SU =
qryCis3Uc04.SU) INNER JOIN [qryCis3Uc08-1] ON Denik.POL =
[qryCis3Uc08-1].Pol
ORDER BY Denik.ID_Denik;

You have two boolean expressions with AND as columns. These require
parenthesis around the entire expression.

((LEFT([denik].[pol],1) >= "1")
AND (LEFT([denik].[pol],1) <= "4")) AS prijem,
((LEFT([denik].[pol],1) >= "5")
AND (LEFT([denik].[pol],1) <= "7")) AS vydej,

Really, I do not know for sure that this is the problem.

 

[Albert D. Kallal]

What I need in a form:

1) LookSU.Vrozp value immediatelly after Data.SU input. I need this value
for logical controls (validations) in SU_BeforeUpdate event procedure.

Hum, if I gather what you are doing, simply make the combo box SU have all 3
columns from he table LOOKSU.

Thus, the combo box will have 3 columns

SU SUName Vrozip

When you select he su value, the SUName will display (if you set the first
column length to 0 in the combo box).

You can now display Vrozip. Simply add a new text box, and set the data
source as:

=([combo37].[column](2))

Note that the column function is zero based, so, 2 = display 3rd column

So, that will allow you to display the value. It much easier (and faster)
then using a dlookup. However, you could use a dlookup expression also.

2) Leave LookSU table untouched when I delete a record in Data (bear in
mind possible multiple record selection).

That is naturally, and normally how ms-access works. It is possible you
setup you relational wrong.

3) Display SUname without using a combo-box based on Data.SU.

You have quite a few choices and approaches. You can simply use dlookup, or
consider basing the form as "data" for the main table, and LEFT join in the
child table. simply set the text box enabled property = no to prevent
accident updating of the SUname. The other approach is to use dlookup.

eg:

=(dlookup("SUName","LookSU","SU = '" & me!SU & "'")

Since su is a text field, you have to surround the value with quotes....

4) Display SUname immediatelly after SU input.

All of the 3 above suggestions (combo box, relational join, dlookup) will
and should display immediate right after you enter the su value.

Is anything possible in MS Access without DLookup in calculated fields?

???? I don't understand the above. We building a form here..right? (don't
use direct table data entry).

What I'd like in a form:
5) Sequencial input (from "left to right") similar to PC FAND... ;-)

Again, you can set the tab order anyway you want. While in design
mode..view-tab order. You can also for the forms property set the cycle to
current record if you want the cursor to re-start at the top of form.

If you need to have fields required, then simply set that in table design
mode, the user will not be able to move on, close, or save until all fields
are entered...

 

[=?iso-8859-15?Q?Vladim=EDr_Cvajniga?=]

Thx for your response, Michael. Albert D. Kallal's post helps a lot, see
below.

Vlado

P.P.P.S: Data source in my form is more complicated and (probably) it's
the
reason why I can't make it work as expected:
SELECT [Denik].[RokUd] & [Denik].[MesUD] & " " & [Denik].[DokUD] AS
CisDokl, Denik.*, [qryCis3Uc08-1].Typ AS Typ08, Cis3Uc14.TYP AS Typ14,
qryCis3Uc04.Vrozp, (Left([Denik].[Pol],1)>="1") And
(Left([Denik].[Pol],1)<="4") AS prijem, (Left([Denik].[Pol],1)>="5") And
(Left([Denik].[Pol],1)<="7") AS vydej, Left([Denik].[Pol],1)="8" AS finan
FROM ((Denik LEFT JOIN qryCis3Uc14 ON (Denik.AU = qryCis3Uc14.AU) AND
(Denik.SU = qryCis3Uc14.SU)) INNER JOIN qryCis3Uc04 ON Denik.SU =
qryCis3Uc04.SU) INNER JOIN [qryCis3Uc08-1] ON Denik.POL =
[qryCis3Uc08-1].Pol
ORDER BY Denik.ID_Denik;

You have two boolean expressions with AND as columns. These require
parenthesis around the entire expression.

((LEFT([denik].[pol],1) >= "1")
AND (LEFT([denik].[pol],1) <= "4")) AS prijem,
((LEFT([denik].[pol],1) >= "5")
AND (LEFT([denik].[pol],1) <= "7")) AS vydej,

Really, I do not know for sure that this is the problem.

 

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

Thanks very much for your respond. It helps a lot. See my notes below.

Vlado

What I need in a form:
1) LookSU.Vrozp value immediatelly after Data.SU input. I need this value
for logical controls (validations) in SU_BeforeUpdate event procedure.

Hum, if I gather what you are doing, simply make the combo box SU have all
3 columns from he table LOOKSU.

Thus, the combo box will have 3 columns

SU SUName Vrozip

When you select he su value, the SUName will display (if you set the first
column length to 0 in the combo box).

You can now display Vrozip. Simply add a new text box, and set the data
source as:

=([combo37].[column](2))

Note that the column function is zero based, so, 2 = display 3rd column

So, that will allow you to display the value. It much easier (and faster)
then using a dlookup. However, you could use a dlookup expression also.

Great idea!!! I just didn't know how to use combo-box's columns in
calculated field.
=====================================================

That is naturally, and normally how ms-access works. It is possible you
setup you relational wrong.

There are no relations between the tables yet. This may be "the problem".
=====================================================

You have quite a few choices and approaches. You can simply use dlookup,
or consider basing the form as "data" for the main table, and LEFT join in
the child table. simply set the text box enabled property = no to prevent
accident updating of the SUname. The other approach is to use dlookup.

eg:

=(dlookup("SUName","LookSU","SU = '" & me!SU & "'")

Since su is a text field, you have to surround the value with quotes....

I'll try to avoid DLookups and will use combo-box stuff instead. DLookup
would significantly slow down app's performance.
=====================================================

All of the 3 above suggestions (combo box, relational join, dlookup) will
and should display immediate right after you enter the su value.

???? I don't understand the above. We building a form here..right? (don't
use direct table data entry).

"Is anything possible in MS Access without DLookup in calculated fields?"
should sound
"Is anything of the above possible in MS Access without DLookup in
calculated fields?"

Yes, we're building a form. I think I will do most of the stuff just by
adding new fields to the combo-box.
=====================================================

Again, you can set the tab order anyway you want. While in design
mode..view-tab order. You can also for the forms property set the cycle
to current record if you want the cursor to re-start at the top of form.

If you need to have fields required, then simply set that in table design
mode, the user will not be able to move on, close, or save until all
fields are entered...

In MS Access user can enter any field just by a mouse click or Tab key. In
PC FAND user has to fill the record in given order (he/she must press Enter
on each field to confirm input; boolean fields don't need Enter) - it is
possible to move back to any of the previous fields but skipping "over"
unfilled fields is not allowed.
Im affraid this can't be done in MS Access... :'-(
=====================================================

 

[Albert D. Kallal]

eg:

I'll try to avoid DLookups and will use combo-box stuff instead. DLookup
would significantly slow down app's performance.

dlookups when used correctly, do not impact performance in a significant
way. it is when people use them in a query in place of a relational join
where they are horrible.

On a standard form, using a few dlookups, and ensuring the values used for
the lookup are indexed, then you find little, if any performance problem.

In fact, with really small tables (say only 150,000 records), you not
noticed any slowdown at all. dlookup should be used sparingly because often
there is better approaches available (such as our .collumn() example).
However, using a few dlooups on a form is not going to effect load times, or
performance in a noticeable way. It usually poor data designs, and poor
approaches to solutions that cases the performance issues.

In MS Access user can enter any field just by a mouse click or Tab key. In
PC FAND user has to fill the record in given order (he/she must press
Enter
on each field to confirm input; boolean fields don't need Enter) - it is
possible to move back to any of the previous fields but skipping "over"
unfilled fields is not allowed.
Im affraid this can't be done in MS Access... :'-(

Yes, but who cares? If you verify the record before you update, then why
COULD THIS BE ANY KIND OF A POSSIBLE problem?

What is the big deal if you enter first name first, and then go back and
fill out the last name after you filled out other data? you can't be in a
working environment that cares about this in a significant way? I am real
stunning loss as to why would it matter? If you were using a old text
system, then you have to go field by field because you did not have a mouse.

Simply set the tab order, and assume you have data entry people with a
actual brain between their ears. Simply ensure and verify that all fields
that are required are fill out. I really can't imagine any reason to remove
the ability of people to move around, and jump to fields of their choice on
screen. I am at a complete 100% loss here as to why this is a problem (or
even more worse, an actually need, or requirement).

i would not worry, or even waste any time on this issue, unless there is
some strange unknown reason for forcing that order....

 

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

TYVM, Albert. Your respond gave me more ideas to think about.


Vlado

----- Original Message -----
From: "Albert D. Kallal" <[email protected]>
Newsgroups: microsoft.public.access
Sent: Sunday, May 13, 2007 2:33 AM
Subject: Re: SQL question (hard one, I think)

dlookups when used correctly, do not impact performance in a significant
way. it is when people use them in a query in place of a relational join
where they are horrible.

On a standard form, using a few dlookups, and ensuring the values used for
the lookup are indexed, then you find little, if any performance problem.

In fact, with really small tables (say only 150,000 records), you not
noticed any slowdown at all. dlookup should be used sparingly because
often there is better approaches available (such as our .collumn()
example). However, using a few dlooups on a form is not going to effect
load times, or performance in a noticeable way. It usually poor data
designs, and poor approaches to solutions that cases the performance
issues.

I think Access does not read all 150,000 records at once. Instead, it reads
only a part of the table (query) depending on user's needs. It's very easy
to test: open a "huge" local table (query) and scroll down or use F5 to jump
to a recod in the middle or at the end of the table. You'll hear your HD
working as MS Access reads appropriate data.
Anyway, if there's a way to avoid DLookups I'll avoid them. I'll use combos
as you have explained before.
==================================

Yes, but who cares? If you verify the record before you update, then why
COULD THIS BE ANY KIND OF A POSSIBLE problem?

Not a real problem except that it needs extra programming..
==============================================

What is the big deal if you enter first name first, and then go back and
fill out the last name after you filled out other data?

It's not only about first and last name. It's accounting, salaries, etc.
==============================================

you can't be in a working environment that cares about this in a
significant way? I am real stunning loss as to why would it matter? If
you were using a old text system, then you have to go field by field
because you did not have a mouse.

I could use mouse in PC FAND as well! :O But user could not skip any
unfilled field.
All validations were much easier in PC FAND but it doesn't matter. I can
validate in Access, too... it just needs more programming than in PC FAND.
===============================================

Simply set the tab order, and assume you have data entry people with a
actual brain between their ears.

I assume they have brain... but users do all things that should not be
allowed to do. ;-)

Simply ensure and verify that all fields that are required are fill out. I
really can't imagine any reason to remove the ability of people to move
around, and jump to fields of their choice on screen. I am at a complete
100% loss here as to why this is a problem (or even more worse, an
actually need, or requirement).

i would not worry, or even waste any time on this issue, unless there is
some strange unknown reason for forcing that order....

Some fields may depend on previous fields. And that's why (sometimes) I need
to worry about how users input their data.

We had dependencies in PC FAND - they were declared on table declaration
level (as well as validations) and thus, as to dependencies and validations,
there was no need to do extra programming in any forms based on an
appropriate table. I can do most of the stuff in MS Access but it really
needs extra programming. OTOH, MS Access saves some time on reports. ;-)

P.S. Due to some reasons I can't use validations on database level.

 

[Larry Linson]

In MS Access user can enter any field just by
a mouse click or Tab key.

Set the Enabled Property of a Control to No, and the Locked Property to Yes,
and try to tab or click into the Field.

In PC FAND user
has to fill the record in given order (he/she must
press Enter on each field to confirm input; boolean
fields don't need Enter) - it is possible to move
back to any of the previous fields but skipping
"over" unfilled fields is not allowed.
Im affraid this can't be done in MS Access... :'-(

It could be done, with enough VBA code-behind-the-forms, and appropriate use
of Field Properties.

It seems quite user-UNfriendly to me... as though it were done for the
convenience of the implementer, not for the convenience of the user... and
I'd wonder exactly what it was intended to accomplish. It is certainly not
something I'd wish on my users.

If you could explain what this accomplishes, in your view, perhaps someone
could suggest how you might accomplish what you want, in an easier way.

It always leads to frustration when one expects a product to _be_ (that is,
work identically to) another product -- if you like the way the orginal
product works, use it. If you are going to move to another product, learn
how it works and work with it, not against it.

Larry Linson
Microsoft Access MVP

 

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

TYVM for your respond, Larry.
Comments inline.

Vlado

Set the Enabled Property of a Control to No, and the Locked Property to
Yes, and try to tab or click into the Field.

I can't do that since user MUST input some data in that field(s). But it
must be done AFTER he/she entered data in previous field(s).

Image 1:
http://img182.imagevenue.com/img.php?image=42605_PCFAND-edit1_122_448lo.jpg
Image 2:
http://img15.imagevenue.com/img.php?image=42690_PCFAND-edit2_122_971lo.jpg

Fields in form (from left to righht):
Dat Druh CDok Txt Vypis pPrij pVyd uPrij uVyd

PC FAND: In new record user cannot skip Druh (based on a lookup table).
Other fields (to the right of Druh) may be "Druh-dependent" so that (in many
cases) I and/or user don't have to do anything to fill in appropriate
subsequent fields (dependent fields).

1) Dependency declaration in PC FAND:
#D (Druh='K ') Txt:='prevod do pokladny'; pVyd:=0; uPrij:=0;
uVyd:=pPrij;

If user enters value K to Druh, all dependent fields (Txt, pVyd, uPrij,
uVyd) are filled in automatically (without any other user's interaction).
New record: In this case cursor moves to new record immediatelly after pPrij
input.

2) Logical control (validation rule) for Druh (must exist in lookup table)
and validation text (LookDruh is a lookup table):
#L LookDruh.exist: "Does not exist in lookup table.";

PC FAND: User can't skip Druh nor he/she can leave it empty.
Validation rule is "field based" and thus there's no need of
extra programming.
Validation for Druh is perfomed immediatelly after input.
It's very easy to recognize new record - it has dots in
unfilled fields.
MS Access: In new record user can skip Druh.
In this case, validation rules for Druh must be "record
based". Needs extra programming.
User can skip ANY field. :-(
It's difficult to recognize new record.
=============================================================

It could be done, with enough VBA code-behind-the-forms, and appropriate
use of Field Properties.

It seems quite user-UNfriendly to me... as though it were done for the
convenience of the implementer, not for the convenience of the user... and
I'd wonder exactly what it was intended to accomplish. It is certainly not
something I'd wish on my users.

If you could explain what this accomplishes, in your view, perhaps someone
could suggest how you might accomplish what you want, in an easier way.

I hope that images help to understand what I mean. See above.
=======================

It always leads to frustration when one expects a product to _be_ (that
is, work identically to) another product -- if you like the way the
orginal product works, use it. If you are going to move to another
product, learn how it works and work with it, not against it.

I'm not trying to fight against Access. I just want some more functionality
without need of extra programming.
=======================

 

[Larry Linson]

I can't do that since user MUST input some data
in that field(s). But it must be done AFTER he/she
entered data in previous field(s).

Vladimir... now you are adding requirements after the fact. But, you can still use those properties. As I said later, you can make it operate very much like your favorite product, with enough VBA code behind the Forms/Controls.

In the AfterUpdate event for the first Control, set or reset the Enabled and Locked Properties of the second one. Depending on your requirements, you may have to include additional code to handle the case in which the user moves backward to a previously entered Control.

2) Logical control (validation rule) for Druh
(must exist in lookup table) and validation
text (LookDruh is a lookup table):
#L LookDruh.exist: "Does not exist
in lookup table.";

You can specify a Field (in the Table) as "Required." Control-level validation is performed immediately after entry. With VBA code, you can force entry of Controls in a particular order (but, IMNSHO, there should be compelling reason to force that on the users).

But, the common way of handling a value in a lookup table in Access is not to force the user to type it in, but to use either a List Box or a Combo Box with its "Limit to List" property set to Yes, for the user to _choose_ the value from a list of permissible ones. Using a Combo Box with its AutoExpand property set to Yes (the default setting), it will scroll to the nearest match in its drop-down list, as the user types in a value.

It's difficult to recognize new record.

What version of Access are you using? In Access 2003, and I believe several prior versons, there is a .NewRecord property available in VBA. Open any module Window and look in Help for NewRecord Property.

No comments about the friendliness / unfriendliness to the user?

I hope that images help to understand what
I mean. See above.

I believe I understand _what_ you are doing... what I don't understand is _why_ you would subject your users to this "working in lockstep only" approach, except it was/is convenient for the developer, and that convenience was because you were developing the "PCFAND way".

I'm not trying to fight against Access. I just
want some more functionality without need
of extra programming.

I think you have the proverbial two chances of Microsoft changing Access to work as PCFAND works: slim and none. You'd like features that I consider constraining to the users, whose _ease_ of use of the application should be the primary concern; for example, you want the user to have to enter a value, then have it looked up, and get an error message if it is wrong; when, in Access, you could simply let the user choose from the list of valid values.

Of course, with Microsoft moving to "managed code", and emphasizing macros, anything is possible.

PC FAND appears to meet your requirements exactly -- is there some reason you aren't continuing to use it? If you are happy with it, and your users are happy with the applications you produce with it, why change?

Larry Linson
Microsoft Access MVP

 

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

Thanks very much for your respond.

1) New record: I know about NewRecord property. But it was something
different I was talking about: visual presentation of new record in data
editor (data grid).
2) There were no combo-boxes in PC FAND but it was very easy to implement
similar functionality. In data editor there were so called "exit procedures"
(similar to event procedures in Access). Exit procedure on empty lookup
field could open an appropriate lookup table nad there were several ways how
to handle lookup table data. It was possible to add new records to lookup
table in that exit procedure (!!!) and then close the form (or table view)
with new key value in key-field. I think A2007 has similar functionality.
3) Why not stay with PC FAND? They didn't release "Windows" version. :'-(
I've been programming in Access since it's 97 version. Now I have A2002.
I'll buy A2007 as soon as MS releases SP 2. Actual version of A2007 seems to
be VERY BUGGY, UNSTABLE and VERY SLOW (in some circumstances).
I stopped developing new PC FAND projects sometimes in 1999. If there
was WinFAND I would have not switched to Access. There are many goodies in
Access but I still miss (declaration based) PC FAND functionality. I always
hope that some of PC FAND goodies will be implemented in Access, ie. some
MVPs will pass those ideas to Access developers' team... ;-) I just wonder
why they still didn't get PC FAND to make some exploration.

Thank you very much again for all the ideas, Larry.

Vlado

"Larry Linson" <[email protected]> píše v diskusním pøíspìvku

I can't do that since user MUST input some data
in that field(s). But it must be done AFTER he/she
entered data in previous field(s).

Vladimir... now you are adding requirements after the fact. But, you can
still use those properties. As I said later, you can make it operate very
much like your favorite product, with enough VBA code behind the
Forms/Controls.

In the AfterUpdate event for the first Control, set or reset the Enabled and
Locked Properties of the second one. Depending on your requirements, you
may have to include additional code to handle the case in which the user
moves backward to a previously entered Control.

2) Logical control (validation rule) for Druh
(must exist in lookup table) and validation
text (LookDruh is a lookup table):
#L LookDruh.exist: "Does not exist
in lookup table.";

You can specify a Field (in the Table) as "Required." Control-level
validation is performed immediately after entry. With VBA code, you can
force entry of Controls in a particular order (but, IMNSHO, there should be
compelling reason to force that on the users).

But, the common way of handling a value in a lookup table in Access is not
to force the user to type it in, but to use either a List Box or a Combo Box
with its "Limit to List" property set to Yes, for the user to _choose_ the
value from a list of permissible ones. Using a Combo Box with its AutoExpand
property set to Yes (the default setting), it will scroll to the nearest
match in its drop-down list, as the user types in a value.

It's difficult to recognize new record.

What version of Access are you using? In Access 2003, and I believe several
prior versons, there is a .NewRecord property available in VBA. Open any
module Window and look in Help for NewRecord Property.

No comments about the friendliness / unfriendliness to the user?

I hope that images help to understand what
I mean. See above.

I believe I understand _what_ you are doing... what I don't understand is
_why_ you would subject your users to this "working in lockstep only"
approach, except it was/is convenient for the developer, and that
convenience was because you were developing the "PCFAND way".

I'm not trying to fight against Access. I just
want some more functionality without need
of extra programming.

I think you have the proverbial two chances of Microsoft changing Access to
work as PCFAND works: slim and none. You'd like features that I consider
constraining to the users, whose _ease_ of use of the application should be
the primary concern; for example, you want the user to have to enter a
value, then have it looked up, and get an error message if it is wrong;
when, in Access, you could simply let the user choose from the list of valid
values.

Of course, with Microsoft moving to "managed code", and emphasizing macros,
anything is possible.

PC FAND appears to meet your requirements exactly -- is there some reason
you aren't continuing to use it? If you are happy with it, and your users
are happy with the applications you produce with it, why change?

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

I'll buy A2007 as soon as MS releases SP 2. Actual
version of A2007 seems to be VERY BUGGY,
UNSTABLE and VERY SLOW (in some circumstances).

Many users wait for a Service Pack or two to correct problems before they
consider deploying a new version in their environment. And, the major
changes to the user interface are a stumbling block to some organizations,
as well.

I stopped developing new PC FAND projects
sometimes in 1999. If there was WinFAND I
would have not switched to Access.

I was sure that I'd move to Paradox for Windows when it became available,
but the Windows version did not have enhancements over the DOS version to
make it competitive to Access.

There are many goodies in Access but I still miss (declaration
based) PC FAND functionality.

It's possible that "declaration based functionality" has a special meaning
to you; but it really does not define a particular approach to me.

I always hope that some of PC FAND goodies will be
implemented in Access, ie. some MVPs will pass those
ideas to Access developers' team... ;-)

I'm afraid, even with all that you've written and the examples, I just don't
understand enough about it to pass on a coherent description.

I just wonder why they still didn't get PC FAND
to make some exploration.

Perhaps they were not aware of it. I used a few DOS database programs, and
one that I wrote myself in Turbo Basic to provide PC-based data entry and
edit for a mainframe product that worked much like Windows PC databases. I
did rather thorough research for some tech support articles for the
mainframe version of Lotus 1-2-3, and know that PC FAND was not a product
that I used or found mentioned in the resources I researched.

Thank you very much again for all the ideas, Larry.

You're welcome.

Larry Linson
Microsoft Access MVP

 

[AaronKempff]

It's possible that "declaration based functionality" has a special meaning
to you; but it really does not define a particular approach to me.

Lawrence, Your vast knowledge in the field of Access surpasses the
majority of us dilettantes, however please allow me this rare
opportunity to clarify a point on which your awareness is, it would
seem, to be found wanting.

Declarative functionality is a feature of 4GL languages, of which SQL
is one. To leverage your excellent aforementioned examples, the
specifying of a Field (in the Table) as "Required." would be
considered declarative functionality, whereas control-level validation
performed immediately after entry with VBA code would be the
equivalent 3GL procedural approach. Declarative functionality is of
particular importance for a SQL products such as Access because of the
potential for consideration by the optimizer (for instance, using
table properties to generate a query plan), which pays no regard to
VBA code in the user interface layer. In a RAD environment such as
Access, anything which can be deferred to "The System" is a boon.

 

[David W. Fenton]

(e-mail address removed) wrote in

Lawrence, Your vast knowledge in the field of Access surpasses the
majority of us dilettantes, however please allow me this rare
opportunity to clarify a point on which your awareness is, it
would seem, to be found wanting.

Declarative functionality is a feature of 4GL languages, of which
SQL is one. To leverage your excellent aforementioned examples,
the specifying of a Field (in the Table) as "Required." would be
considered declarative functionality, whereas control-level
validation performed immediately after entry with VBA code would
be the equivalent 3GL procedural approach. Declarative
functionality is of particular importance for a SQL products such
as Access because of the potential for consideration by the
optimizer (for instance, using table properties to generate a
query plan), which pays no regard to VBA code in the user
interface layer. In a RAD environment such as Access, anything
which can be deferred to "The System" is a boon.

So, from that description, it's not an Access feature that is being
requested, but a database engine feature.

It also sounds to me like Jet has "declaration based functionality,"
though perhaps not a complete implementation (maybe triggers are
considered part of it?).

 

[AaronKempff]

So, from that description, it's not an Access feature that is being
requested, but a database engine feature.

Yes, database engine it is. Let us not forget, though, Access 2007 has
its own engine, which makes it increasingly difficult to disregard
engine features when discussing 'Access' features.

It also sounds to me like Jet has "declaration based functionality,"
though perhaps not a complete implementation (maybe triggers are
considered part of it?).

You are entitled to such a classification, naturally, but in my
estimation triggers are not 'declarative functionality' because they
are procedural code to which the optimizer pays no regard. Reactive
rather than proactive, to put it crudely.