Adding New Values to a combo box on the fly

[Guest]

I am trying to add values to my combo box called Patterns on the fly. My
problem is the table of Patterns I am adding to requires both a PatternName
and a Manufacturer as primary fields for each record.

I have been using the code below supplied by Al Campagna.

Private Sub PatternName_NotInList(NewData As String, Response As Integer)
Dim sqlAddPattern As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this Pattern to the list?", vbYesNo)
If UserResponse = vbYes Then
sqlAddPattern = "Insert Into Patterns ([PatternName]) values ('" &
NewData & "')"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

My first field on the form is manufacturer. Its drop down list is fairly
complete, and if not the above code works to add a new manufacturer to the
manufacturers table. Next I tab to the PatternName ComboBox. I get to the
point where it is ready to add the new name to the list, but if I try to
proceed I get a run time error 3058 primary or index field can not contain a
null value. The code line: CurrentDb.Execute sqlAddPattern, dbFailOnError is
highlighted

How can I modify the code so the manufacturer value i selected is also added
by the code?

Thanks so much

 

[Guest]

Lele:

You'll need to insert values into both the PatternName and Manufacturer
fields in the Patterns table. You can get the latter from the Manufacturer
control in the form, and then concatenate it into the string expression for
the SQL statement. You should also first test for the manufacturer control
not being Null:

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

Dim sqlAddPattern As String, strMessage As String
Dim UserResponse As Integer
Dim ctrl as Control

On Error Goto Err_Handler

Set ctrl = Me.ActiveControl

Beep
strMessage = "Do you want to add this Pattern to the list?"
UserResponse = MsgBox(strMessage, vbYesNo+ vbQuestion, "New Pattern")

If UserResponse = vbYes Then Then
If Not IsNull(Me.Manufacturer) then
sqlAddPattern = "INSERT INTO Patterns (PatternName, Manufacturer) "
& _
VALUES (""" & NewData & "","" & Me.Manufacturer & "")"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
strMessage = "A manufacturer must be selected first."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

Exit_Here:
Exit Sub

Err_Handler:
Response = acDataErrContinue
strMessage = Err.Description & " (" & Err.Number & ")"
MsgBox strMessage, vbExclamation, "Error"
Resume Exit_Here

End Sub

I'm assuming Manufacturer is a text field both in the form's underlying
table and in the Patterns table. Note that I've used doubled quotes
characters "" rather than a single quote character ' to represent a literal
quotes character within the string expressions. This guards against the
possibility of a patter or manufacturer's name containing one or more
apostrophes e.g. O'Reilly's Fabrics for the manufacturer or Witches' Brew
for the pattern.

Ken Sheridan
Stafford, England

I am trying to add values to my combo box called Patterns on the fly. My
problem is the table of Patterns I am adding to requires both a PatternName
and a Manufacturer as primary fields for each record.

I have been using the code below supplied by Al Campagna.

Private Sub PatternName_NotInList(NewData As String, Response As Integer)
Dim sqlAddPattern As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this Pattern to the list?", vbYesNo)
If UserResponse = vbYes Then
sqlAddPattern = "Insert Into Patterns ([PatternName]) values ('" &
NewData & "')"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

My first field on the form is manufacturer. Its drop down list is fairly
complete, and if not the above code works to add a new manufacturer to the
manufacturers table. Next I tab to the PatternName ComboBox. I get to the
point where it is ready to add the new name to the list, but if I try to
proceed I get a run time error 3058 primary or index field can not contain a
null value. The code line: CurrentDb.Execute sqlAddPattern, dbFailOnError is
highlighted

How can I modify the code so the manufacturer value i selected is also added
by the code?

Thanks so much

 

[Guest]

Hello Ken,
thanks so much for your help on this. I believe I am very close to making
this work. Unfortunately, I am getting so errors when I try to run the code.



--
Lele

Lele:

You'll need to insert values into both the PatternName and Manufacturer
fields in the Patterns table. You can get the latter from the Manufacturer
control in the form, and then concatenate it into the string expression for
the SQL statement. You should also first test for the manufacturer control
not being Null:

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

Dim sqlAddPattern As String, strMessage As String
Dim UserResponse As Integer
Dim ctrl as Control

On Error Goto Err_Handler

Set ctrl = Me.ActiveControl

Beep
strMessage = "Do you want to add this Pattern to the list?"
UserResponse = MsgBox(strMessage, vbYesNo+ vbQuestion, "New Pattern")

If UserResponse = vbYes Then Then
If Not IsNull(Me.Manufacturer) then
sqlAddPattern = "INSERT INTO Patterns (PatternName, Manufacturer) "
& _
VALUES (""" & NewData & "","" & Me.Manufacturer & "")"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
strMessage = "A manufacturer must be selected first."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

