Cascading combo boxes not enforcing mandatory field

Posted on 2013-01-25
Medium Priority
Last Modified: 2013-01-25
I have a pair of cascading combo boxes on an Access 2010 form, one (Source) unbound and one (InformationTitle) bound to a mandatory field in the form's underlying recordsource. Source is used to narrow down the choice available in InformationTitle.

The record source for the form is:
SELECT tblEvidence.EvidenceName, tblEvidence.EvidenceID, tblEvidence.InformationID FROM tblEvidence ORDER BY tblEvidence.EvidenceName;

Open in new window

The row source for Source (unbound combo) is:

SELECT tblSource.SourceID, tblSource.Source FROM tblSource ORDER BY tblSource.Source;

Open in new window

The row source  for InformationTitle (bound to mandatory table field tblEvidence.InformationID) is:

SELECT tblInformation.InformationID, tblInformation.SourceID, tblInformation.Title
FROM tblInformation WHERE ([Source]) Is Null OR tblInformation.SourceID=[Source])
ORDER BY tblInformation.Title;

Open in new window

The relevant elements of the underlying tables are:

tblEvidence: EvidenceID (long); EvidenceName (text); InformationID (long)
tblInformation: InformationID (long); Title (text)
tblSource: SourceID (long); Source (text)

All these fields are mandatory.

There is a many-to-one relationship between tblInformation and tblEvidence, and a many-to-one relationship between tblSource and tblInformation.

The validation rule on InformationID in tblEvidence is (Is Not Null).
The validation rule for InformationTitle on the form is Not IsNull (InformationTitle) -- although I don't think it should need one if the validation rule on the underlying table is working.

In Source_AfterUpdate, the following code executes:

Me.InformationTitle.Value = Null

Open in new window

In form_Current, the following code executes:
     If IsNull(Me.InformationTitle) Then
         Me.Source = 1 'The default value
         Me.Source = Me.InformationTitle.Column(1)
     End If

Open in new window

There is no code in InformationTitle_AfterUpdate.

The cascade works as expected, with two exceptions.

1. If an existing record is edited, by (a) selecting a 'Source' which has no associated 'Information' records, and (b) saving the record without completing the InformationTitle field, the old value of InformationTitle is restored when the expected behaviour is that an error be reported and the user required to fix their data.  (If a new record is created and saved with InformationTitle blank, the error is trapped as expected).
2. If an existing record is edited, by (a) changing the selection of 'Source', and (b) selecting an associated piece of 'Information' and (c) reverting to the original 'Source', 'InformationTitle' also reverts to its previous value when the expected behaviour is that it would be set to Null ready for the user to make a new selection.

What blindingly obvious mistake have I made? I've been struggling with this for two days, and haven't found anything that sets me on the right path.
Question by:colevalleygirl
1 Comment

Accepted Solution

colevalleygirl earned 0 total points
ID: 38820097
I needed to remove the validation rule from InformationTitle on the form, and modify the code for Source_AfterUpdate to

Me.InformationTitle.Value = Null
Me.InformationTitle.Text = ""

Open in new window


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question