Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2014
    Posts
    3

    Unhappy Unanswered: Search key not found in any record - Access 2007

    Hello all, this is my first post.

    I have created a database in access and I am trying to get it ready to roll out within my department. I am an intern and still learning so please be nice

    I can compile and make an accde file no problem. I can access it fine aswell as others in my office who have access 2007 installed on their machine. But for others who do not have access installed on their machine, the people will actually need to access it. They start it up and they get "search key not found in any record", and when you click Ok it just carrys on and brings up my login screen.

    Ive tried all day to get rid of this message, ive looked at other forums and people have talked about spaces in column headers and i have checked all that and with some few changes its still coming up with the error.

    Has anybody had this problem before and know how I could tackle it?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what code are you running BEFORE the logon screen opens?
    somewhere you are accessing a table. the error message is saying your attempt tot read a row/value from the the table using (I guess a where clause, or possibly a dlookup) failed because it couldn't find a row that matched your criteria.

    because its a logon screen it would be my guess that you are looking up a userid or similar.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2014
    Posts
    3
    I have set access to open on my logon screen, in the form load sub I have this code.

    'Hide Ribbon Bar
    DoCmd.ShowToolbar "Ribbon", acToolbarNo

    'Hide Nav
    DoCmd.NavigateTo "acNavigationCategoryObjectType"
    DoCmd.RunCommand acCmdWindowHide

    'load username into the username textbox on startup
    UserNameWindows
    Me.txtWindowsLogin = CurrentUser
    Me.txtName = DLookup("UserName", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")
    Me.activeaccount = DLookup("Account_Approved", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")
    Me.approved = DLookup("Account_Active", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")

    'Load the users password into the variable for comparison
    MyPass = DLookup("Password", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")

    Me.txtPassword.SetFocus

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    current user is not the same as the windows network logon. in my own opinion its a very very dangerous approach. current user is the name of the logon the user used to open Access. the defautl current user (is 'admin' which gives full admin rights on all objects). if you want the current user use the network API's from Dev Ashish (google:- dev ashish api).

    Im expecting your problem to be here
    Code:
    Me.txtName = DLookup("UserName", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")
    Me.activeaccount = DLookup("Account_Approved", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")
    Me.approved = DLookup("Account_Active", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")
    if dlookup cannot find the specified row it returns null
    so...
    Code:
    Me.txtName = DLookup("UserName", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")
    if isnull(txtName) then msgbog ("didn't find user:" & txtwindowsLogon & " in TBL_Users")
    Me.activeaccount = DLookup("Account_Approved", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")
    Me.approved = DLookup("Account_Active", "TBL_Users", "Windows_Login='" & txtWindowsLogin & "'")

    Although I don't think its an issue here, using 3 dlookups to retrieve a different column from the same row ins't neccesarily a smart call. dlookup carries a performance penalty. you would probably be better using a recordset for this and retrieve all 3 columns at the same time. domain functions are a powerful tool, but can be an issue if you use a lot of the. I don't "know" how they are implemented but I suspect the function opens a recordset, finds the data, then closes a recordset. by opening a recordset by cloning the current project connection you save the overhead of opening and closing a a connection

    if you use the network API call to retrieve the actual network logon then you cna by pass any requirememnt to authenticate a user via passwords.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Brainbox43 View Post

    ...for others who do not have access installed on their machine, the people will actually need to access it...
    Isn't the real issue, here, that even ACCDE files require Access to be installed? For users who do not have Access installed you'll have to install Access Runtime (which, for v2007, is a free download) on each machine and create a Runtime version of the file. Go to 'Help' and simply enter Runtime and the first listing should be

    Introduction to the Access 2007 Developer Extensions and Runtime

    which gives just that, an introduction to using Runtime.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Sep 2014
    Posts
    3

    Found the problem!

    After some more debugging I managed to locate the problem by printing out a sequence of numbers to the screen and it lead me to this line of code.

    DoCmd.NavigateTo "acNavigationCategoryObjectType"

    After commenting out the code and running again it seems to run fine on other machines without a full access licence now.

    Thank you for the help and teaching me a few things.

    The problem is this was originally the previous interns project that he left for me to finish when he left so its always hard going through someone else's code and figuring out the logic behind what they were thinking.

    Can anyone think of why this line of code was throwing me this error?

    Thanks in advance.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looks like a fragment of tossware
    DoCmd.NavigateTo Method (Access) suggests that this limits what the user can see when they open a copy of the db.
    the statement is garbage.

    However that assumes that users can view the access applciation in design mode.. never a good idea. Access applications shoudl be deployed to users as an MDE or ACCDE and if its multi user as a split front end and back end

    giving users design access is not clever, in fact it removes any vestige of security you may think you have included. if any of the code is reliant on security then you must not allow users to view code or objects.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •