Collection does not contain the Automation Object

Hello Experts,

The txtUser textbox on the Switchboard accepts the User's Initials.

(let's say ABC is the txtUser.Text)

strCurrentUser is set to that string.

The Private Function fnCheckUser() (behind the Switchboard form) breaks on the following line:

fnCurrentUser = DLookup("fldUsers", "tblUsers, "fldUsers = " & strCurrentUser)

The error message says:

The result of the query resulted in the following error: The Object does not contain the Automation Object "ABC"

and stops the program.

There are no OLE fields in this very simple DB.  There is Access97 for the Program DB and the Data DB.  I'm not calling a Named Query (although I suspect Access is calling the DLookup a "query")

What am I doing wrong?  I think the syntax is right and the Text field "fldUser" does contain "ABC" (and all the other sets I tried)


Who is Participating?
jschrismanConnect With a Mentor Commented:
Since strCurrentUser is a string, you must enclose it in single or double quotes like so...

fnCurrentUser = DLookup("fldUsers", "tblUsers, "fldUsers = '" & strCurrentUser & "'")

Notice the new single quote after the = sign, and the single quote appended to the end of the criteria string.

Access needs to know what it is trying to find.  When the function is run, it puts the literal value in the expression.  By forcing the variable strCurrentUser to be a string, even though we may have assigned it as one earlier, the expression as stand alone is valid.  You can always test the string to see if it is recognized as one by taking out the element and printing it in the debug window.

To make life simpler and the code easier to read, I create public constants like gConQuote which is """".

  Public Const gConQuote As String = """"

   DLookup("fldUsers", "tblUsers, "fldUsers = " &  gConQuote & strCurrentUser & gConQuote)

or force it to be a string by using

   DLookup("fldUsers", "tblUsers", "fldUsers = " & CStr(strCurrentUser))

This way when I read the code, there is no doubt what I was trying to do.

As an aside, I much prefer to use Jim's second approach to quotes in queries, because if the string contains a name like O'Connor, you'll get an error.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

MacRenaAuthor Commented:
Hi JimM, hope you are well!

Thank you for this great 'constant' idea!  It works nicely.

However, I hope you will forgive me in awarding jschristman, since his Comment...
>>"Since strCurrentUser is a string, you must enclose it in single or double quotes..."<<
was the correct answer, and was there first.  I'm sure you agree.

jschristman,  thank you for your Answer, and welcome to EE!

BTW, I ended up deleting the fnCheckUser and put the DLookup directly in the Textbox's Validation Rule like...

"admin" Or (DLookUp("fldUser","tblUser","fldUser = form![txtUser]") Is Not Null)

 ...with the Validation Text:

"The initials you entered are not valid.  Please try again."

 ...with the HandleButtonClick Event assuring that the txtUser can't be bypassed:

(right after the OnError statement)

If IsNull(Forms![Switchboard]![txtUser]) Then
      MsgBox "You must enter a user ID."
      Exit Function
End If

Thank you everyone for your help!

Mac:  While the comment was first, it was only a partially correct answer as it did not cover all situations.

But if it was good enough for you, that's all that matters.

What no points for the constant idea?  :-)

Another one that I fequently use for building SQL strings is gConComma  ",".

I'm well and the tests were benign.

Have a happy set of holidays.  Can't get away from technical based terms, can I?

MacRenaAuthor Commented:
Glad to hear all's well that "ENDs" well (sorry, couldn't resist).

And if you want points for the Constant idea, then get on Linda and Ian and get the "Divide Points" option instituted!

Let's see, in ASCII HEX that would be:

4D 45 52 52 59
43 48 52 49 53 59 4D 40 53
48 40 50 50 59
4E 45 57
59 45 40 52


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.