Problem with DLookUp

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This concerns VBA in Access forms. I trust that this is the most appropriate newsgroup.

How can I get DLookUp to work when the criteria is derived from a combo box? If I try to enter the current value of the combo box directly I get run-time 2001 - "You cancelled the previous operation". If I assign the current combo box value to an intermediate local variable that also fails and if I assign it to a public variable and try to pass that using a function - as with the trick that works with parameter passing to a query - that fails also. I am out of ideas.
 
Where are you trying to use the DLookup when you attempt to get the value
from the combo box? Is the combo box on the main form or on a subform?

--
Wayne Morgan
Microsoft Access MVP


Peter Hallett said:
This concerns VBA in Access forms. I trust that this is the most appropriate newsgroup.

How can I get DLookUp to work when the criteria is derived from a combo
Click to expand...
box? If I try to enter the current value of the combo box directly I get
run-time 2001 - "You cancelled the previous operation". If I assign the
current combo box value to an intermediate local variable that also fails
and if I assign it to a public variable and try to pass that using a
function - as with the trick that works with parameter passing to a query -
that fails also. I am out of ideas.
 
The "You cancelled the previous operation" error could be
indicative of a corrupt database rather than dlookup
syntax.

As long as you remember to put square brackets around
table and field names that have embedded spaces and ensure
that the criteria punctuation matches the datatypes
(quotes for stings and #s for dates etc) you should not
have a problem with the dlookup function

-----Original Message-----
This concerns VBA in Access forms. I trust that this is
Click to expand...
the most appropriate newsgroup.
How can I get DLookUp to work when the criteria is
Click to expand...
derived from a combo box? If I try to enter the current
value of the combo box directly I get run-time 2001 - "You
cancelled the previous operation". If I assign the
current combo box value to an intermediate local variable
that also fails and if I assign it to a public variable
and try to pass that using a function - as with the trick
that works with parameter passing to a query - that fails
also. I am out of ideas.
 
The DLookUp was run from within a sub-form and queried a table not currently bound to any open form. A criterion of the form "[ProductID]=6" or "ProductID= " & Me![Fred] is fine as long as [Fred] is not the currently displayed value in a combo box on the sub-form. If it is then the error message is displayed. This is not the first time I have encountered this problem. I got much the same sort of thing recently in another similar situation using a different set of forms and sub-forms.
 
I certainly had a corrupt database just a short time ago and needed to ask Microsoft for one of its undocumented fixes. It is apparently undocumented because one of the ways it can 'fix' corrupt databases is to destroy them! Luckily it did not do this in my case. Instead, it removed about 1.5Mb of junk, invisible to Compact and Repair, and appears to have left me with a nice clean version. If you note my reply to Wayne you will see that I encountered an almost identical situation not long ago in different circumstances. Cleaned-up database or not, however, I don't seem to be to get around this one.
 
long as [Fred] is not the currently displayed value in a combo box on the
sub-form<

By this, do you mean that if you have a control other than a combo box bound
to [Fred] that it works? If that is the case, is the comb box corrupted? Try
making a new one and see what happens. How may columns does the combo box
have? What is the Bound Column? The bound column is the one that will give
you the value of the combo box and may not be what you are actually seeing
in the combo box. A combo box is frequently used to give the user and easy
text value to select while a different value is actually used in the
database.

Have you tried doing this with a new, clean database to see what happens?

--
Wayne Morgan
Microsoft Access MVP


Peter Hallett said:
The DLookUp was run from within a sub-form and queried a table not
Click to expand...
currently bound to any open form. A criterion of the form "[ProductID]=6"
or "ProductID= " & Me![Fred] is fine as long as [Fred] is not the currently
displayed value in a combo box on the sub-form. If it is then the error
message is displayed. This is not the first time I have encountered this
problem. I got much the same sort of thing recently in another similar
situation using a different set of forms and sub-forms.
 
I still cannot get out of this one. I have just created a new form with two sub-forms, one of which has a sub-sub-form. The problem is wholly confined to the sub-sub-form and is as follows:

The combo box, [CNVATRate], is bound to tbl_VAT_Rates, which is not linked to, or used by, the main form nor any of the other sub-forms. Nor is it bound to the sub-sub-form, in any way other than to the combo box in question

tbl_VAT_Rates comprises two columns. VAT rate category is listed in the first (VAT_Rate), as strings, and the associated percentage Value Added Tax rates are listed in the second (VAT_Percent), as single precision numbers with one place of decimals. Thus "Standard" in the first column is associated with 17.5 in the second. The combo box, [CNVATRate], is bound to this table and consequently offers the options "Non-VAT", "Reduced","Standard" and "Zero-Rated". I cannot work out how to get the matching value of the second column from the combo box directly, so I use the following bit of VBA to asign the matching percentage rate to the field [CNVAT]. (The CN prefix simply indicates that this sub-sub-form is used to issue credit notes.)

If (IsNull(Me![CNVATRate]) = False) And Me![CNCredit] > 0 The
Me![CNVAT] = DLookup("VAT_Percent", "tbl_VAT_Rates", "VAT_Rate =" & Me![CNVATRate]
End I

This is pretty self-explanatory. It just says, "If the VAT Rate category has been selected in the combo box [CNVATRate] and a non-zero credit has been entered in [CNCredit] then look up the matching percentage VAT in tbl_VAT_Rates, the table to which the combo box is also bound, and display it in the unbound field [CNVAT] (from where it can subsequently be multiplied by [CNCredit] to arrive at the amount of VAT to be credited.

As with all other attempts I have made to use DLookUp in this way, on a number of other forms and in different circumstances using different combo boxes, the instruction fails with the, "You cancelled the previous operation," error message. The syntax is acceptable to the the compiler and, when checked though the debugger, the Me![CNVATRate] criterion always displays the correct value when DLookUp is run - ie it faithfully reflects the currently selected option in the combo box

In circumstances where there is no criterion, DLookUp works just fine. In cases where a criterion is involved, however, if that criterion is derived from a combo box the result is invariably failure, wherever or however DLookUp is used. I cannot understand this nor see how to solve my problem without, perhaps, using a query followed by a Requery after the VAT Rate has been selected in the combo box. However, that is not the point. It is always possible to 'find another way' but what I need to know is why DLookUp won't play ball in these circumstances. It is galling not to be able to take advantage of a useful function because I cannot get it to work for reasons that I do not comprehend.
 
Thus "Standard" in the first column is associated with 17.5 in the second. The combo box, [CNVATRate], is bound to this table and consequently offers the options "Non-VAT", "Reduced","Standard" and "Zero-Rated". I cannot work out how to get the matching value of the second column from the combo box directly
Click to expand...

You can use the combo's Column property. It's a zero-based array of
the fields in the combo's rowsource; so the VAT would be

[CNVATRate].Column(1)
 
Thanks, John,

It is an excellent solution. I still don’t know why DLookUp fails to work but, now that I can interrogate the unbound columns of the combo box, who cares? The only snag is that my next job is to go back through my 4.5Mb database and throw a whole lot of unnecessary code and queries into the wpb. Still, that should slim it down a bit.

To anyone else wishing to use this method, I would make the, perhaps obvious, point that, although the unbound columns need not be displayed in the combo box drop-down list, they must be included in the query or SQL statement to which the combo box is bound.

Peter Hallett
 
Back
Top