Access doesn't recognize field contents

Posted on 2008-11-03
Last Modified: 2013-11-28
I created a form (via the wizard) that uses a query that pulls data from two tables (Staff and TimeSheets) interrelated by a StaffID field.  I get an error when trying to add a new record to the TimeSheets table.  The message states: The field TimeSheets.StaffID cannot contain a Null value because the required property for this field is set to True.  I can see the value in the field for TimeSheets.StaffID and it isnt Null; all the data is clearly visible on the form.  The StaffID used, is kept in a hidden field on another form thats open when this form displays the data.  

What is wrong and what am I overlooking?  Thank you.
Question by:ejefferson213
    LVL 10

    Expert Comment

    Can you upload a pared-down sample copy of your database? (Remove any confidential data first.)
     It's often *much* quicker to figure out this kind of problem if we can see the whole picture.
    LVL 33

    Expert Comment

    by:Mike Eghtebas
    In relationship window, drag StaffID from staf table to timesheet table (if you have done this already... click on the relationship line to right click and check "Cascade Uodate Related Fields".


    Author Comment

    I'll endevor to create a scaled down DB for your inspection.  Meanwhile, I had previously created a link in the relationships view between Staff and TimeSheets tables.  I made the update Cascade as requested and tried the various join type options all with the same result as before.  

    Author Comment

    To simplify this, I recreated this such that there was only one table, one form (no subforms), one query.  When I go to add a new record to the table, I get the same error even though the StaffID field is filled in.  I set a BeforeUpdate event to display the text field containing the StaffID and it's proper.  This is very confusing.  I set a breakpoint in the BeforeUpdate event to look around but I don't know what to look for.  It's as if Access doesn't see the contents of this one field.  

    Author Comment

    I've attached a scaled down database for your inspection.  Here are the instructions:

      1).  Open database
      2). Open form TimeSheetUseridPassword
      3). In Name box, select EJ
      4). Enter the password abc and click on submit
      5).  Click on Add/Change Times
      6).  Try entering a new time sheet

    Thank you.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    To be blunt...

    Your Database is designed in a nonstandard way.

    Instead of posting you DB and asking us to "Figure it out, then fix it, it might be better to start from the first form and ask if it is designed properly.

    IMHO, all three of these question you have posted goes back to the nonstandard design you have chosen.

    LVL 14

    Accepted Solution

    The problem lies with trying to use a single field to save and calculate data.  Anytime you enter an equals sign into the control source of form control or query field, the result is a calculation that can be displayed, but cannot be saved.

    In Query Design, add StaffID to the list of fields.  Now open that query.  You see that you old records have a value in this field, but it does not get filled in automatically even though you've linked the =Forms...... in a similarly named field.  You can add records to the query only if you type into the required StaffID field.

    If you use the amended query as the basis for a Form, you can
    - use the Form_Load event to set the value of StaffID and not display it to the user
    - use the OpenArgs parameter of the DoCmd.OpenForm statement to pass a value used in Form_Open
    - use the default value property of a control to auto-fill StaffID

    but in each of these cases, you need to have a field in the query whose control source is simply StaffID since it is a required field of the source table.




    Author Closing Comment

    Sorry for the delay; last few days have been very hectic.  

    pteranodon72,  Thank you very much for solving this problem and explaining it to me.  I've been struggling with this for days and trying many different solutions, none of which worked.  Thank you again!!!!!

    Author Comment

    Jeff,  I'd love to know more about why you feel this is non-standard.  I'm not surprised since I'm doing this without any training.  I certainly want to develop something that's reliable and will scale and be easily supported. Perhaps you can point me to some good classes or books from which I can learn.  Thanks for your comments!
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    I always buy the "Access Bible" for every version of Access I have used.
    IMHO, it is the best, general reference book on Access you can buy.
    I have also heard great things about the "Access Step-by-Step" books.

    Of course there are "Targeted" books, but those should come later (VBA Books, Application Design Books)

    The best class you can take is a College level class in Microsoft Access *and* a class on database design.
    These will give you enough time to learn study and absorb the material. You are also tested on your knowledge.
    Stay away form those one or two day "Training" classes.

    On your database, a few observations:

    Standard Security protocol does not recommend "Listing" all user ID's.
    This gives any hacker immediate access to get clues on the passwords.
    Users should "Know" there Usernames.
    You should validate correct usernames in the same way you validate the passwords.
    And it seems like a hacker could simply type in a "New Password" in the box you provided!?

    You should always provide a Form to gather "Parameters" from users.
    Unless they know otherwise, a prompt to: "Enter Month" might result in a user entering:
    Better to make a form with a dropdown for the year and month, so there can be no chance of invalid input.

    You should also adopt a standard naming convention like LNC: (

    Finally, it is always a good idea to ask before you build.
    As I mentioned before, instead of saying:
        "I built this, and it does not work..."
    Instead, before building it, I would ask:
        "What is the best way to design a form, to do..."



    Author Comment


    Thank you very much for your insight.  I'll take some of those design tips back to my application and rework it.  I've been looking for some good courses around me but so far no luck.  I did purchase one of those Bible books and as you said, it's quite thorough and well written!

    Thanks again.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now