Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Unhappy Unanswered: Adding a linked record in a subform

    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

    Me.EmployeeID.DefaultValue = Forms!frmEmployee!EmployeeID.Value

    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.

  2. #2
    Join Date
    Aug 2003

    Talking Re: Adding a linked record in a subform

    I solved the problem. The LinkMaster and LinkChild properties of the subform were not set properly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts