I am building an HR application in Access 2000 and I am stuck with a problem. One of the requirements is to track data about employees children. As not all employees have children, I created a tblChildren table where I store the parent's EmployeeID, a ChildID, and the child's name, date of birth and gender. This table is the "many" part of a 0-to-many relation with the tblEmployees table. The referential integrity is enforced.
The EmployeeID is a Long Integer input manually in the main form, while the ChildID is an AutoNumber.
On the main frmEmployee form I have a Tab control. On of the tabs is called Children and contains a subform called sfrmChildren that works in datasheet mode.
The data source for the main frmEmployee form is the tblEmployee table. In the Current event of the main form i set the RecordSource of the subform as:
Me.sfrmChildren.Form.RecordSource = _
"SELECT EmployeeID, ChildID, ChildName, DateOfBirth, Gender FROM frmChildren" :_
& " WHERE EmployeeID=" & Me.EmployeeID.Value
The problem arises when I try to add a new child record for an existing employee: I am unable to initialize the value of the EmployeeID field in the subform. I tried a couple of solutions, but none worked:
1. I bound the EmployeeID from the tblChildren table to a hidden TextBox on the subform. Then in the BeforeUpdate event of the subform I tried to assign a value to the TextBox, but I got an error message saying that in order to assign a value to a Text Box, it must have the focus. Setting the focus didn't work because the TextBox is hidden.
2. I tried to use the DefaultValue property of the EmployeeID TextBox on the subform. The assignement statement in the subform
worked (Access didn't complain), but although in the Help it says that this value takes precedence over a DefaultValue set for a table field, a 0 (the default) is stored as EmployeeID in the tblChildren table instead of the value currently displayed the main form. And of course, this works only when I turn the referential integrity off.
I tried to study the Northwind, Orders, and Solutions sample databases but was unable to figure how they work. (I don't consider myself an Access developer and I am not that much familiar with the Access model. Usually I work in VB, but this time Access is required).
Basically, it is the classical Order/Order Detail situation, but in these sample databases the OrderID is auto generated, while my EmployeeID is entered manually.
Can anyone help? I would appreciate very much any example or a pointer to a source of information.
Thank you for taking the time to read this novel up to this point.