01-30-12, 18:43 #1Registered User
- Join Date
- Jan 2012
Unanswered: Requery form/sub-form after new record entered into subform
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
Set frmMain = Forms![frm_all]
Set sfrm = frmMain![frm_eoisub].Form
hldMainID = frmMain![StudyNo]
hldsfrmID = sfrm![StudyNo]
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!
02-05-12, 17:43 #2Registered User
- Join Date
- May 2004
- New York State
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, addCode:
bMark = Me.BookmarkCode:
Me.Bookmark = bMark
02-06-12, 12:41 #3Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
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)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007