add new record in sub table based on change on form

[alwayslearning]

My database has a main table called "Projects" with a unique number
"ProjectID" as primary key. Each project has an "Owner". When the owner
changes before the project is complete, I capture that in another table
called OwnerHistory. Each record of this table has, in addition to a
"ProjectID", an owner name and a date of change, etc. The second table is
necessary because of the one to many relationship. I have a data entry form
displaying the current owner from the main table, and also the historical
owners in a subform.

Problem: When the user changes the owner name on the main form using a
combo box control, I want to trigger an event to add a new record to the
OwnerHistory table so the user doesn;t have to enter the information twice.
I've tried going at this both ways - driving an event from the exit of the
control on the main form, and from doing a new entry in the subform. Results
less than pleasing. Help appreciated!

 

[alwayslearning]

ruralguy,

I'm not quite sure what you were wanting me to do. I did keep reading
through other posts on similar issues though, and I finally came up with some
syntax that works, as follows:

Private Sub Ownertxt_Change()

Dim newOwner As String
newOwner = Me![Ownertxt] 'put the desired value into a
variable
Me![HistoricalOwnerssub].SetFocus 'shift to the subform
DoCmd.GoToRecord , , acNewRec 'open a new record in the subform
Me!HistoricalOwnerssub.Form!HOwner = newOwner 'write the value

End Sub

This is triggered by the On Change event. When the user changes the
contents of the combobox on the main form, it opens a new record on the
subform, then writes the new contents of the combobox into the appropriate
place on the subform. The date can be defaulted to now(), and the ProjectID
is taken care of by the parent-child linkage.

Looks simple now, but I kept circling around the right syntax.

Thanks!