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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

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.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.