two records to represent one relationship

[Lynn Trapp]

John,
If you are referring to selecting 205055 via a combo box, then the
BeforeUpdate event of the FORM will be AFTER the selection occurs. The
BeforeUpdate event of the form occurs just prior to updating the record --
most often when you are moving to a new record.

I still think your design is going to produce some rather bizarre results.
It looks like you are trying to do this the hard way. A bill of materials
works as a top down hierarchical structure and trying to make something both
a parent and a child to the same record is not easy to model. While you can
physically enter the values into the tables, I won't make any guarantees
about what results you will get after you start trying to write queries
against this model.

Consider this. If 12345 is a Finished Good then it will be made up of
various elements. While it is possible that a Finished Good may also be made
up of other Finished Goods, there has to be a top level Finished Good
somewhere. It doesn't look to me like you are allowing for that in your
model.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Thanks, Lynn. I think we're getting closer. However, what I'm looking for

is something to happen AFTER I make a selection. For example, if I select
205055 as being associated with 12345 then I need the record of 205055 being
associated with 12345 to be created. It seems to me a simple matter of
having a record duplicating itself in reverse. However, I'm a complete
novice when it comes to building Before/AfterUpdates. This example looks
just like this in tblProfilesAssociations:

 

[Lynn Trapp]

Inline below

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Thanks, Lynn. I may not have been clear. I was saying that this is "like"

a BOM. The function is not to be a BOM but to simply list - in a subform -
the associated profiles with the current profile. Therefore, there is no
true parent/child heirarchy involved. It's simply a table with two fields
that record in the manner I listed below.

I think the problem, from the start, has been lack of clarity. I think the
reason for this is that you insist on using the field names and terms that
YOU know and are accustomed to, but that none of the rest of us are familiar
with. Even though I think I am getting closer to understanding what you
want, I still don't have the foggiest idea what a Profile is and, if you are
not wanting a standard BOM, then I'm quite at a loss to understanding what
you are doing?

Yes - I'm selecting via a combo box and have listed it's name and subform previously:
sfrmProfilesAssociations.cbProfilesAssociations.

I think it might be best to simply disregard EVERYTHING that has been

discussed to this point and start with a clean slate.

Unfortunately, all you have done below is restate what you have said several
other times in this thread and it's not really getting me closer to
understanding, in real world terms, what a Profile is AND, most importantly,
why you feel the compulsion to make something both a Parent and a Child of
the same record. You can do it, but what will it accomplish for you?
Remember, we don't know your business needs or what your business is trying
to accomplish.

I work every day in a very large database that stores Finished Goods and Raw
Materials and, while one Finished Good may be the child of another Finished
Good, which is the child of still another Finished Good, at the bottom of
that chain there are simply Raw Materials that are not the parent of
anything and there is a Finished Good at the top that is not the child of
anything.

Put simply, I do not understand what demands your business requirements
could make to cause you to want a model that would make a model of this
kind. Let me suggest that you throw away the table names and field names for
a minute and tell me in terms that my grandmother can understand what you
are trying to do. Every database needs to model a real world situation and I
cannot, for the life of me, envision yours.

 

[Lynn Trapp]

John,
While it's against my better judgment to tell you how to do this without
first understanding what your business needs are, I am going to post code
that you can modify to make it work. You will have to change the field/form
control names to suit your situation. You will need to add this code to the
On Change event of your Combobox. I can only say that I hope doing this
does not cause you to end up with an endless array of problems. Also, you
may want to add some kind of check to verify that you don't make a record
it's own Parent AND Child. The code below does not deal with that
possibility.

Private Sub cboProfileAssociations_Change()
Dim v_parent_id As String
Dim v_child_id As String
Dim v_Str_SQL As String

v_parent_id =
Forms!frmprofiles.Form!sfrmprofileassociations!cboProfileAssociations
v_child_id = Forms!frmprofiles!txtProfileID

DoCmd.SetWarnings False
v_Str_SQL = "Insert Into tblProfileAssociations Values ("
v_Str_SQL = v_Str_SQL & v_child_id & "," & v_parent_id & ")"
DoCmd.RunSQL (v_Str_SQL)
DoCmd.SetWarnings True

End Sub

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Thanks, Lynn. I may not have been clear. I was saying that this is "like"

a BOM. The function is not to be a BOM but to simply list - in a subform -
the associated profiles with the current profile. Therefore, there is no
true parent/child heirarchy involved. It's simply a table with two fields
that record in the manner I listed below.

Yes - I'm selecting via a combo box and have listed it's name and subform previously:
sfrmProfilesAssociations.cbProfilesAssociations.

I think it might be best to simply disregard EVERYTHING that has been

discussed to this point and start with a clean slate.

OK - I've taken a nice, big, deep breath...

Here are the pertinent parameters to my database :

tblProfiles
txtProfileID (PK)

tblProfilesAssociations
txtProfilesAssociations
txtProfileID
These two are multiple-keys with txtProfileID establishing the relationship with tblProfiles.

sfrmProfilesAssociations
cbProfilesAssociations
Control Source is tblProfilesAssociations.txtProfilesAssociations.

What I can currently do is open parent forms of sfrmProfilesAssociations

and use cbProfilesAssociations to select associated profiles. What I would
like to do is make a selection and have the record automatically update
itself for the selection made. For example, in parent form "FG" with record
12345 open: I select 205055 as an associated profile TO 12345. Upon
selection, 12345 is then recorded as an association TO 205055. The record
would look like this in tblProfiles.Associations: