Requery problem

[Pyro]

Hiyas,

I am trying to requery an unbound combo on a subform based on a record selection on another subform (default view continuous forms).

The unbound combo has this code running in it:

Private Sub N_Subject_afterUpdate()
'Move to the record selected in the control
Me.RecordsetClone.FindFirst "[NotesID] = " & Me![N_Subject]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The subform (which is set to continuous forms) where i am selecting the record is running this:

Private Sub Detail_Click()
Forms![frm_Customer_Details]![sfrm_Quote_note].Form![N_Subject].Requery
Forms![frm_Customer_Details]![sfrm_Quote_note].Form![N_Subject] = Forms![frm_Customer_Details]![sfrm_Quote_note].Form![N_Subject].ItemData(0)
End Sub
 
Private Sub Form_Click()
Forms![frm_Customer_Details]![sfrm_Quote_note].Form![N_Subject].Requery
Forms![frm_Customer_Details]![sfrm_Quote_note].Form![N_Subject] = Forms![frm_Customer_Details]![sfrm_Quote_note].Form![N_Subject].ItemData(0)
End Sub

When i select a row on the unbound combo that is below row one, a memo field is updated with the corresponding record, the code fails when i select a new record in the other subform. The memo field displays data from the same row as the previously selected record. i.e. it is not being requeried to display the first record again...

Any thoughts?

 

[Galaxiom]

I'm unable to quite get my head around what you are doing. And probably also swimming beyond my depth. But since nobody else tried......

You appear to have a field and a combobox with the same name [N_Subject] ? This does make your code hard to read and introduces a lot of room for confusion. Access will tell you if it can't find a field or control but can happily return the unintended value of something with that name if you happen to get the syntax wrong. You could easily be misled.

You appear to be referring only to the form's Record Source's fields in the second code box.
Does .Form![N_Subject].ItemData(0) mean to refer to the first item from a lookup in the N_Subject field of the RecordSource of the subform control [sfrm_Quote_note]?

Or did you mean to refer to the combobox? If so drop the .Form term.

Otherwise there is a wild stab in the dark.

Try using the DoCmd.Requery method to requery the control with the problem.
This reloads the query rather than just rerunning it as is the case with the Requery method of the control or form.

Someone posted this link here last week and it sounds like it might be your solution since you get a problem with new records.

http://msdn.microsoft.com/en-us/library/aa203946(office.10).aspx