On 21 Dec 2006 17:37:02 -0800, "dee"
<pr******************@comcast.netwrote:
You probably mean the AfterUpdate event.
Your db design probably includes at least 3 tables: Leads, Salesreps,
and SalesRepsOnLeads (LeadID, SalesrepID). We call this a many-to-many
relationship. Draw the relations in the relationship window and
enforce them. The SalesRepsOnLeads table is the one to insert a row
into.
You can do this without code. Make the subform's recordsource
SalesRepsOnLeads. Now you have 2 columns with numbers. Hide the LeadID
(this is the LinkChildFields). Then replace the SalesRepID with a
dropdown (RowSource = query like this: select SalesRepID, SalesRepName
from Salesreps), and keep the ControlSource as SalesRepID.
Then you can just go to a new line in the subform, drop down the
salesrep list, and select the new guy. Access will automatically
insert a new row in tblSalesrepsOnLeads.
This is a very powerful technique and the default way to handle
many-to-many situations. Only deviate from this if you REALLY know
what you're doing, and why. The Northwind sample application's Orders
form is another example of this implementation (M:M between orders and
products via orderdetails)
-Tom.
>I have a main form "FmLeads".
It contains a field "Salesman" which has an 'After Change' Event
Procedure.
The Main form "FmLeads", contains a subform "FmDispoHistory". It
displays all historic Salesman changes in continuous form format.
It is related to with ONE "FmLeads" record -->MANY "FmDispoHistory"
records via an ID field.
I need the Salesman 'After Change' Event Procedure on the main form to
add a new record to the "FmDispoHistory" subform, and then to assign
the value of the "Salesman" field from the main form to the "Salesman"
field in the subform.
Could someone help me with a few lines of VB code that can do tjhis?
Thank You