Access 2007: Form problem, 'You can't save this record at this time'

Posted on 2011-04-26
Last Modified: 2012-06-21
Good morning, I have been trying to make a database here with the help of some experts and have run into a dilly of a pickle. I'm attaching the form to this entry so that interested persons can look at the problem if I am unable to explain clearly enough.  The form controls of frmPtCharacteristics work fine until you attempt to add new data and then everything stops working.  Entries can't be created, I am unable to search between records with the combo box, and i get an error prompt when I move to the next form because it tries to close the original form and can not save it.
I have a form whose underlying table is related to several others in a one-to-many fashion with updated fields and join property 2 (include all records from 'one' table in many table).  
When I enter data the form associated with this table, and try to save a record I get the error 'You can't save this record at this time'.  I'm not sure what to do about this. The data entered IS saved, but then everything just seems to stop working.  Can someone please take a look at this, I'm stumped.

Thanks for the help,
Bevo S.

Question by:Bevos
    LVL 24

    Accepted Solution


    I strongly hope that this product was not invented with the help of experts...:-)

    I would say, this "database" is a good example where you can find any problem which usually can be found in all kind of Access forums.

    First thing is that it is based on macros. If you not plan to create a web database with Access 2010 you should immediately throw away all macros and start program with VBA. It's not really harder than this, but you can debug it - macros cannot be debugged (not in Access 2007).
    I could solve your problem by converting the embedded macro as saved macro, after this little change it worked... but that doesn't mean the rest is OK.

    You used multi-valued fields - they produce problems if you try to query them.
    You used lookup fields on table level - they hide the underlying value if it would be an ID. But: You didn't use an ID although you have lookup tables (which is normally the right way) - all lookup tables should have an ID field as primary key (PK) - none of your lookup tables have a primary key or ID, you copy all the values to the main tables and produce a lot of redundant data. The database will be very slow in the end and has nothing to do with a relational database.
    You can enter anything in the most fields - no validation. The search combobox is not refreshed after saving a record. The new record button has the same text as tooltip. Form objects have no meaningful name like "Command104", "Check180". Table and field names have spaces.

    That are only the errors I saw with a quick view on some elements. I would say this "database" needs a complete redesign and a lot of learning about database design from you...



    Author Comment

    Thanks for the unsolicited critique.


    Author Comment

    In seriousness, I recognize these shortcomings already.  Changing the macros to VBA solved the error thought and I  begrudgingly offer you full points for your disparaging answer.
    LVL 24

    Expert Comment

    Hi Bevo,

    sorry, it was not meant to be disparaging - it was meant as accelerating incentive to you to not try to go on working on this way because you will get in trouble not at the current point but in future when you try to make more of this. It is very much easier to change the basics at the beginning and then go on developing, if you try to change these things in a further point of development you will have a lot more to do with it.
    Unfortunately it is also the fault of Microsoft and Access that it simply allows to create such things and sell it as "features" (like multi-valued fields or lookup fields). It would be very much easier if they had implemented a multi-value frontend possibility with a normal lookup table for example or create a normal lookup table with a wizard instead of lookup fields on table level and so on.

    Hope you feel not demotivated to go on developing databases.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now