check subform for records
in [Forms]
|
From: johnlute on 6 Apr 2010 11:24 Dirk - Holy cow! Is that ever cool! I actually have two subforms so I tweaked yuor code to this: Private Function RequireChildRecord(Optional Unloading As Boolean) Dim GoBackID As Variant GoBackID = Null If Len(LastRecordID & vbNullString) > 0 Then If (LastRecordID <> Nz(Me.IMNumber, 0)) Or Unloading Then If DCount("*", "tblINGsAllergens", "IMNumber=" & LastRecordID) = 0 Then If MsgBox("Allergen information is required!", _ vbCritical + vbOKOnly) Then GoBackID = LastRecordID If DCount("*", "tblINGsSensitivities", "IMNumber=" & LastRecordID) = 0 Then If MsgBox("Sensitivity information is required!", _ vbCritical + vbOKOnly) Then GoBackID = LastRecordID End If End If End If End If End If End If If Not IsNull(GoBackID) Then If Unloading Then DoCmd.CancelEvent End If Me.Recordset.FindFirst "IMNumber=" & GoBackID Else LastRecordID = Me.IMNumber End If End Function Does that ever work like a charm! > 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. Thanks for the elegant and clear explanation. I decided to opt for the blowing out the app simply because it's sensitive niformation that I think they'd be afraid to blow out of. Wowzers! Thanks a whole big bunch!!!
From: johnlute on 6 Apr 2010 11:26 Hi, Marsh. > 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. Yeah. I was having a brain cramp. > 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. That's something I started to tinker with but you spelled it out better than what I was trying to do. I'm thinking that this would work, too. Thanks for your input!
From: Dirk Goldgar on 6 Apr 2010 12:44 "johnlute" <jlute(a)marzetti.com> wrote in message news:2ca530f3-1e89-437b-9a7f-5a5d6710e29c(a)r27g2000yqn.googlegroups.com... > >Holy cow! Is that ever cool! <g> > I actually have two subforms so I tweaked yuor code to this: > [...] > If DCount("*", "tblINGsAllergens", "IMNumber=" & LastRecordID) > = 0 Then > If MsgBox("Allergen information is required!", _ > vbCritical + vbOKOnly) Then > GoBackID = LastRecordID > > If DCount("*", "tblINGsSensitivities", "IMNumber=" & > LastRecordID) = 0 Then > If MsgBox("Sensitivity information is required!", > _ > vbCritical + vbOKOnly) Then > GoBackID = LastRecordID > End If > End If > End If > End If > [...] > I decided to opt for the blowing out the app simply because it's sensitive > niformation that I think they'd be afraid to blow out of. I though you might decide that. But I note two things in the above-quoted code. 1. Since you're not examining the results of the msgboxes, you don't need to put them in If statements. The following code would be equivalent, but simpler: If DCount("*", "tblINGsAllergens", _ "IMNumber=" & LastRecordID) = 0 _ Then MsgBox "Allergen information is required!", _ vbCritical + vbOKOnly GoBackID = LastRecordID If DCount("*", "tblINGsSensitivities", _ "IMNumber=" & LastRecordID) = 0 _ Then MsgBox "Sensitivity information is required!", _ vbCritical + vbOKOnly GoBackID = LastRecordID End If End If 2. It looks to me as if you are only checking tblINGsSensitivities if you've already determined that there isn't a record in tblINGsAllergens. Is that what you intended? If not, if you want to test each child table independently, then you would revise the above to: Dim strMessage As String ' ... If DCount("*", "tblINGsAllergens", _ "IMNumber=" & LastRecordID) = 0 _ Then strMessage = vbCr & "Allergen information is required!" GoBackID = LastRecordID End If If DCount("*", "tblINGsSensitivities", _ "IMNumber=" & LastRecordID) = 0 _ Then strMessage = strMessage & vbCr & _ "Sensitivity information is required!", _ GoBackID = LastRecordID End If If Len(strMessage) > 0 Then MsgBox Mid(strMessage, 2), vbCritical + vbOKOnly End If ' ... -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: johnlute on 6 Apr 2010 14:22
Awesome catch, Dirk! I was so thrilled at the function that I jumped the gun a bit. As I went back and continued testing it I realized exactly what you just pointed out. > 1. Since you're not examining the results of the msgboxes, you don't need to > put them in If statements. The following code would be equivalent, but > simpler: Thanks - I flew over that. > 2. It looks to me as if you are only checking tblINGsSensitivities if you've > already determined that there isn't a record in tblINGsAllergens. Is that > what you intended? If not, if you want to test each child table > independently, then you would revise the above to: > > Dim strMessage As String > > ' ... > > If DCount("*", "tblINGsAllergens", _ > "IMNumber=" & LastRecordID) = 0 _ > Then > strMessage = vbCr & "Allergen information is required!" > GoBackID = LastRecordID > End If > > If DCount("*", "tblINGsSensitivities", _ > "IMNumber=" & LastRecordID) = 0 _ > Then > strMessage = strMessage & vbCr & _ > "Sensitivity information is required!", _ > GoBackID = LastRecordID > End If > > If Len(strMessage) > 0 Then > MsgBox Mid(strMessage, 2), vbCritical + vbOKOnly > End If I want to check each table so your perception and revision is on track! Thanks for the follow-up and correction. As always you are a scholar and a gentleman. I hope the Easter Bunny left you a nice chocolate morsel. :) |