Exit_Here:
Exit Sub

Err_Handler:
Response = acDataErrContinue
strMessage = Err.Description & " (" & Err.Number & ")"
MsgBox strMessage, vbExclamation, "Error"
Resume Exit_Here

End Sub

I'm assuming Manufacturer is a text field both in the form's underlying
table and in the Patterns table. Note that I've used doubled quotes
characters "" rather than a single quote character ' to represent a literal
quotes character within the string expressions. This guards against the
possibility of a patter or manufacturer's name containing one or more
apostrophes e.g. O'Reilly's Fabrics for the manufacturer or Witches' Brew
for the pattern.

Ken Sheridan
Stafford, England

I am trying to add values to my combo box called Patterns on the fly. My
problem is the table of Patterns I am adding to requires both a PatternName
and a Manufacturer as primary fields for each record.

I have been using the code below supplied by Al Campagna.

Private Sub PatternName_NotInList(NewData As String, Response As Integer)
Dim sqlAddPattern As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this Pattern to the list?", vbYesNo)
If UserResponse = vbYes Then
sqlAddPattern = "Insert Into Patterns ([PatternName]) values ('" &
NewData & "')"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

My first field on the form is manufacturer. Its drop down list is fairly
complete, and if not the above code works to add a new manufacturer to the
manufacturers table. Next I tab to the PatternName ComboBox. I get to the
point where it is ready to add the new name to the list, but if I try to
proceed I get a run time error 3058 primary or index field can not contain a
null value. The code line: CurrentDb.Execute sqlAddPattern, dbFailOnError is
highlighted

How can I modify the code so the manufacturer value i selected is also added
by the code?

Thanks so much

 

[Guest]

Thanks so much for your help. i believe I am very close on this. The line

sqlAddPattern = "INSERT INTO Patterns (PatternName, Manufacturer) "
& _
VALUES (""" & NewData & "","" & Me.Manufacturer & "")"

Is causing me a bit of confusion. I think the &_ is just a way of
continuing the same line and is unnecessary to type. Is that correct? Also
when I type the " before the word VALUES I get the error: expected end of
statement. When I eliminate it and type the rest of the code, I get the
runtime error message, Unable to find your field "|"

Thanks again

--
Lele

Lele:

You'll need to insert values into both the PatternName and Manufacturer
fields in the Patterns table. You can get the latter from the Manufacturer
control in the form, and then concatenate it into the string expression for
the SQL statement. You should also first test for the manufacturer control
not being Null:

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

Dim sqlAddPattern As String, strMessage As String
Dim UserResponse As Integer
Dim ctrl as Control

On Error Goto Err_Handler

Set ctrl = Me.ActiveControl

Beep
strMessage = "Do you want to add this Pattern to the list?"
UserResponse = MsgBox(strMessage, vbYesNo+ vbQuestion, "New Pattern")

If UserResponse = vbYes Then Then
If Not IsNull(Me.Manufacturer) then
sqlAddPattern = "INSERT INTO Patterns (PatternName, Manufacturer) "
& _
VALUES (""" & NewData & "","" & Me.Manufacturer & "")"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
strMessage = "A manufacturer must be selected first."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

Exit_Here:
Exit Sub

Err_Handler:
Response = acDataErrContinue
strMessage = Err.Description & " (" & Err.Number & ")"
MsgBox strMessage, vbExclamation, "Error"
Resume Exit_Here

End Sub

