Access doesn't recognize field contents

Posted on 2008-11-03
Medium Priority
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

ID: 22872076
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 34

Expert Comment

by:Mike Eghtebas
ID: 22872813
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

ID: 22872955
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.  
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 22873277
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

ID: 22877856
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
ID: 22883304

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

pteranodon72 earned 2000 total points
ID: 22887390
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

ID: 31512902
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

ID: 22905417
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
ID: 22907414

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: (http://en.wikipedia.org/wiki/Leszynski_naming_convention)

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

ID: 22944277

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

864 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