DLookup Strikes Again

[Guest]

An old problem, that I aired some time ago, has raised its head again

I have a table that lists aircraft registration numbers and other associated details. Each record contains a Boolean field entitled ‘Selected’. A form has a combo box bound to this table via a query that selects those registration numbers for which ‘Selected’ is true. A query, run prior to the opening of this form, dynamically determines which aircraft are currently available, and which are not, by setting ‘Selected’ true or false respectively for each of the records in the table. The combo box on the form thus only offers the currently selectable aircraft. That works just fine. The combo box options are not, however, limited to the list derived from the table. This allows the selection of an aircraft that does not currently feature in the ‘Selected’ list, by typing-in its registration number. (There are, needless to say, good reasons for wanting to do this on occasions.) If a non-listed aircraft is selected then two subsequent tests must be performed :â€

1. If, due perhaps to a typo, the entered registration number does not correspond to any aircraft listed in the table, whether selected or not, then an error must be flagged
2. If the entered registration number corresponds to a currently non-selected aircraft then the additional details relating to it must be separately retrieved. The code for this is not discussed here

An obvious, and simple, way to perform these tests seems to be to use DLookup to individually check all the registration numbers selected or entered in the combo box to determine whether they appear in the table to which the combo box is bound. This was done as follows:â€

Private Sub Aircraft_AfterUpdate(
Dim Selected_AC As Strin

Selected_AC = DLookup("Registration", "tbl_Aircraft", "Registration = " & Me![Aircraft]
....etc
....etc
End Su

Me![Aircraft] is the registration number currently displayed in the combo box. The theory is that if Selected_AC is Null then the entered registration number does not correspond to an aircraft in the table – an error condition. Conversely, if the registration number is valid, then a similar DLookup test could subsequently be run to determine whether the ‘Selected’ field, corresponding to that aircraft registration, is true. If not then the aircraft is listed in the table but does not feature in the current selectable list. The above sub fails on the first test, however, because of the old DLookup problem. Apparently because Me![Aircraft] is a combo box, DLookup does not work, yielding, instead, Run Time Error ‘2001’ : “You cancelled the previous operation.†This has happened on every other occasion that I have tried to use DLookup in this way and I am therefore forced to the conclusion that the DLookup syntax I have used is incorrect or that DLookup is unusable in this context. How can I get around this

 

[Wayne Morgan]

It appears from your description that Registration is a text field (i.e.
N123XT), yet your syntax is for a numeric value. The DLookup for a text
value should look like

Selected_AC = DLookup("Registration", "tbl_Aircraft", "Registration = '" &
Me![Aircraft] & "'")

Also, how many columns are in the combobox and which column is the Bound
Column? The value of the combobox comes from the Bound Column, which may not
be what is displayed in the text window of the combobox. You can use the
column property of the combobox to refer to a different column of the
currently selected item.

Example:
Selected_AC = DLookup("Registration", "tbl_Aircraft", "Registration = '" &
Me![Aircraft].Column(1) & "'")


The Column index is zero based, so 0 is the 1st column, 1 the 2nd column,
etc. You may also want to check for a Null value before calling DLookup,
since if the value is Null you aren't going to find a result anyway.

--
Wayne Morgan
Microsoft Access MVP

An old problem, that I aired some time ago, has raised its head again.

I have a table that lists aircraft registration numbers and other

associated details. Each record contains a Boolean field entitled 'Selected
'. A form has a combo box bound to this table via a query that selects
those registration numbers for which 'Selected' is true. A query, run prior
to the opening of this form, dynamically determines which aircraft are
currently available, and which are not, by setting 'Selected' true or false
respectively for each of the records in the table. The combo box on the
form thus only offers the currently selectable aircraft. That works just
fine. The combo box options are not, however, limited to the list derived
from the table. This allows the selection of an aircraft that does not
currently feature in the 'Selected' list, by typing-in its registration
number. (There are, needless to say, good reasons for wanting to do this on
occasions.) If a non-listed aircraft is selected then two subsequent tests
must be performed :-

1. If, due perhaps to a typo, the entered registration number does not

correspond to any aircraft listed in the table, whether selected or not,
then an error must be flagged.

2. If the entered registration number corresponds to a currently

non-selected aircraft then the additional details relating to it must be
separately retrieved. The code for this is not discussed here.

An obvious, and simple, way to perform these tests seems to be to use

DLookup to individually check all the registration numbers selected or
entered in the combo box to determine whether they appear in the table to
which the combo box is bound. This was done as follows:-

Private Sub Aircraft_AfterUpdate()
Dim Selected_AC As String

Selected_AC = DLookup("Registration", "tbl_Aircraft", "Registration = " & Me![Aircraft])
...etc.
...etc.
End Sub

Me![Aircraft] is the registration number currently displayed in the combo

box. The theory is that if Selected_AC is Null then the entered
registration number does not correspond to an aircraft in the table - an
error condition. Conversely, if the registration number is valid, then a
similar DLookup test could subsequently be run to determine whether the
'Selected' field, corresponding to that aircraft registration, is true. If
not then the aircraft is listed in the table but does not feature in the
current selectable list. The above sub fails on the first test, however,
because of the old DLookup problem. Apparently because Me![Aircraft] is a
combo box, DLookup does not work, yielding, instead, Run Time Error '2001' :
"You cancelled the previous operation." This has happened on every other
occasion that I have tried to use DLookup in this way and I am therefore
forced to the conclusion that the DLookup syntax I have used is incorrect or
that DLookup is unusable in this context. How can I get around this?

 

[Guest]

Wayne

Thank you for your response. It provided a solution to a long-standing puzzle. The source of the trouble was the incorrect syntax you identified. Having read the definition of the DLookup function in the Access VBA Help, I was unaware of the distinctly arcane syntax required when involving string fields in the criterion. Perhaps someone should point out to Microsoft (are you listening, MS?) that the present on-line Help for this function is not only deficient but actually misleading. Without your assistance I have no idea how I would have resolved the problem. However, with that assistance I have now got everything working nicely

As a post script, you seem to have misunderstood the purpose to which I applied the DLookup function so perhaps I could clarify that

The aircraft registration number selection combo box can return three different results:
1. A registration number chosen from the drop down list – ie one that is listed in tbl_Aircraft and which has its ‘Selected’ field in that table currently set True
2. A registration number that is listed in tbl_Aircraft but which has its ‘Selected’ field in that table currently set False – ie the aircraft registration is valid but does not currently feature in the combo box drop-down list. In order to enter a non-listed registration number, the Limit to List property of the combo box has, of course, to be set to No
3. An invalid registration number which is not listed in tbl_Aircraft. Again, entry demands that the Limit to List property is set to No

The function of the following bit of code – an extended and corrected version of what I supplied earlier - will therefore be clear:

Private Sub Aircraft_AfterUpdate(
Dim Selected_AC As Strin
Dim Selectcheck As Boolea

Selected_AC = DLookup("[Registration]", "tbl_Aircraft", "[Registration] = '" & Me![Aircraft] & "'"
Selectcheck = DLookup("[Selected]", "tbl_Aircraft", "[Registration] = '" & Selected_AC & "'"
Etc
Etc
End Su

The first DLookup will return a valid registration number in Selected_AC if that registration number is listed in tbl_Aircraft. If not then the run-time error ‘Invalid Use of Null’ will result

The second DLookup will only run if the first returns a valid registration number and will then set Selectcheck to True or False, indicating whether the chosen registration number is in the current ‘Selected’ list or not. This provides all the information required for the further processing of the aircraft data

Invoking a run time error is admittedly a bit of a crude way of suspending program operation when an invalid registration number is supplied but it is effective. Nevertheless, I will probably change it to the following rather more elegant procedure:

Private Sub Aircraft_AfterUpdate(
Dim Selected_AC As Strin
Dim Selectcheck As Boolea

If IsNull(DLookup("[Registration]", "tbl_Aircraft", "[Registration] = '" & Me![Aircraft] & "'")) The
MsgBox(“Invalid Registration Numberâ€, Etc.
Els
Selected_AC = DLookup("[Registration]", "tbl_Aircraft", "[Registration] = '" & Me![Aircraft] & "'"
Selectcheck = DLookup("[Selected]", "tbl_Aircraft", "[Registration] = '" & Selected_AC & "'"
End I
Etc
Etc
End Su

This avoids the ‘crash’ but involves three DLookups, instead of two. The additional run time overhead should, however, be negligible. I hope that this resolves the confusion. As you will see, the aim is not to check for a Null but to use the Null return from DLookup as a diagnostic tool. If you can see an easier way of doing this job, please let me have your thoughts. In the mean time, please accept my thanks for your assistance

 

[Wayne Morgan]

If Selected_AC was a variant instead of a string it would accept a Null, so
you could just use the one DLookup statement then check to see if
Selected_AC is null. However, trapping the error is not that difficult and
is sometimes the easiest way to do things. We tend to think of errors as
doing something wrong or that something is broken, but that's not
necessarily the case. For example, when trying to access files, trapping the
error when it results (i.e. the disk isn't in the floppy drive) beats
running a whole bunch of checks first. The only difference is whether
Windows/VBA tells you that there was an error or if you pre-discover the
error for yourself. One thing I would do though, is I would definitely put a
few good comments into the code at this point describing what you're doing.
Relying on an error trap is not as intuitive as doing the checking yourself
when you come back in a year to two and try to read the code to see how you
did it.

--
Wayne Morgan
Microsoft Access MVP

Wayne,

Thank you for your response. It provided a solution to a long-standing

puzzle. The source of the trouble was the incorrect syntax you identified.
Having read the definition of the DLookup function in the Access VBA Help, I
was unaware of the distinctly arcane syntax required when involving string
fields in the criterion. Perhaps someone should point out to Microsoft (are
you listening, MS?) that the present on-line Help for this function is not
only deficient but actually misleading. Without your assistance I have no
idea how I would have resolved the problem. However, with that assistance I
have now got everything working nicely.

As a post script, you seem to have misunderstood the purpose to which I

applied the DLookup function so perhaps I could clarify that?

The aircraft registration number selection combo box can return three different results:-
1. A registration number chosen from the drop down list - ie one that is

listed in tbl_Aircraft and which has its 'Selected' field in that table
currently set True.

2. A registration number that is listed in tbl_Aircraft but which has its

'Selected' field in that table currently set False - ie the aircraft
registration is valid but does not currently feature in the combo box
drop-down list. In order to enter a non-listed registration number, the
Limit to List property of the combo box has, of course, to be set to No.

3. An invalid registration number which is not listed in tbl_Aircraft.

Again, entry demands that the Limit to List property is set to No.

The function of the following bit of code - an extended and corrected

version of what I supplied earlier - will therefore be clear:-

Private Sub Aircraft_AfterUpdate()
Dim Selected_AC As String
Dim Selectcheck As Boolean

Selected_AC = DLookup("[Registration]", "tbl_Aircraft",

"[Registration] = '" & Me![Aircraft] & "'")

Selectcheck = DLookup("[Selected]", "tbl_Aircraft", "[Registration] = '" & Selected_AC & "'")
Etc.
Etc.
End Sub

The first DLookup will return a valid registration number in Selected_AC

if that registration number is listed in tbl_Aircraft. If not then the
run-time error 'Invalid Use of Null' will result.

The second DLookup will only run if the first returns a valid registration

number and will then set Selectcheck to True or False, indicating whether
the chosen registration number is in the current 'Selected' list or not.
This provides all the information required for the further processing of the
aircraft data.

Invoking a run time error is admittedly a bit of a crude way of suspending

program operation when an invalid registration number is supplied but it is
effective. Nevertheless, I will probably change it to the following rather
more elegant procedure:-

Private Sub Aircraft_AfterUpdate()
Dim Selected_AC As String
Dim Selectcheck As Boolean

If IsNull(DLookup("[Registration]", "tbl_Aircraft", "[Registration] =

'" & Me![Aircraft] & "'")) Then

MsgBox("Invalid Registration Number", Etc.)
Else
Selected_AC = DLookup("[Registration]", "tbl_Aircraft", "[Registration] = '" & Me![Aircraft] & "'")
Selectcheck = DLookup("[Selected]", "tbl_Aircraft", "[Registration] = '" & Selected_AC & "'")
End If
Etc.
Etc.
End Sub

This avoids the 'crash' but involves three DLookups, instead of two. The

additional run time overhead should, however, be negligible. I hope that
this resolves the confusion. As you will see, the aim is not to check for a
Null but to use the Null return from DLookup as a diagnostic tool. If you
can see an easier way of doing this job, please let me have your thoughts.
In the mean time, please accept my thanks for your assistance.