I'm assuming Manufacturer is a text field both in the form's underlying
table and in the Patterns table. Note that I've used doubled quotes
characters "" rather than a single quote character ' to represent a literal
quotes character within the string expressions. This guards against the
possibility of a patter or manufacturer's name containing one or more
apostrophes e.g. O'Reilly's Fabrics for the manufacturer or Witches' Brew
for the pattern.

Ken Sheridan
Stafford, England

I am trying to add values to my combo box called Patterns on the fly. My
problem is the table of Patterns I am adding to requires both a PatternName
and a Manufacturer as primary fields for each record.

I have been using the code below supplied by Al Campagna.

Private Sub PatternName_NotInList(NewData As String, Response As Integer)
Dim sqlAddPattern As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this Pattern to the list?", vbYesNo)
If UserResponse = vbYes Then
sqlAddPattern = "Insert Into Patterns ([PatternName]) values ('" &
NewData & "')"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

My first field on the form is manufacturer. Its drop down list is fairly
complete, and if not the above code works to add a new manufacturer to the
manufacturers table. Next I tab to the PatternName ComboBox. I get to the
point where it is ready to add the new name to the list, but if I try to
proceed I get a run time error 3058 primary or index field can not contain a
null value. The code line: CurrentDb.Execute sqlAddPattern, dbFailOnError is
highlighted

How can I modify the code so the manufacturer value i selected is also added
by the code?

Thanks so much

 

[Guest]

Lele:

Mea culpa! The opening quote character is missing from the second line:

sqlAddPattern = "INSERT INTO Patterns (PatternName, Manufacturer) " & _
"VALUES (""" & NewData & "","" & Me.Manufacturer & "")"

This is entered as two lines but is executed as one, so it builds a single
string expression which is assigned to the sqlAddPattern variable. You are
quite right about the underscore character at the end of the first line being
a continuation character, which is used to make code more readable by
avoiding individual lines being too long and requiring you to scroll right to
see the end of the line. If I entered it all as one line here it would wrap
within the message window of course, likes so:

sqlAddPattern = "INSERT INTO Patterns (PatternName, Manufacturer) VALUES
(""" & NewData & "","" & Me.Manufacturer & "")"

but that doesn't happen in the VBA window.

Sorry about the confusion.

Ken Sheridan
Staffprd, England

 

[Guest]

Hello Again Ken,

When I enter the code shown below I get the error message COMPILE ERROR:
EXPECTED END OF STATEMENT . When I click ok, the cursor jumps to the "",""
and the entire line is then shown in the color red.

sqlAddPattern = "INSERT INTO Patterns (PatternName,Manufacturer)" & _
"VALUES (""" & NewData & "","" & Me.Manufacturer & "")"

What am I doing wrong?

Thanks again.
--
Lele

Lele:

Mea culpa! The opening quote character is missing from the second line:

sqlAddPattern = "INSERT INTO Patterns (PatternName, Manufacturer) " & _
"VALUES (""" & NewData & "","" & Me.Manufacturer & "")"

This is entered as two lines but is executed as one, so it builds a single
string expression which is assigned to the sqlAddPattern variable. You are
quite right about the underscore character at the end of the first line being
a continuation character, which is used to make code more readable by
avoiding individual lines being too long and requiring you to scroll right to
see the end of the line. If I entered it all as one line here it would wrap
within the message window of course, likes so:

sqlAddPattern = "INSERT INTO Patterns (PatternName, Manufacturer) VALUES
(""" & NewData & "","" & Me.Manufacturer & "")"

but that doesn't happen in the VBA window.

Sorry about the confusion.

Ken Sheridan
Staffprd, England

 

[Terry Kreft]

The faults with your string are;-
There is a mising space after the ) on the first line
the quotes in the second line are wrong

Assuming PatternName an Manufacturer are both Text fields you should be
using

sqlAddPattern = "INSERT INTO Patterns (PatternName,Manufacturer) " & _
"VALUES (""" & NewData & """,""" & Me.Manufacturer & """)"

Notice how for each " we want embedded in the string ther are actually a
pair of ".

--

Terry Kreft

Hello Again Ken,

When I enter the code shown below I get the error message COMPILE ERROR:
EXPECTED END OF STATEMENT . When I click ok, the cursor jumps to the "",""
and the entire line is then shown in the color red.

sqlAddPattern = "INSERT INTO Patterns (PatternName,Manufacturer)" & _
"VALUES (""" & NewData & "","" & Me.Manufacturer & "")"

