Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Cascading combo boxes not enforcing mandatory field

Posted on 2013-01-25
1
Medium Priority
?
401 Views
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.Requery
Me.InformationTitle.Value = Null
Me.InformationTitle.SetFocus

Open in new window


In form_Current, the following code executes:
     If IsNull(Me.InformationTitle) Then
         Me.Source = 1 'The default value
     Else
         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.
0
Comment
Question by:colevalleygirl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

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

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

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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