Creating new records, RTE: "You can't assign a value to this object"

[RA99]

Hi there:

I'll try to simplify the context of my problem as much as possible, noting I'm very green at VBA.

In my application I have individuals from a contact universe who may be classified as Members. They must be Members to qualify to be a Programmer, and thus be assigned to a Program. So, Programmers is a sub-set of Members, and because Programs can have more than one Programmer and Programmers can be involved in more than one program, Programs and Programmers are linked by a junction table, ProgramRoles, containing only its own primary key and the foreign keys from the other two tables.

I have set the db up so the Program is operationally primary since Program requires the most information. I have a form where a query of Program is on the main form and a query of Programmers on the subform, i.e. showing Programmers by Program. On the subform, I am trying to provide an automated option for the user to select from Members, create a Programmer record, and create a Program Role record to complete the relationship.

So, I've created a combo box on a query of Members with the MemberID primary key as the first column along with name fields so that any active member may be selected. On the combo box's AfterUpdate event I have the following code:

Private Sub Combo44_AfterUpdate()

Dim PassProgmrID As Integer
Dim PassProgID As Integer

DoCmd.OpenTable "ProgrammersT", acViewNormal, acAdd
Me.MemberID = Me![Combo44].Column(1)    ** error here **
PassProgmrID = Me.ProgmrID
PassProgID = Me.ProgID
DoCmd.Close acDefault, "ProgrammersT", acSaveYes

DoCmd.OpenTable "ProgramRoleT", acViewNormal, acAdd
Me.[ProgmrID] = PassProgmrID
Me.[ProgID] = PassProgID
DoCmd.Close acDefault, "ProgramRoleT", acSaveYes

Me.Requery

End Sub

Although the first part of this code (on the Programmers file) ran successfully once, I am now getting the run-time error "You can't assign a value to this object" at the marked line. Hopefully this is something obvious to a real VBA coder, but for the moment I'm stuck.

[June7]

The subform should be bound to the junction table, then have a combobox to select programmers.

Entering new programmers must be done on an independent form. Adding new lookup records 'on-the-fly' is what the combobox NotInList event is for. http://www.blueclaw-db.com/access_no...ed_example.htm

[RA99]

Thanks for that. I can't (I don't think) use the NotInList facility as I can't think of a way to restrict the user's choices to current Members. So, I'm still working my way through my problem.

So far, I have my main form pointing to a query of Programs, and a subform bound to the ProgramRoles junction table. The SQL for the subform actually looks like this:

SELECT ProgramRoleT.*,  ProgrammersT.MemberID, MembersT.MasterID, ContactMasterT.LastName,  ContactMasterT.FirstName, ContactMasterT.MiddleName
FROM  ((ContactMasterT INNER JOIN MembersT ON ContactMasterT.MasterID =  MembersT.MasterID) INNER JOIN ProgrammersT ON MembersT.MemberID =  ProgrammersT.MemberID) INNER JOIN ProgramRoleT ON ProgrammersT.ProgmrID =  ProgramRoleT.ProgmrID;

The complexity in that comes from the need to see identifying fields from tables other than the junction table.

A combo box on the footer of the subform accesses records from the Members table, and the following code is attached to the AfterUpdate event of the combo box:

Private Sub Combo44_AfterUpdate()

DoCmd.Hourglass True
DoCmd.OpenForm "ProgrammerNewF2"
Me.[MemberID] = Me![Combo44].Column(0)
DoCmd.Close acForm, "ProgrammerNewF2", acSave
DoCmd.Hourglass False

End Sub

The ProgrammerNew form in this code is bound to the Programmers table, and contains controls for ProgrammerID (pk), MemberID (fk) and an active flag (text). The form is set to Data Entry.

This setup appears to populate the subform with a new programmer associated with the correct Program. However, it fails to save the change, indicating that I am trying to introduce a duplicate to a primary key field or indexed.

I've checked my three tables (Programs, Programmers, ProgramRoles) to ensure only the primary keys are indexed. I've tried populating the ProgrammerNew form manually, and that form appears to do what was intended, i.e. it creates a new Programmer record with a new ProgrammerID, and this entry appears on the main form/subform where I would expect it. However, it will not let me save from the ProgrammerNew form or the main/subform combination. The error message to a manual save says that the changes "were not successful because they would create duplicate values in the index, primary key or relationship." The message from the AfterUpdate code is similar.

[June7]

I do not understand your aversion to the NotInList event. The NotInList event code is a vehicle to add new 'current' member.

Don't use INNER JOIN - this requires related records in all table for any to show on the form. Otherwise, don't include the other tables. Show identifying fields as columns in combobox.

The code is not populating MemberID on ProgrammerNewF2 form - it is referencing the MemberID field on the first form.

Put code in the ProgrammerNewF2 form Current event to set MemberID.

If IsNull(Me!MemberID) Then Me!MemberID = Forms!firstform!MemberID

or pass MemberID to ProgrammerNewF2 with OpenArgs:

DoCmd.OpenForm "ProgrammerNewF2", , , , , , Me!MemberID

Then code in ProgrammerNewF2:

If IsNull(Me!MemberID) Then Me!MemberID = Me.OpenArgs

[RA99]

The reason I was trying to blend descriptor data into the junction table subform is that I am trying to display current Program/Programmer relationships on the form, as well as add capacity to choose an existing Member as a new Programmer and associate that Programmer with the current Program on the form.

To try to capture your advice to get rid of the inner joins, I left the subform that displays the current relationships intact, and moved the new Programmer process to a second subform bound only to ProgramRoles, and set to Data Entry. The combo box on the new subform displays records from Members table. I've put an unbound control called MemberID on the subform and passed the MemberID value from the selected Member from the combo box to that control. This is my AfterUpdate code for the combo box:

Private Sub Combo13_AfterUpdate()

Me.MemberID = Me!Combo13.Column(0)
DoCmd.Hourglass True
DoCmd.OpenForm "ProgrammerNewF2"
DoCmd.Close acForm, "ProgrammerNewF2", acSave
DoCmd.Hourglass False

End Sub

The OnCurrent event code for the ProgrammerNew Form is:

Private Sub Form_Current()
If IsNull(Me!MemberID) Then Me!MemberID.Value = Forms!ProgramViewF!ProgramViewSub2!MemberID
If IsNull(Me!ProgmrActive) Then Me!ProgmrActive = "Active"
End Sub

This setup is correctly creating the new Programmer record, so I've made progress in that regard. But it is not creating a new record in the junction table, ProgramRoles. I am obviously still missing something.

[June7]

So am I. Think I need to analyse db. If you want to provide, follow instructions at bottom of my post.

[RA99]

So am I. Think I need to analyse db. If you want to provide, follow instructions at bottom of my post.

Pleased to provide db, attached. The part I am working on is ProgramViewF and related forms. You will probably shift by it as shift bypass is not yet disabled, but your password is Maestro, pw Access99.

[RA99]

Well, I've eliminated one false theory. I realized I still had some data fields in my junction table other than primary and two foreign keys. I've moved those off to a new table so the junction table is actually 'clean', but I'm still not creating the junction record when I add a new programmer. Could I be creating some confusion about the relationship between the second subform and the main form?

R.

[John_G]

Hi -

Going back to your original post:

It looks as if you want to add a new record to the table "ProgrammersT", is that correct?

If so, that's not the correct way of doing it. You open the table, but then you do this:

Me.MemberID = Me![Combo44].Column(1) ** error here **

The Me. I have highlighted in red does not refer to the table you opened, it refers to the current form.

Far better would be to use a recordset to add the records:

Private Sub Combo44_AfterUpdate()  
Dim PassProgmrID As Integer 
Dim PassProgID As Integer
Dim rst as RecordSet

set rst = currentdb.openrecordset("ProgrammersT")
rst.AddNew
  rst!MemberID = Me![Combo44].Column(1)    ** error here **
  rst!PassProgmrID = Me.ProgmrID
  rst!PassProgID = Me.ProgID
rst.Update
rst.Close

set rst = currentdb.openrecordset("ProgramRoleT")
rst.Addnew
  rst![ProgmrID] = PassProgmrID
  rst![ProgID] = PassProgID
rst.Update
rst.Close
Me.Requery
End Sub

If I have misunderstood what you wanted do, then what was the purpose of the OpenTable statements?

John