What am I doing wrong?

Thanks again.

 

[Guest]

Hello Again Ken,

When I enter the code shown below I get the error message COMPILE ERROR:
EXPECTED END OF STATEMENT . When I click ok, the cursor jumps to the "",""
and the entire line is then shown in the color red.

sqlAddPattern = "INSERT INTO Patterns (PatternName,Manufacturer)" & _
"VALUES (""" & NewData & "","" & Me.Manufacturer & "")"

What am I doing wrong?

Thanks again.

 

[Guest]

Thanks so much for your help, I made the adjustments you suggested and the
code works; however, I do still have a problem. The combo box is on
a form which also has a sub form connected in a one to many relationship.
I need to amend the code so when I add the values to the main form through
the combo box, a new blank record is prepared and ready to recieve infomation
for the sub form. The subForm is called Colors has two fields, an
autoIdNumber and Color. Right now when I run the code, I get an error message

"Your changes were not sucessful because they would create duplicate
values...etc. I believe that what is happening is a new autonumber is not
being created on the subform (as it needs to be) so there is a duplicate
value, a violation and I can not create the new record.

Any help is greatly appreciated.


Lele

 

[Guest]

Thanks for your help on this. I have been using the code you suggested, and
am making progress, but I now have a new problem. The combo box is on
a form which also has a sub form connected in a one to many relationship.
I need to amend the code so when I add the values to the main form through
the combo box, a new blank record is prepared and ready to recieve infomation
for the sub form. The subForm is called Colors has two fields, an
autoIdNumber and Color. Right now when I run the code, I get an error message

"Your changes were not sucessful because they would create duplicate
values...etc. I believe that what is happening is a new autonumber is not
being created on the subform (as it needs to be) so there is a duplicate
value, a violation and I can not create the new record.

Thanks so much.


Lele

 

[Guest]

Lele:

I doubt very much the error has anything to do with the subform. If its
occurring when you try to add a new pattern/manufacturer via the combo box's
NotInList event procedure then it suggests that the Patterns table is indexed
in such a way as to stop you doing so. This could be a unique index on
either the PatternName or Manufacturer columns or jointly on both columns.
In the first two cases it would mean that the pattern or manufacturer already
exists in a row in the table, in the latter case that the combination of the
two exists in a row.

As far as the subform is concerned you don't need to 'prepare a new blank
record'. Records are created simply by entering data into a new row (record)
in the subform, and inserted into the table when you move to another record,
close the form or otherwise explicitly save the record.

I'm puzzled by the fact that the subform has only two columns (fields) the
autonumber primary key and the Color column. What links it to the parent
form? Normally with a subform bound to a table related many-to-one to the
parent table's underlying table the subform's table would have a foreign key
as well as the primary key, and the foreign key would reference the parent
form's table's primary key. Say for example you have a Customers parent form
and an Orders subform, the Orders table would have an OrderID primary key, a
CustomerID foreign key along with columns such as OrderDate etc. The subform
would be linked to the parent form by setting the LinkMasterFields and
LinkChildFields properties of the subform control. With just your two columns
there seems to be no foreign key.

I think it would help if you could explain what your parent form represents
in terms of a real world entity, and what its RecordSource is, and what the
subform represents and what its RecordSource is, and how the two relate to
each other. At the moment I'm having some difficulty visualising the logical
model behind your form/subform.

Ken Sheridan
Stafford, England

 

[Guest]

Hello Ken,
You're right. Sorry about the error. I do have 3 fields in the table that
underlyies the Colors Subform. They are FabricID (autonumber), PatternID and
color. The PatternID field is the foreign field (I think that's what it is
called, I think of it as the many side)

