Record Validation

[Dirk R]

Access 2000
Form / Subform

I am currently working on a project where the parent form (master)
enjoys a one-to-many relationship with the detail subform (child). An
ID field links them.

The subform is continuous and displays about six fields. These fields
are largely bound drop-down combo boxes that allow the user to choose
the values (it is limited to the list). But here's the catch: Based on
the value he or she chooses from DropDown1 it will populate the
dropdown fields in DropDown2 and DropDown3.

For example:

DropDownA contains a list of automobile manufactures.

DropDownA
---------
1. Ford
2. Dodge
3. BMW
4. Porche

Based on the selection of the user, it populates DropDownB, which
contains a list of models, and DropDownC, which contains
manufacturer-based financing plans. In this case, the user selects
"Ford". Thus the DropDownB would look like:

DropDownB
---------
1. Mustang
2. Thunderbird
3. Focus
4. Explorer
5. Taurus

DropDownC includes a list of financing options offered by each company
that are tied ONLY to the manufacturer (DropDownA value)

DropDownC
---------
1. Ford Small business financing
2. Ford First-time buyer financing
3. Ford College student incentive
4. Ford dealer direct
5. Ford Corporate account division

There are a number of other fields, but they are not pertinent to the
challenge at hand. Basically the continuous subform contains multiple
rows of this data. If the user decides to change the value in
DropDownA it will automatically re-populate DropDownB and DropDownC
while setting the value of each to Null (thus indicating the need to
choose new values for each) while leaving the remaining row fields
alone. This work perfectly.

However, it does pose one small challenge: data validation. Once the
user changes the selection of DropDownA and thus resets the value of
DropDownB and DropDownC to null, we do not want the user to close the
form (or exit the database) without first making his or her selection
from DropDownB and DropDownC. Every record must have these values
(this is database common sense).

In the table I have tried making "Required" the fields to which the
DropDowns are bound, but this spits back an error each time DropDownB
or DropDownC is reset, since you cannot have a Required field set to
Null.

What would be preferable is to find a method that by clicking a button
(or closing the form) it would automatically "validate" the data on
the subform based on rules either (1) specified in VBA or (2) as
specified by the validation rule on the field of the subform itself. I
realize that this tricky and complicated. I have tried several
procedures, but I am having difficulty finding the "fool proof"
solution.

Thanks for your time,

Dirk

 

[Allen Browne]

Easiest is if B and C can accept nulls. Just use the AfterUpdate event
procedure of DropDownA to clear the values from DropDownB and DropDownC:

Private Sub DropDownA_AfterUpdate()
If Not IsNull(Me.DropDownB) Then
Me.DropDownB = Null
End If
Call DropDownB_AfterUpdate
End Sub

Private Sub DropDownB_AfterUpdate()
If Not IsNull(Me.DropDownC) Then
Me.DropDownC = Null
End If
End Sub


If that is not acceptable, you will need to requery B and C, and then assign
their values to .ItemData(0).

 

[Suzanne Lejeune]

To the group,
I am having a similar problem, but I need you to go backwards on this. I am
a very junior "access" programmer. I want to do something similar but can't
figure out how to do it. I have list of main expense categories linked to a
list of sub type expenses. Once a user chooses the main category of
expense, I would like the list of sub type expenses to only display the ones
that are linked. No matter what I do it will always display all of them.
Your help on this is much appreciated. If this question is too junior for
this forum, please direct me elsewhere.

Thanks
Suzanne

 

[Marshall Barton]

. . . I have list of main expense categories linked to a
list of sub type expenses. Once a user chooses the main category of
expense, I would like the list of sub type expenses to only display the ones
that are linked.

Set the ExpenseType combo box's RowSource to a query that
uses the Category combo box as its criteria:

SELECT ExpenseType, ExpDescription
FROM ExpenseTypesTable
WHERE ExpenseCategory = Forms!theform.cboCategory
ORDER BY ExpDescription

Then, use a little code in the cboCategory combo box's
AfterUpdate event procedure to keep the two combo boxes in
sync:

Me.cboExpenseType = Null
Me.cboExpenseType.Requery

 

[Marshall Barton]

Set the ExpenseType combo box's RowSource to a query that
uses the Category combo box as its criteria:

SELECT ExpenseType, ExpDescription
FROM ExpenseTypesTable
WHERE ExpenseCategory = Forms!theform.cboCategory
ORDER BY ExpDescription

Then, use a little code in the cboCategory combo box's
AfterUpdate event procedure to keep the two combo boxes in
sync:

Me.cboExpenseType = Null
Me.cboExpenseType.Requery

Suzanne, please keep the correspondence in the newsgroups.

In response to private email:
Thanks so much for your help! I was able
to do the first part, but you lost me on the
second part. Do I need to create a macro
that runs after an update has been made
to the cboCategory box?
What does the Me stand for?

Those two lines are VBA code, not a macro. They are
supposed to be included in the category combo box's
AfterUpdate event procedure. The event procedure will run
whenever the value in the category combo box is set or
changed. The first line clears any existing value in the
expense combo box (because the category was changed and an
existing expense type may no longer apply). The second line
causes the expense type combo box to display the expense
types associated with the newly selected category.

Me is just the form objet that the VBA code is contained in.
It's not strictly required, but using it is a common
practice to eliminate any ambiguity with any variables of
the same name.