By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,641 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

Can't create new record in subform

P: n/a
dee
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

Dec 22 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Dec 22 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.