DAO in Access 2003

[martin]

I've created some DAO (including AddNew and Update),
attached to the NotInList event of a combo box in a
subform, to add a record to the table underlying the main
or parent form. It works but the main form won't
refresh, behaving as if the new record isn't there until
I close the form and open it again. I'm sure I've done
something like this before and am wondering if this is an
issue with v.2003.

Any ideas anyone?

 

[Allen Browne]

Did you remember to set the Response argument of the NotInList procedure to
acDataErrAdded?

 

[Guest]

Yes I did - and the new record appears fine in the
combobox in the subform, just not in the main form which
doesn't seem to want to refresh.

 

[Allen Browne]

You have the same set of records in the main form as in the subform? That's
unusual.

The main form has its own records, so you will need to Requery it so it
hears about the new record. That will cause it to reload, and so it goes
back to the first record. To work around that, you have to save the primary
key into a variable before the Requery, and then FindFirst in its
RecordsetClone to return to the desired record again.

There is probably a better solution than having the same records in both
forms (main and sub).

 

[Martin]

You were right - thanks very much for that. (It's a
contacts database that's tracking who recommended whom:
the subform has a combobox listing the people in the
mainform so that I spell names correctly when I type them
but allows me to add new people from there as necessary.)

I used the subform's afterupdate event (the notinlist
event happens too early in the chain) and in fact didn't
need to use the recordsetclone or any DAO for this part:

Private Sub Form_AfterUpdate()
Dim myCurrent As Variant
myCurrent = Me.Parent.Bookmark
Me.Parent.Requery
Me.Parent.Bookmark = myCurrent
End Sub

 

[cristina]

hello, what your name??