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)
Thanks,
Mac
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)
Thanks,
Mac
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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","tblUse
...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]
MsgBox "You must enter a user ID."
Forms![Switchboard]![txtUs
Exit Function
End If
Thank you everyone for your help!
Mac
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","tblUse
...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]
MsgBox "You must enter a user ID."
Forms![Switchboard]
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?
Jim
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?
Jim
ASKER
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
26
48 40 50 50 59
4E 45 57
59 45 40 52
:>)
Mac
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
26
48 40 50 50 59
4E 45 57
59 45 40 52
:>)
Mac
:-(
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.
Jim