As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

I broke something in my Access database and don't know how to fix it.

ElJeffeElJeffe Moderator, ClubPA mod
So I have a database. In, you know, Access. It consists of a whole bunch of linked tables, and was created long ago by someone who was not me. I recently changed the tab-through order in a form (also not made by me) to properly reflect a couple of fields I had added to the middle of the form.

There is a field on the form which is a search field - you type in a number, hit enter, and it searches a field called ID_Number for that number and brings up that record. It is kind of any important function, and it recently stopped working. If you type in a number and hit enter, it now does nothing. You can open a drop-down list that shows all ID_Number values that exist in the relevant table, but if you select one, it still doesn't bring up the proper record.

Now, I have back-up versions of the database from back when this field still functioned properly. I add/change data in this thing frequently, and reverting to an old version isn't really an option I want to consider, but I need to make this thing work. I've tried going into Design View and checking the properties in the current version versus a back-up version, and every single value is identical. I also looked at the properties for the ID_Number field in the same form (since that's what it's referencing) in the current version versus the back-up, and all that stuff is the same, as well. I need to either figure out a way to fix the field so searching is possible, or transplant the feature from the old, functional version to the new version.

I'm not really that proficient in Access. I can make simple databases from scratch, and I can do rudimentary things like design and tweak forms and queries, but nothing fancy. Any ideas here?

I submitted an entry to Lego Ideas, and if 10,000 people support me, it'll be turned into an actual Lego set!If you'd like to see and support my submission, follow this link.

Posts

  • DarkewolfeDarkewolfe Registered User regular
    I'm trying to envision the exact problem you're describing, but it sounds like you broke the linkage for the query. Take a look at the SQL for the query itself and see if it's calling for a table that you've renamed?

    What is this I don't even.
  • AnhierdAnhierd Registered User new member
    edited April 2013
    Can you tell if all the linked tables are still linked correctly in the new version?

    Can you tell if the databases are using VBA on their forms to perform the actions. Could any of it have been deleted?

    When you changed the tab-through order did you accidently change the name of any of the objects placed on the form?

    Can the databases be cleansed of data and IP so that they could be looked at?

    Anhierd on
  • ElJeffeElJeffe Moderator, ClubPA mod
    Okay, I had apparently renamed the search field, because it was called "Combo345" and I wanted to give it a name that made sense. (The guy who made the database didn't use descriptive names for anything, so everything is "Comboxxx" and "Labelyyy".) I guess that when I renamed the field, the code written for the form itself got confused. I changed the name back to the old name, and now everything works fine.

    Is there a way to rename something in such a way that it can find references to that item and auto-update them? Because now I'm scared to ever rename anything sensible, because it might break something somewhere.

    I submitted an entry to Lego Ideas, and if 10,000 people support me, it'll be turned into an actual Lego set!If you'd like to see and support my submission, follow this link.
  • DarkewolfeDarkewolfe Registered User regular
    edited April 2013
    No, I'm pretty sure that there's not a way in Access to force an update across all queries using that table's name. You'd have to figure out where all the calls are and update them yourself.

    The problem here is that Access is mostly just a GUI for the SQL database, and it's not the best GUI in the world. It's just more user friendly and functions well with other MS products.

    Darkewolfe on
    What is this I don't even.
  • JihadJesusJihadJesus Registered User regular
    Darkewolfe wrote: »
    No, I'm pretty sure that there's not a way in Access to force an update across all queries using that table's name. You'd have to figure out where all the calls are and update them yourself.

    The problem here is that Access is mostly just a GUI for the SQL database, and it's not the best GUI in the world. It's just more user friendly and functions well with other MS products.

    Depends. If its running the search using VBA code, it's probably explicitly referencing the name of the control to pass the value, which wouldn't update. If it's just a bound lookup combo box created with the standard access wizard tool, it should be fine to rename it. VBA references tend not to update, but anything you did just within the GUI should be okay.

  • DarkewolfeDarkewolfe Registered User regular
    JihadJesus wrote: »
    Darkewolfe wrote: »
    No, I'm pretty sure that there's not a way in Access to force an update across all queries using that table's name. You'd have to figure out where all the calls are and update them yourself.

    The problem here is that Access is mostly just a GUI for the SQL database, and it's not the best GUI in the world. It's just more user friendly and functions well with other MS products.

    Depends. If its running the search using VBA code, it's probably explicitly referencing the name of the control to pass the value, which wouldn't update. If it's just a bound lookup combo box created with the standard access wizard tool, it should be fine to rename it. VBA references tend not to update, but anything you did just within the GUI should be okay.

    Hmm. This isn't true for me, but maybe I'm on a different version of Access. If I change the name of a table, even queries made with the tool break.

    What is this I don't even.
  • DiannaoChongDiannaoChong Registered User regular
    ElJeffe wrote: »
    Okay, I had apparently renamed the search field, because it was called "Combo345" and I wanted to give it a name that made sense. (The guy who made the database didn't use descriptive names for anything, so everything is "Comboxxx" and "Labelyyy".) I guess that when I renamed the field, the code written for the form itself got confused. I changed the name back to the old name, and now everything works fine.

    Is there a way to rename something in such a way that it can find references to that item and auto-update them? Because now I'm scared to ever rename anything sensible, because it might break something somewhere.

    Er, I don't know if you are experienced with coding, or just VBA...

    edit: TL;DR theres too many things it could be, which range from "simple" to "find/replace" to "rewrite it"
    No, and welcome to programming hell. :( Best thing to do is jump into design mode for the button, figure out what references did what (do you have a backup copy of the front end you could reference? might make it easier) and change the code to point to the new objects, or put the objects back. Sorry you either got someones either emergency/sloppy work.

    If you have no backup for the GUI, I really hope you didn't change much for the control's names. You might be stuck rewriting the commands that reference your stuff, or the whole front end.

    ALTHOUGH if your function for the search (did you rename the button that "fires" it?) got renamed only, it may be a thing of fixing the reference. If you can 'view code' on whatever fires the event, and theres no code there? It could be as simple as copying and pasting the code from the old call into the new one. But it sounds alot nastier than that to be honest.

    Besides all of the above, if you know the old reference in comparison to the new reference, you can do find/replace, but that can make a bigger mess.

    steam_sig.png
  • schussschuss Registered User regular
    ElJeffe wrote: »
    Okay, I had apparently renamed the search field, because it was called "Combo345" and I wanted to give it a name that made sense. (The guy who made the database didn't use descriptive names for anything, so everything is "Comboxxx" and "Labelyyy".) I guess that when I renamed the field, the code written for the form itself got confused. I changed the name back to the old name, and now everything works fine.

    Is there a way to rename something in such a way that it can find references to that item and auto-update them? Because now I'm scared to ever rename anything sensible, because it might break something somewhere.

    You're talking about every DBA/reporting person's dream.

    While you can theoretically do a find and replace, that may cause more issues than it solves. Your best bet is to document all the current fields and remind yourself that you will never do this on anything you create.

  • SpongeCakeSpongeCake Registered User regular
    Fucking Access man.

    Fucking. Access.

  • schussschuss Registered User regular
    SpongeCake wrote: »
    Fucking Access man.

    Fucking. Access.

    Seriously. I wish I could wash it from the face of the earth.

Sign In or Register to comment.