General Rules - Receiving unexplained errors

[cp2599]

I'm having problems identifying why sometimes dlookup works in one
procedure but doesn't work in another. I'm trying to populate an
unbounded textbox with a value based on the selected value from an
unbounded combo box. I'm performing the dlookup in the After Update
procedure of the unbounded combo box and am getting the 'cancelled
previous operation' error.

Could someone please explain the rules about what you can and can't do
in Before and After Update versus a command button or current
procedure or refer me to a book that will explain the rules.

I'm having a similar problems as to when I can reference an attribute
from another opened form / subform as well. Sometimes it will
recognize it (i.e. command button) and sometimes it won't After Update.

 

[Jeanette Cunningham]

Dlookup errors in my experience are often caused by incorrect syntax or a
value that is not what you think it is.

To debug dlookup errors, I usually use this process.

Dim strCriteria As String
Dim varReturn as Variant

strCriteria = "[TheField] = " & Me.ControlName 'number field

strCriteria = "[TheField] = """ & Me.ControlName & '""" 'text field

Debug.Print strCriteria

varReturn = (Dlookup("[FieldName]", "TableOrQueryName" strCriteria)
Debug.Print varReturn
If Len(varReturn) >0 Then
Me.Control2 = varReturn
End If

To use the debug process, run your form using the above code with names
changed to suit your form, then open the immediate window with Ctl+G.
See what access gets for strCriteria and varReturn.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[BruceM]

Just to clarify, use one or the other but not both of the strCriteria lines,
depending on whether the unbound combo box (ControlName in Jeanette's code)
containing the value to be used in DLookup is a number or text. Note that
it is the value of the bound column, which is not necessarily what you see
in the combo box.

Note that the arrangement of quotes if text is either:
strCriteria = "[TheField] = " " " & Me.ControlName & " " " "
or
strCriteria = "[TheField] = ' " & Me.ControlName & " ' "

Spaces added for clarity. Use the first if [TheField] could contain
apostrophes (Bob's Garage). Use the second if it could contain quotes. Use
either if no quotes or apostrophes. If there could be either, there are
ways, but no need to get into that now.

Also, add a comma to DLookup:
varReturn = (Dlookup("[FieldName]", "[TableOrQueryName]", strCriteria)

Square brackets are needed if the name contains anything other that letters,
numbers, or underscores, but do no harm in any case.

Dlookup errors in my experience are often caused by incorrect syntax or a
value that is not what you think it is.

To debug dlookup errors, I usually use this process.

Dim strCriteria As String
Dim varReturn as Variant

strCriteria = "[TheField] = " & Me.ControlName 'number field

strCriteria = "[TheField] = """ & Me.ControlName & '""" 'text field

Debug.Print strCriteria

varReturn = (Dlookup("[FieldName]", "TableOrQueryName" strCriteria)
Debug.Print varReturn
If Len(varReturn) >0 Then
Me.Control2 = varReturn
End If

To use the debug process, run your form using the above code with names
changed to suit your form, then open the immediate window with Ctl+G.
See what access gets for strCriteria and varReturn.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I'm having problems identifying why sometimes dlookup works in one
procedure but doesn't work in another. I'm trying to populate an
unbounded textbox with a value based on the selected value from an
unbounded combo box. I'm performing the dlookup in the After Update
procedure of the unbounded combo box and am getting the 'cancelled
previous operation' error.

Could someone please explain the rules about what you can and can't do
in Before and After Update versus a command button or current
procedure or refer me to a book that will explain the rules.

I'm having a similar problems as to when I can reference an attribute
from another opened form / subform as well. Sometimes it will
recognize it (i.e. command button) and sometimes it won't After Update.