Using VB to validate data in Subforms 2

[taiwai94]

Hello,

I've spent two days rummaging through the internet to try to find an a scenario similar to mine, but looks as if only being an amateur in VB isn't helping either.

I have started putting a database for the service I work for - courses for parents to help them regain control of their children. The database has two sections - one for people and one for courses.

The first form, FFamilies records a reference for the families.

Within this there is a subform called FFamiliesMembers which records info on the individual family members: name, date of birth, sex, ethnicity, etc. Typically, there are two family members recorded within each family - a parent and a child.

Within this there is another subform called FFamiliesMembersApps. This records info on based on the paper application forms such as who referred them to the course, clinical data, and the course they are applying for etc.

There is then another subform called FFamiliesMembersAppsAssess which is for keeping track of those famly members invited for assessment.

Then another subform called FFamiliesMembersAppsAssessOffers to record whether or not an offer was made to the family for a particular course.

The last subform is called FFamiliesMembersAppsAssessOffersAccs and is to record whether or not the family accepted the offer.

These subforms reflect the tabel structure and has been designed this way as at each stage,a number of applicants will be lost. So potentially 30 might apply for a course but the course can only accomodate 10 say.

Each stage (subform) has a bit of VB code for validation purposes.

For example, in the FFamiliesMembersApps subform there is a field called AssessmentOffered. If this field is null or not yes, then after a user attempts to enter data into the FFamiliesMembersAppsAssess the VB kicks in and stops them from entering data and informs them that the application was unsuccessful in the first place (hence, they can't enter data).

There are similiar VB validation rules (on the AfterUpdate event on the form) to follow the logical sequence so if a given stage was unsuccessful, you can't update the next stage.

An example of the VB in the given example is:

---------------------------------
Function ApplicationAssessment()
If (Eval("[Forms]![FFamilies]![FFamiliesMembers].[Form]![FFamiliesMembersApplications].[Form]![AssessmentOffered] <> 53 Or [Forms]![FFamilies]![FFamiliesMembers].[Form]![FFamiliesMembersApplications].[Form]![AssessmentOffered] Is Null ")) Then
MsgBox "An assessment cannot be attached to an unsuccessful application.", vbCritical, "Invalid Field Update"
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdUndo
End If
End Function

' 53 is the code for Yes

-------------------------------------------


Now, this validation process works perfectly, there are no problems with this aspect.


There is also a form called FCourses which contains information on the individual course. There are a number of subforms (within Tabs) on this form.

One of the subforms is called FCoursesApplications which is a modified copy of FFamiliesMembersApps (modified so new applications can't be added, since this may require the creation of a new family/member which this part of the database was not designed for) however, existing applications can be updated.

The FCoursesApplications form contains a subform called FFamiliesMembersAppsAssess which in turn contains all of the subsequent subforms mentioned above.

The problem now is that the VB validation rules don't work from within the FCourse form. The initial solution was to copy all the appropriate forms and modified copies versions of the VB to create validation compatibility, but from a maintenance point of view, this is very long winded and undesirable.

I have tried searching the internet and found something about using something like Me!Parent!Form!Field but I have not managed to get it working (limited VB knowledge/skills I'm afraid).

Could anyone please help with the VB syntax to make the VB validation rules work within both the FFamilies and FCourses forms without having to make copies of the existing subform hierarchy? Many thanks!

 

[MazeWorX]

Easy method for refrencing forms or controls.

Open the form you want to reference
Open a query in design view(any query)
place the cursor in the criteria field
right click select build.
Now click on Forms
Loaded forms
Click on the plus sign beside the form in question to expand the list of subforms
select the subform and then the control on the subform.
click paste
theres your reference. now just highlight the reference ctrl c to copy it and exit the builder without saving
paste the reference into you vba code.


Also your code above Sets Warnings to false but I dont see where you set it back to true.

DoCmd.SetWarnings True

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]

 

[taiwai94]

Hello MazeWorX,

Thanks for that but it didn't work when I tried it through both the FCourses or FFamilies forms.

What I'm trying to do is to have only one if/eval statement that works for 2 different scenarios which are:
------------------------------------------------------
If (Eval("[Forms]![FFamilies]![FFamiliesMembers].[Form]![FFamiliesMembersApplications].[Form]![AssessmentOffered] <> 53 Or [Forms]![FFamilies]![FFamiliesMembers].[Form]![FFamiliesMembersApplications].[Form]![AssessmentOffered] Is Null ")) Then...etc
------------------------------------------------------
If (Eval("[Forms]![FCourses]![FCoursesApplications].[Form]![AssessmentOffered] <> 53 Or [Forms]![FCourses]![FCoursesApplications].[Form]![AssessmentOffered] Is Null ")) Then...etc
------------------------------------------------------
Is itp ossible to have one if/eval that does the same job the two above do, and still be valid?

If it's any help, I'm using Access 2003 (SP3) with a database in 2002/2003 format.

Thanks.

 

[taiwai94]

Sorry, forgot to add, the VB code is triggered from the subform which would be:

FFamilies -> FFamiliesMembers -> FFamiliesMembersApplications

and

FCourses -> FCoursesApplications

respectively, thanks.

 

[taiwai94]

Sorry again, having a Friday meltdown, the subform in both cases is FFamiliesMembersAppsAssess (where the VB is triggered) and is as follows:

FFamilies -> FFamiliesMembers -> FFamiliesMembersApps -> FFamiliesMembersAppsAssess

and

FCourses -> FCoursesApplications -> FFamiliesMembersAppsAssess

 

[ElTheSwell]

You can make the If/Eval statement work in both cases (or for all forms having the same field name accepting a value of 53) by evaluating which form is current at run time (i.e. at the point this code is triggered) and assign it to a variable, then reference the variable in your if statement instead of literal name. Generally for subforms this is best accomplished by treating the subforms as controls on the main form rather than forms. In the following example the code is called by a command button’s Click event. The command button is on the subform. The code returns the name of the subform just updated before the Command button was clicked.

Dim ctlCurr As Control
Set ctlCurr = Screen.PreviousControl
MsgBox ("what is current subform? " & ctlCurr.Name)

You can follow this up with a SELECT block that takes different actions depending on which subform name is stored in ctlCurr.

Dim frmVar as string
frmVar = ctlCurr.Name

SELECT CASE frmVar
case “[FFamiliesMembersApplications]”
‘take some action
case ”[FCoursesApplications]”
‘do something else
end select

 

[taiwai94]

Thank-you for your help but I think I have managed to get it working - looks like I may have over-complicated it (?).

I used...

If Parent!AssessmentOffered <> 53 Or IsNull(Parent!AssessmentOffered) Then

...and this works in both cases. Thanks for your help though.