Form that populates multiple tables? Please help...

[allie357]

I am very lost at this point.
I have a entry form with new entries to enter new policy violations.
There are two related tables Employees and Violations involved in this
problem.

The entry form has the Violator's last name, first name, date entered,
violator's dept no, policy violated, amount, and description fields on
it.

I have been trying to use these links to do something similar....
http://www.databasedev.co.uk/not_in_list.html
http://www.databasedev.co.uk/downloads.html

Ideally, I would like Violator's Last Name to be a drop down box that
would list the contents of the Employees Last Name field. When the user
chooses a last name from the list it would populate the Violator's
First Name field. If the user enters a name not in the drop down list
I want them to be able to add a new one so it is added to the Employee
tables and the foreign key is updated in the Violations table. Is this
possible? I tried to follow the example above and I get the error
Control can't be edited it's bound to the Violator_ID in the Employee
table. Any help is appreciated.

 

[Ken Sheridan]

Firstly the Violations table should contain a ViolatorID foreign key field
referencing the primary key (EmployeeID say) of the Employees table. It
should not have fields for the FirstName, LastName or the violator's DeptNo,
which should only be in the Employees table. The form should be bound to the
Violations table and the combo box bound to the ViolatorID foreign key field
in that table. Its RowSource should be something like:

SELECT EmployeeID, LastName, FirstName, DeptNo
FROM Employees
ORDER BY LastName,FirstName;

The combo box's BoundColumn property should be 1, its ColumnCount property 4
and its ColumnWidth something like:

0cm;3cm;3cm;2cm

Or rough equivalent in inches, but the first dimension must be zero. Its
ListWidth property should be the sum of the ColumnWidths, 8cm in the above
case.

To show the FirstName, and DeptNo values on the form have two unbound text
boxes with ControlSource properties of:

=cboViolatorID.Column(2)
and
=cboViolatorID.Column(3)

where cboViolatorID is the name of the combo box. The Column property is
zero-based, so these refer to the third and fourth columns of the RowSource,
i.e., the FirstName and DeptNo columns.

To add a new name to the list you have to insert a row into the Employees
table. The simplest way around this is to use something like the DblClick
event procedure of the combo box to open a form bound to the Employees table
in dialog mode and then requery the combo box after the form is closed, e.g.

DoCmd.OpenForm "frmEmployees", _
DataMode:=acFormAdd, _
WindowMode:=acDialog

Me.cboViolator_ID.Requery

An alternative method is to use the combo box's NotInList event procedure so
that a name can be typed into the combo box, with code like this:

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new employee to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmEmployees", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure employee has been added
If Not IsNull(DLookup("EmployeeID", "Employees", "LastName = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Employees table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

In the frmEmployees form's Open event procedure you'd put:

If Not IsNull(Me.OpenArgs) Then
Me.LastName.DefaultValue = """" & Me.OpenArgs & """"
End If

The problem with this approach, however, is that while it works well with
unique values, names can be duplicated (I worked with two Maggie Taylors
once), so it won't let you add a new employee with the same last name as an
existing employee. I'd recommend the first method, double clicking to open
the form.

Ken Sheridan
Stafford, England