Form involving multiple parents and one child

Hi, I would be grateful if someone could help:

I have 4 tables. 3 of these tables (tables A, B, C) have a one to many
relationship with the fourth table (table X).

In each case, table A, B, C form the one side of the relationship and table X
forms the many side of the relationship.

I am confident about setting up a single table (A,B or C) as the main form
and using table X as the subform.

Ideally however , I would like a form that has all three tables (A,B and C)
forming the main form and dictating which records show in the table X subform.


My underlying reason for doing this is so that I can add new records to
tables A, B, C without having to open other forms. Table X would also be able
to use these new records entered in to A, B, & C for its own data.

Advice on the best approach would be much appreciated

RubberStamp

You could do it in a way similar to that which you envisage, but you would
need to use four subforms within a single unbound 'container' form. The
subforms based on tables A, B and C would be straightforward (to save space
each could go on a separate page of a tab control if you wished). The
subform based on table X would be based on a query which references as
parameters the controls on the other three subforms bound to primary keys of
their underlying tables. In the Current event procedures of subform's A, B
and C you would then requery subform X so that it shows only those rows which
reference the rows currently selected in subform's A, B and C.

However, as your aim is to be able to insert rows into the three referenced
tables A,B and C I suspect that you might be able to achieve this using a
more conventional approach. For this you would need just the one form, with
no subforms, basing this on table X. From your description table X appears
to be modelling a ternary (3-way) many-to-many relationship between tables A,
B and C, so would have three foreign key columns referencing the primary keys
of these three tables. An example of a ternary relationship would be one
between Salesperson, Customer and Product, where Sale is the relationship
between the three entity types, each of which it would reference in a
many-to-one relationship (many-to-many relationships are always modelled by
being resolved into a number of many-to-one relationships). By using combo
boxes as the controls bound to these columns in the form you can use its
NotInList event procedures to insert new rows into tables A,B and C.

Exactly how you do this depends on whether you need to insert a value into a
single column in the referenced table. Taking a simple example lets assume
the referenced table is a table Cities with an autonumber CityID column and a
text City column. In your form you'd have a combo box bound to the
corresponding CityID foreign key column, but showing the City text value.
the combo box would be set up as follows:

RowSource: SELECT CityID, City FROM Cities ORDER BY City;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
LimitToList: Yes

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

In the NotInList event procedure of the combo box would be code like this:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

All that's needed to add a new city is to type its name into the combo box
and answer Yes at the prompt.

Lets assume that the Cities table also has a State column. In this case the
combo box on your form would be set up like this:

RowSource: SELECT CityID, City, State FROM Cities ORDER BY City;

BoundColum: 1
ColumnCount: 3
ColumnWidths: 0cm;4cm;4cm
ListWidths: 8cm
LimitToList: Yes

You'd need to experiment with the ColumnWidths and ListWidths dimensions to
get the best fit, but the latter must be the sum of the former, and the first
dimension of the former must be zero.

On your form you'd now put an unbound text box with a ControlSource of:

=cboCity.Column(2)

where cboCity is the name of the combo box. The Column property is
zero-based so this will show the third column, i.e. the state for the
selected city.

In this scenario, when adding a new city you also need to insert a value
into the State column in the new row in the Cities table of course, so you
can't do it simply by typing a city name into the combo box. What you do is
type the name in, and then after you answer Yes at the prompt a form will be
popped up with the city name already in place, ready for you to enter the
state. When you close the form the combo box will be updated. So the code
for the combo box's NotInList event procedure now goes like this:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

where frmCities is the name of a form bound to the Cities table. You then
need to add the following code to the Open event procedure of frmCities to
set the DefaultValue property of its City control to the new city name you
entered into the combo box on your first form:

Private Sub Form_Open(Cancel As Integer)

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

End Sub

For combo boxes which reference the primary keys of other referenced tables
you'd do the same.

Ken Sheridan
Stafford, England

"RubberStamp" wrote:

Hi, I would be grateful if someone could help:

I have 4 tables. 3 of these tables (tables A, B, C) have a one to many
relationship with the fourth table (table X).

In each case, table A, B, C form the one side of the relationship and table X
forms the many side of the relationship.

I am confident about setting up a single table (A,B or C) as the main form
and using table X as the subform.

Ideally however , I would like a form that has all three tables (A,B and C)
forming the main form and dictating which records show in the table X subform.


My underlying reason for doing this is so that I can add new records to
tables A, B, C without having to open other forms. Table X would also be able
to use these new records entered in to A, B, & C for its own data.

Advice on the best approach would be much appreciated

RubberStamp