Hi there,

I have a study database where we are recruiting study participants, and using Access to manage all their details. I have a tabbed frm, where there is the main form (frm_all) and 5 tabs, one of which contains a sub-form (frm_eoisub). On this particular tab is where the details of new participants are entered. After entering the details of the new person I need to save the record and then requery the whole form but leaving focus where it is. The main form then populates with the participants Study Number, date of birth and name.

I have been successful in using a simple macro (Save, Requery), however this always moves focus back to the first record, which is most annoying.

I've tried numerous other codes from other forums, but have hasd no success. The closest I got was:

Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As
Long
Set frmMain = Forms![frm_all]
Set sfrm = frmMain![frm_eoisub].Form
hldMainID = frmMain![StudyNo]
hldsfrmID = sfrm![StudyNo]

frmMain.Requery
frmMain.Recordset.findFirst "[StudyNo] = " & hldMainID
sfrm.Recordset.FindFirst "[StudyNo] = " & hldsfrmID

This code will save and keep focus for existing records, but doesn't work for new records as the Main form doesn't yet have a Study Number for the participant.

Any help would be greatly appreciated!

I admit to be ignorant of tabbed forms, but why don't you try a bookmark? Simply DIM a variable, say bMark as Variant, and when you're ready to requery, add After requerying, add to return to the correct record.

Sam

Two problems here:

First off, you appear to be entering a Child Record before the Parent Record exists.

Secondly, you're apparently committing multiple Fields to multiple Tables, a huge no no in Access!

Data for an individual, such as Study Number, date of birth and name, would normally be stored in the Table that the Main Form is based on, and in any dependent Tables only the unique identifier, which I'm assuming in this case is Study Number, would be stored.

Linq ;0)>