Problems moving SQL to Code using Mark Plumpton method

[richard]

Hi

Am using Mark Plumptons method to speed up form loading.
This is the first time I have done this so being extra careful. Problem is I
have a combo box which has a WHERE selection which Marks example does not
provide an example code.
The code I have below creates a Syntax error, could someone please advise
what the problem is.

Me.model.RowSource = _
"SELECT type " & _
"FROM tblcartype " & _
"WHERE tblcartype.carmake=Me.model" & _
"ORDER BY type;"

If I take out the '&_' at the end of the WHERE clause then the ORDER clause
appears in red

Many thanks

Richard

 

[Allen Browne]

You need to concatenate the value of the Model into the string.

If carmake is a Text field (when you open the table in design view), you
need:
"WHERE tblcartype.carmake = """ & Me.model & """" & _
If it's a text field, skip the extra quotes.
For an explanation of the quotes, see:
http://allenbrowne.com/casu-17.html

 

[richard]

Allen

This worked fine (once I actually had the right names for the fields in
place),

ie code is now as follows

Me.model.RowSource = _
"SELECT type " & _
"FROM tblcartype " & _
"WHERE tblcartype.carmake = """ & Me.make & """" & _
"ORDER BY type;"

However the record source for the field 'Model' with the WHERE clause now
has a fixed criteria defined by the code and the selection of 'Make'
If the user selects in the combobox 'Make' as Audi then the various Audi
Models are available in the combobox 'Model'(this is fine), But if the user
has made a mistake and meant to click Aston Martin, even when they recorrect
this in 'Make' the available selections in 'Model' are still the Audi models.
In an attempt to sort this out I moved the above code above to the combo box
'make' after update event and it now appears to be working correctly.

Is this the best way of achieveing my end goals which are

1 Speed up my database using the Mark Plumpton method

2 Allow the user to change their minds regarding what is selected in combo
box 'Make'.

If my problem of the code in the 'Form onload event' fixing the criteria in
the rowsource query of combobox 'Model' persists and I have to use the fix
above as I have described with other comboboxes will this negate the work I
am trying to do using the Mark Plumpton method in speeding up the database
and hopefully making it more efficient, or am I just creating a future
problem which will hurt me later??

Your input would be greatly appreciated

Richard

 

[Allen Browne]

There's a couple of ways around that.

One is to use a fully qualified reference in the WHERE clause, e.g.:
"WHERE tblcartype.carmake= [Forms].[Form1].[Model] " & _
This will work, though there could be a delay before Access updates the
list, so you may need to explicitly Requery the combo.

The alternative is to reassign the RowSource when needed. Since the
RowSource has only one field (named Type), I assume this is the display
field, and so you really only need the other items in the list when the
combo is dropped down. You could therefore use its Enter event to assign the
RowSource.

If a combo has multiple columns, and the bound column is not the display
column, it won't display properly unless the list is loaded. You woud
therefore need to use the Current event of the form, and the AfterUpdate of
whatever controls it depends on, to load the right list.

 

[David W. Fenton]

One is to use a fully qualified reference in the WHERE clause,
e.g.:
"WHERE tblcartype.carmake= [Forms].[Form1].[Model] " & _
This will work, though there could be a delay before Access
updates the list, so you may need to explicitly Requery the combo.

I also recommend defining the form reference as a parameter in the
query. This is essential in some contexts in A2K3, as I found out
recently with an app upgraded from A97, where saved queries with
form references resolved Nulls to zero-length-strings until I
defined parameters with appropriate data types.

 

[Allen Browne]

Interesting David.

I've had the opposite problem when you define a parameter of type Text, JET
resolves it to a ZLS instead of recognising the Null. Details:
http://allenbrowne.com/bug-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

One is to use a fully qualified reference in the WHERE clause,
e.g.:
"WHERE tblcartype.carmake= [Forms].[Form1].[Model] " & _
This will work, though there could be a delay before Access
updates the list, so you may need to explicitly Requery the combo.

I also recommend defining the form reference as a parameter in the
query. This is essential in some contexts in A2K3, as I found out
recently with an app upgraded from A97, where saved queries with
form references resolved Nulls to zero-length-strings until I
defined parameters with appropriate data types.

 

[David W. Fenton]

I've had the opposite problem when you define a parameter of type
Text, JET resolves it to a ZLS instead of recognising the Null.
Details:
http://allenbrowne.com/bug-13.html

But does the problem happen for text parameters for fields of type
text? In my case, 4 of the form fields were text, one was a long and
one was a Date. It was the text fields that were evaluating as ZLS
without the parameter when converted from A97 to A2K3. When the text
parameter was declared, the correct Null values were passed.

But in the case where I encountered it, text was the correct
parameter type, whereas in your example, text is *not* the correct
parameter type (or, at least, not the most narrowly-defined thype).

 

[Allen Browne]

The problem occurs when a parameter is declared as type Text, and it takes
the form of:
[Forms].[Form1].[Text0]
so that it is resolved by the Expression Service. In this case, JET
misinterprets the Null text box as a zero-length string.

This occurs regardless of the data type of the field that this parameter is
applied on. As an example, if you have:
- a table with a Text field where Allow Zero Length is Yes;
- some records with Nulls, and some with zero-length strings;
- an unbound text box named Text0 on Form1, value Null;
- declared parameter of:
[Forms].[Form1].[Text0]
and same criteria under the Text field
then the query returns the zero-length records and not the nulls.

It ought not to return the zls records: they do not match the Null text box.

 

[David W. Fenton]

The problem occurs when a parameter is declared as type Text, and
it takes the form of:
[Forms].[Form1].[Text0]

Does it make a difference if you use bangs instead of dots (I never
use dots)?

so that it is resolved by the Expression Service. In this case,
JET misinterprets the Null text box as a zero-length string.

This occurs regardless of the data type of the field that this
parameter is applied on. As an example, if you have:
- a table with a Text field where Allow Zero Length is Yes;

If that's a part of the requirement, then I'll never encounter it,
as I will never allow ZLS.

- some records with Nulls, and some with zero-length strings;
- an unbound text box named Text0 on Form1, value Null;
- declared parameter of:
[Forms].[Form1].[Text0]
and same criteria under the Text field
then the query returns the zero-length records and not the nulls.

It ought not to return the zls records: they do not match the Null
text box.

In my case, there were no ZLS's allowed in the table, and the Null
textboxes on the forms were being resolved by the expression service
as ZLS (I tested this by putting the expression
[Forms]![MyForm]![TextBox]="" in my query, and that returned TRUE).
When I defined the parameter for these form control references, they
stopped returning ZLS's and instead returned Null (as they had in
A97 without the parameters).

It's all moot for this particular app since the code was entirely
rewritten to not use stored queries at all, but to just execute the
SQL directly, but it was a change in behavior that really caused a
major problem for the client, in an app that's been in daily
production use since the last quarter of 1997 (it's been through
many revisions, but not in the particular area where this problem
came up).

I have not attempted to reproduce this, but I did check the the
problem results returned are as I said (ZLS without parameter and
Null with it) before my previous reply.

I wonder what other factor is involved? Were your textboxes bound or
unbound?

 

[Allen Browne]

The text boxes I tested are unbound.

I don't think Bang/dot is a factor.

The Allow Zero Length is not a factor: it's just that to demonstrate that
JET matches the Null text box to the zero-length-string entries, I needed
some records taht contained a zls.

The problem seems to be in the way the Expression Service evaluates the Null
as being a zero-length string. DLookup() has a similar problem: it also uses
the ES, and it returns Null where a field actually contains a zls.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The problem occurs when a parameter is declared as type Text, and
it takes the form of:
[Forms].[Form1].[Text0]

Does it make a difference if you use bangs instead of dots (I never
use dots)?

so that it is resolved by the Expression Service. In this case,
JET misinterprets the Null text box as a zero-length string.

This occurs regardless of the data type of the field that this
parameter is applied on. As an example, if you have:
- a table with a Text field where Allow Zero Length is Yes;

If that's a part of the requirement, then I'll never encounter it,
as I will never allow ZLS.

- some records with Nulls, and some with zero-length strings;
- an unbound text box named Text0 on Form1, value Null;
- declared parameter of:
[Forms].[Form1].[Text0]
and same criteria under the Text field
then the query returns the zero-length records and not the nulls.

It ought not to return the zls records: they do not match the Null
text box.

In my case, there were no ZLS's allowed in the table, and the Null
textboxes on the forms were being resolved by the expression service
as ZLS (I tested this by putting the expression
[Forms]![MyForm]![TextBox]="" in my query, and that returned TRUE).
When I defined the parameter for these form control references, they
stopped returning ZLS's and instead returned Null (as they had in
A97 without the parameters).

It's all moot for this particular app since the code was entirely
rewritten to not use stored queries at all, but to just execute the
SQL directly, but it was a change in behavior that really caused a
major problem for the client, in an app that's been in daily
production use since the last quarter of 1997 (it's been through
many revisions, but not in the particular area where this problem
came up).

I have not attempted to reproduce this, but I did check the the
problem results returned are as I said (ZLS without parameter and
Null with it) before my previous reply.

I wonder what other factor is involved? Were your textboxes bound or
unbound?