The form portion is based on my patterns table which includes a PatternID
field (autonumber) as well as PatternName, mfgID and ManufacturerName and a
few other bits of relevant info displayed in the top portion of the form.
The colors subform is presented as a continuous form, so if one pattern has 3
colors, they appear as 3 rows.

When I look to determine if a pattern is in the list, I already have an
existing pattern and its corresponding color row(s) visible in the subform.
When I tell the main form combo box I do want to add the new value, the "old"
color rows with their respective fabricID are still in the subform. Even if
I didn't get "bleeped", which I do, this would still be a problem, since the
Fabric colors are different for every fabric.

I hope this makes things somewhat clearer.

Thanks again for your help

 

[Guest]

Lele:

I'm a little confused now, I'm afraid. The original question in this thread
was about using the NotInList event procedure to insert a new row into the
Patterns table, but it now appears that the main form is itself based on the
Patterns table. It sounds to me the form should be based on another table
which models a many-to-many relationship between Patterns and Manufacturers
(i.e. each pattern can be produced by more than one manufacturer, and each
manufacturer can produce more than one pattern. Right?), by having PatternID
and ManufacturerID foreign keys.

The question then is are the colours related to patterns alone, i.e. is the
same pattern available in the same colours from every manufacturer who
produces that pattern? Or are they related to Pattern/Manufacturers i.e.
does each manufacturer who produces a particular pattern produce it in a
range of colours which might differ from the range produced by another
manufacturer?

The answers to the above determine what tables are required, so it might be
that your database needs a more fundamental change than we've been looking at
so far.

Ken Sheridan
Stafford, England

 

[Guest]

Hi Ken,
I am sure it is me rather you who is confused.

You are right I used my many Patterns table when creating the Form I call
PatternsFabrics (Named for the Main Form and sub Form respectively). I
didn't realize this could be a problem.

I wrestled with the Mfger issue! A manufacturer creates a pattern, lets
call it Starshine and then prints it several different colorways. So Mfger A
has StarshineRed, StarshineYellow, StarshineBlue.

I choose to make both the MfgId and the PatternName key fields because it is
possible that MgerB could also come out with a pattern called Starshine,
however, IT WOULD NEVER BE THE SAME PATTERN! Manufacturers try to give the
illusion of exclusivity of their designs although most of the designs come
from China and are sold by different manufacturers under different names.

When Mfger B creates their pattern called starshine, the colorways would
almost certainly be different. So we could have StarshineLemon
StarshineTangerine, StarshineCherry.
There is the slimest possibility that one or even more of the colorways
could be duplicate, but it would be a random event. Again manufactuers are
trying to appear unique with both pattern and color. One thing is for sure,
if mfger B came out with StarshineRed, StarshineYellow, and StarshineBlue.
They would NOT be the same goods the those from MFGer A. Furthermore, this
would be totally by accident and would actually cause some potential
confusion in the marketplace. So obviously it is not desirable.



Thank you so much for any help and ideas you can provide.

 

[Guest]

Lele:

Lets go back to basics and determine what tables we need. A table models an
entity type and its columns represent the attribute types of the entity type.
For an entity type Employees say, attribute types might be FirstName,
LastName, DepartmentID etc. Tables can also model relationship types.
Generally a one-to-many relationship type is not modelled by a table, though
it always can be and in some special circumstances might be; usually we just
create a relationship between the two tables. A many-to-many relationship
type, however, is always modelled by a table. In fact a relationship type is
a special kind of entity type, so its true to say that tables model entity
types. Take an entity type Projects. There is a many-to-many relationship
type between Employees and Projects as each employee can work on one or more
projects, and each project involves one or more employees.

There can also be one-to-one relationship types. These are not commonly
used however. Where they are used is to model a Type/Sub-type, e.g.
Programmers is a sub-type of Employees. We don't need to consider this kind
of relationship type in your case.

So what entity types and relationship types do we have in your case?
Manufacturers is obviously one, so we have a Manufacturers table. Patterns
is also one so we need a table for that (more strictly speaking its
PatternNames, but lets stick with Patterns for now at least). Then there is
an entity type Colours (or Colors if I can risk offending my British spell
checker!). These are the principal tables, what we'd call referenced tables
in the jargon of the database relational model as they (or more correctly
their primary keys) will be referenced by foreign keys in other tables.

