check subform for records
in [Forms]
|
From: johnlute on 6 Apr 2010 09:48 Access 2003. I'm trying to sketch together a code to check a subform for records. A child record is required in the subform before the parent record can be saved. I put this together and it works fine except that after it fires I can't navigate to the subform. Of course, this is because the parent record hasn't been saved. Any ideas how to get around this? I could use and Undo but then a user might want to kill me every time their data gets undone because they've forgotten to enter a child record. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim rst As Recordset Set rst = sfrmINGsAllergens.Form.RecordsetClone If rst.RecordCount < 1 Then Beep MsgBox "Allergen info is required!", vbCritical Cancel = True End If End Sub Thanks in advance!
From: Dirk Goldgar on 6 Apr 2010 10:25 "johnlute" <jlute(a)marzetti.com> wrote in message news:433f18a8-914f-4d6a-97f3-d42fb4c38892(a)h27g2000yqm.googlegroups.com... > Access 2003. > > I'm trying to sketch together a code to check a subform for records. A > child record is required in the subform before the parent record can > be saved. From the sound of it, this is not ever going to work, uness the subform record is created in advance, through some means other than this form. You cannot create a related child record on a subform before the main form record has been saved. Therefore, if you make saving a new main-form record dependent on having a subform record exist, you have mutually contradictory requirements. Maybe I'm misunderstanding what you are trying to do. Could you explain in more detail? -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: johnlute on 6 Apr 2010 10:34 Hi, Dirk! > Maybe I'm misunderstanding what you are trying to do. Could you explain in > more detail? Nope. You're understanding. i'm trying to do the impossible. I don't know what I was thinking. Since I have to save the parent record first - how then do I stop from navigating away from said parent record if the subform has no records?
From: Marshall Barton on 6 Apr 2010 10:51 johnlute wrote: >Access 2003. > >I'm trying to sketch together a code to check a subform for records. A >child record is required in the subform before the parent record can >be saved. > >I put this together and it works fine except that after it fires I >can't navigate to the subform. Of course, this is because the parent >record hasn't been saved. > >Any ideas how to get around this? I could use and Undo but then a user >might want to kill me every time their data gets undone because >they've forgotten to enter a child record. > >Private Sub Form_BeforeUpdate(Cancel As Integer) >Dim rst As Recordset >Set rst = sfrmINGsAllergens.Form.RecordsetClone > If rst.RecordCount < 1 Then > Beep > MsgBox "Allergen info is required!", vbCritical > Cancel = True > End If >End Sub > Circular problem. You can not create a subform record until the main form record is saved, but you are preventing the main form record from being saved until after the subform record is created. I suppose you could gather the allergen data in unbound main form controls (positioned so they look like the subform). When you are satisfied that sufficient allergen data has been entered, save the main form record and then construct an append query to add the allergen record. After the first allergen record has been added, you can make the subform visible and use it to add more allergen records. -- Marsh MVP [MS Access]
From: Dirk Goldgar on 6 Apr 2010 10:55
"johnlute" <jlute(a)marzetti.com> wrote in message news:d1dee189-3e76-4271-84fa-24c00a771f21(a)n34g2000yqb.googlegroups.com... > >> Maybe I'm misunderstanding what you are trying to do. Could you explain >> in more detail? > > Nope. You're understanding. i'm trying to do the impossible. > > I don't know what I was thinking. Since I have to save the parent record > first - how then do I stop from navigating away from said parent record > if the subform has no records? Now *that* I can help you with. Here's an example of code for a form's module that prompts the user to enter a subform record if there isn't one, before moving to a new record or closing the form: '------ start of code ------ Option Compare Database Option Explicit Dim LastRecordID As Variant Private Function RequireChildRecord(Optional Unloading As Boolean) Dim GoBackID As Variant GoBackID = Null If Len(LastRecordID & vbNullString) > 0 Then If (LastRecordID <> Nz(Me.ID, 0)) Or Unloading Then If DCount("*", "tSub", "MainID=" & LastRecordID) = 0 Then If MsgBox("No child record entered for record! Go Back?", _ vbExclamation + vbYesNo, _ "Fix Record?") _ = vbYes _ Then GoBackID = LastRecordID End If End If End If End If If Not IsNull(GoBackID) Then If Unloading Then DoCmd.CancelEvent End If Me.Recordset.FindFirst "ID=" & GoBackID Else LastRecordID = Me.ID End If End Function Private Sub Form_Current() RequireChildRecord End Sub Private Sub Form_Unload(Cancel As Integer) RequireChildRecord True End Sub '------ end of code ------ In the above code, "tSub" is the name of the subform's recordsource table "ID" is the name of the primary key field of the main form's recordsource table "MainID" is the name of the foreign key field in tSub -- the Link Child Field The ID and MainID fields are numeric. Note that what actually happens in the above code is that we keep track in a module-level variable named "LastRecordID" of the *previous* record's ID value. Then, each time we move to a new record, we check whether the previously current record had any child records. If it didn't, we prompt the user to enter the required info, and go back to the previous record. This code does allow the user to say, "No, I really don't want to go back now." You may or may not want to allow them that choice, but if you don't, you may find them blowing away your application via the task manager, just to get out of the loop. Note also that this is proof-of-concept code. It doesn't handle the case where the user deletes a main form record that doesn't have a child. I believe that would be easy enough to do, but haven't done it. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |