Foreign Key Won't Enter In Subform

[Traci]

My problem is in a form/subform. On the main form I have an unbound listbox
named OptionsInPlan. In the OnCurrent event of the main form I set the value of
the listbox with the code:
Me!OptionsInPlan.Value = Me!OptionsInPlan.ItemData(0)
For some records there are no rows in the listbox.

I want to have the subform track the value of the listbox so I set the
LinkMaster property to OptionsInPlan and the LinkChild property to ElevationID.
When I make selections in the listbox or navigate with the navigation buttons
the subform tracks the value of the listbox and displays the correct
corresponding records.

My problem lies when I try to add a new record to the subform. I have unbound
textboxes directly above each field in the subform and in the last textbox I use
the AfterUpdate event to execute the Add New method to add the values in the 5
textboxes to the underlying table. When the AfterUpdate event fires, the new
record appears in the subform for a moment and then disappears. When I check the
table, the 5 field values have been added but the foreign key(ElevationID) has
not. I have tried it with and without this code in the Add New method:
Rst!ElevationID = Me.Parent!OptionsInPlan and get the same results.

Can someone help with a solution to this problem?

Thanks!

Traci

[Allen Browne]

Hi Traci

The fact that the record appears briefly and then disappears suggest that
Access is creating the record, but does not think that it matches the
LinkMasterFields/LinkChildFields.

Use the Immediate Window to ask Access what is going on.
While the form is running, and after the new record disappears, press
Ctrl+G. Enter some expressions like:
? Forms!MyForm!MySubformControl.LinkMasterFields
? Forms!MyForm!MySubformControl.LinkChildFields

Also ask it whether the data type is being interpreted correcty, e.g.:
? TypeName(Forms!MyForm!OptionsInPlan)
?
TypeName(Forms!MyForm!MySubformControl.Form![WhateverTheMatchingFieldIsInThe
Subform])

For example, if the bound column of the list box is a Number field, you may
be able to solve the problem by setting the Format property of the list box
to General Number (since that indicates the data type for the unbound
listbox).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Traci" <tm******@earthlink.net> wrote in message
news:zT******************@newsread3.news.atl.earth link.net...

My problem is in a form/subform. On the main form I have an unbound listbox named OptionsInPlan. In the OnCurrent event of the main form I set the value of the listbox with the code:
Me!OptionsInPlan.Value = Me!OptionsInPlan.ItemData(0)
For some records there are no rows in the listbox.

I want to have the subform track the value of the listbox so I set the
LinkMaster property to OptionsInPlan and the LinkChild property to ElevationID. When I make selections in the listbox or navigate with the navigation buttons the subform tracks the value of the listbox and displays the correct
corresponding records.

My problem lies when I try to add a new record to the subform. I have unbound textboxes directly above each field in the subform and in the last textbox I use the AfterUpdate event to execute the Add New method to add the values in the 5 textboxes to the underlying table. When the AfterUpdate event fires, the new record appears in the subform for a moment and then disappears. When I check the table, the 5 field values have been added but the foreign key(ElevationID) has not. I have tried it with and without this code in the Add New method:
Rst!ElevationID = Me.Parent!OptionsInPlan and get the same results.

Can someone help with a solution to this problem?

Thanks!

Traci