Return to Record on Form After Save/Requery 1

[Jill50]

I have looked at several threads dealing with this issue, but I am still confused. I have a form based on a list of names sorted by last name, first name. When a new name is entered and saved (I have a function triggered by the save button that increments the ID value), what is the best way to requery in order to put the new name in the correct alphabetical order but have the form remain on the record regardless of what record number it ends up to be after the sort?
Thanks in advance for any help!!

 

[TheAceMan1]

How are ya Jill50 . . . .

1) Hold the ID in a variable.

2) Requery the form (this resorts proper).

3) Through a RecordsetClone, search for the ID above and
set the Form Bookmark to The RecordsetClone Bookmark. This makes the record with ID above the current record.

If you need code or a better explanation, let me know.

TheAceMan

 

[pdldavis]

Hi, an easy way to do that is to display your names in an unbound subform set to invisible. Press a button to display the subform and add your names.

Create a button on the main form using the wizard to refresh the form data, which then turns the subform back to invisible (cosmetics).

The names will appear correctly sorted in your pick list on your main form. if that is what you are using.

Hope that helps.

 

[Jill50]

Thank you both for your quick replies! TheAceMan, I have tried what you are suggesting, but I am obviously missing something, so some sample code would be great if you could!
Thanks!

 

[TheAceMan1]

OK Jill50 . . . .

Enter the following line at the top of your save routine:
Dim rst as Recordset, hldID as Long

Enter the following lines at a point in your save routine where you know the ID can be caputred.:

hldID=Me!YourIDName
Me.Requery
Set rst=Me.RecordsetClone

rst.FindFirst "YourIDName =" & hldID
Me.Bookmark=rst.Bookmark

Enter the following line at the end of your routine:
set rst=Nothing



TheAceMan