ejefferson213
asked on
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.
What is wrong and what am I overlooking? Thank you.
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
Mike
ASKER
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.
ASKER
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.
ASKER
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!!!!!
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!!!!!
ASKER
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!
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
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
ASKER
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.
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.
It's often *much* quicker to figure out this kind of problem if we can see the whole picture.