Access doesn't recognize field contents

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.
ejefferson213Asked:
Who is Participating?
 
pteranodon72Connect With a Mentor Commented:
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.

HTH,

pT

0
 
calpurniaCommented:
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.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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".

Mike
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
ejefferson213Author Commented:
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.  
0
 
ejefferson213Author Commented:
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.  
0
 
ejefferson213Author Commented:
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.
QueryDebuggingTimeSheets.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
ejefferson213,

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.

JeffCoachman
0
 
ejefferson213Author Commented:
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!!!!!
0
 
ejefferson213Author Commented:
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!
0
 
Jeffrey CoachmanMIS LiasonCommented:
ejefferson213,

Books:
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)

Classes:
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!?
:-O

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:
Feb
February
Febuary
2
2/2008
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..."

;-)

JeffCoachman
0
 
ejefferson213Author Commented:
JeffCoachman,

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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.