Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 29th, 2005, 11:36 AM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access bug or design flaw?

Hey all,

I have been working on an Access database, and tried to follow the 3NF rules and design the database the best way I could but now I am stuck with an error about relatioships, I think. I have an employee table for all my employee information. There are 3 kinds of employees, such as clrek, managers, and staff. Each employee can take many different vacation days. There are different kinds of vacations. There are different vacation hours associated with each vacation and employee type. For example a manager can take a single day for 8 hours and a clrek can take a single day but for 7 hours only. So I have 4 tables:

-------------------------------------------
legend:
table name
+ is for PK
- is for other fields (FK)
-------------------------------------------

employee
+employeeID (number)
-employeeTypeID (number)

employeeType
+employeeTypeID (autonumber)
-employeeTypeName (text)

vacationDate
+vacationID (autonumber)
-employeeID (number)
-vacDate (date)
-vacationTypeID (number)

vacationType
-vacationTypeID (autonumber)
-vacationHours (number)
-employeeTypeID (nubmer)

I'd like to believe that this is a good design in 3NF. You can correct me on that if I'm wrong, but my main problem is with the forms not the tables. In the form where the user is supposed to enter the vacation dates, I have employee table as the source then I have a subform which its source is a query on vacation dates. So far so good, but on the subform, which is a continuous form, I have a control (text or combo box). The problem is that I cannot make new records and I recieve this error:

"The current field must match the join key '?' in the table that serves as the 'one' side of the one-to-many relatioship. Enter a record in the 'one' side table with the desired key value..."

Any response would be appreciated.
Thank you,
Sam
__________________
Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW
 
Old March 29th, 2005, 12:12 PM
ru1 ru1 is offline
Authorized User
 
Join Date: Feb 2005
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sam. It sounds like you do not have your subform related back to your main form correctly. Is the EmployeeID a part of the selection criteria when you were creating the subform on the main form. It should of been. You must have the same relationship's between your subforms as you do in the relationships part of your database or that error will occur. It basicaly is saying, hey you said we were related by all these fields, but I don't have that with my subform set up.

Let me know if this helps.

RU1

 
Old March 29th, 2005, 12:24 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey all,

I solved the problem. I must say though, Access is one of the worst applications I've worked with. Aside from all the performance issues, the forms are frustrating to work with.

I didn't know a field would not be available if it is not on the form as a control, even though it is part of the source of the form you are using. I solved my problem by adding the controls of some of the fields from my source query. By cotnrols I mean textboxes.

If anyone had the same problem, let me know.
Sam

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW
 
Old March 29th, 2005, 12:39 PM
Authorized User
 
Join Date: Sep 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just got your message RU1. Thank you for the quick response.

You are absolutely right. I had the EmployeeID related and also I had it linked as a child/master field. But what I didn't tell you was that I also had another field linked: employeeTypeId. However, this field was not in table relations or joins. But I guess since I added the control to the form, it automatically created a relationship for it. Let me know if I'm getting this right.

Sam

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW





Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP or SQL Server flaw ???? surajkala Classic ASP Databases 5 May 19th, 2008 03:15 AM
Bug or no to bug learning C using VS.Net to compil tesh All Other Wrox Books 0 February 14th, 2007 01:06 PM
Need help with ASP/Access design. wreckless Classic ASP Databases 4 November 16th, 2004 07:30 PM
access design master clueless_may Access 0 October 19th, 2004 09:57 AM
String access by character bug in multi-dimensiona richard.york Pro PHP 1 September 6th, 2003 05:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.