Now how do these three tables relate to each other? Firstly we know each
manufacture will produce a number of patterns, and each pattern (or at least
the same named pattern, even though they might differ in the actual design),
and each pattern (i.e. same named pattern) might be produced by several
manufacturers. So we have a many-to-many relationship and consequently need
a table to model it; ManufacturerPetternss say. This table will have two
foreign key columns referencing the primary keys of the Manufacturers and
Patterns tables, ManufacturerID and PatternID. These two columns can form
the composite primary key of the table as the combination of values in them
will always be unique in the table. It may well have other columns which
represent attributes of that pattern as produced by that manufacturer. I
don't know enough about the business to know whether this is in fact the case
or what these might be, however. Its not too important in terms of the
overall logical model.

When we come to Colors these relate not to patterns per se because the
colors for each Pattern(name) are going to be different depending on the
manufacturer, so we need to relate the Colors table to the
ManufacturerPatterns table via another table which models the many-to-many
relationship. Consequently it will have columns ColorID, ManufacturerID and
PatternID. You can actually consider this table as operating in two ways;
(1) It models a 3-way relationship between Colors, Patterns and
Manufacturers, or (2) it models a 2-way relationship between
ManufacturerPatterns and Colors. Lets call this table
ManufacturerPatternColors.

So, with these tables representing our logical model how do we set up forms
for data input. Colors, Patterns and Manufacturers are simple enough, they
just need a form each for inputting data into their columns (incidentally
you'll find I tend to use the more correct terms columns and rows rather than
fields and records; the latter really apply to old style file systems in
which data was stored in fields in sequential records rather than to
relational databases where the data is held in tables).

Where a form/subform are needed is in the case of the ManufacturerPatterns
and ManufacturerPatternColors tables. The parent form would be based on the
former and the subform on the latter. Because these tables are linked by two
columns rather than the usual one the LinkMasterFields and LinkChildFields
properties of the subform control will be the two columns, which are entered
in the controls properties sheet for each property separated by a semi colon,
ManufacturerID;PatternID. The subform just needs a combo box bound to the
ColorID column and listing the colors from the Colors table, as the
ManufacturerID and PatternID values are automatically entered into a new
record by virtue of the link with the parent form.

The main form parent have combo boxes for manufacturer and pattern of
course, bound to the ManufacturerID and PatternID columns. Which brings us
back to where we started; adding values to the lists on the fly. In the case
of these two combo boxes and the colors combo box on the subform the code for
the NotInList event procedures or each is pretty simple. In the case of
manufacturers, as there will be other columns besides the manufacturer's name
(address, phone etc.) you'd need to open a form to insert the new row into
Manufacturers. Whether you'd need to do this in the case of Patterns or
whether you need simply to insert a row with a new name in to patterns
depends on whether there are other columns in patterns than the name. With
colors I'd imagine its just the name, so you'd inset a row into colors
without the need to open another form. I'm assuming that the primary keys of
Manufacturers, Patterns and Colors are all autonumbers, so will be generated
automatically. I'd put this aspect on the back burner for the moment,
though. Concentrate on getting the tables and forms set up. We can come
back to what's needed for the NotInList event procedures later.

I think I'm relatively confusion free now that I understand your business
model better; I hope I haven't confused you too much! You will probably find
it helpful to lay out the tables and relationships diagrammatically on paper
(what's called an entity relationship model). Draw a box for each table with
its column names listed in it and draw lines between then representing each
relationship type and mark which end of the line is the 'many' end and which
the 'one' end. All the direct relationships between the tables are
one-to-many because when you create a table to model a many-to-many
relationship you resolve it into several (usually two) one-to-many
relationships. For this reason these types of table are sometimes called
'resolver' tables. 'Link' tables, 'junction' tables etc are other
descriptions used sometimes, but I'm not very fond of these folksy terms;
they tend to detract from an understanding of the fundamental nature of these
tables as models of relationship types, which, as I said, earlier, are in
essence just a special kind of entity type.

Good luck,

Ken Sheridan
Stafford, England