Reset data to Null in a required field

[BiigJiim]

Hi,

I have a form which contains 2combo boxes (cboMag and cboIssue) bound to two fields (Magazine and Issue), both of which have the Required property = YES. The range of values which can be entered in the 2nd combo box depend on the selection in the 1st combo box.

The problem I have is that if the user CHANGES the value in the 1st field, I want the form to reset the 2nd field to NULL, so that IT MUST be entered again. However if I try this with me.cboIssue= NULL in the AfterUpdate event of cboMag, Access throws an error because cboIssue is bound to a required field. How do I get round this without removed the required field property?

To explain the problem specifically:

My tbl_Bookings has 2 required fields; Magazine and Issue. All possible combinations of Magazine and Issue are stored in tbl_MagazineIssues as follows:

Magazine - Issue
ABC - 01/2016
ABC - 02/2016
ABC - 03/2016
XYZ - 01/2016
XYZ - 02/2016

When the user selects Magazine ABC in the 1st combo box (cboMag) on my booking form, the 2nd combo box (cboIssue) is requeried to only show issues for that magazine. However, if the user selects ABC in cboMag and then 03/2016 in cboIssue, and then decides to change cboMag to XYZ, the value 03/2016 remains in the issue field bound to cboIssue. I want cboIssue to be null so that a new Issue selection has to take place.

As always, any help greatly appreciated!

Jim

 

[The_Doc_Man]

How do I get round this without removed the required field property?

By removing the required field property but adding a "Before Update" event to look at both fields and do a deferred sanity check.

I checked before answering. You can cancel the pending update event if you don't like the situation. It is one of the event cases where you get called with a "Cancel" variable. I.e. Form_BeforeUpdate( Cancel as Integer ) .

The "Cancel" variable is ByRef so if you set it to 1 (or -1) you will prevent the update from occurring. Then you can set up a message box to tweak your user's nose or slap his wrist or whatever image suits you best. If, on the other hand, you like what you see, you just leave Cancel as 0 (which is what it is on entry) and do an End Sub type of return.

 

[Galaxiom]

By removing the required field property but adding a "Before Update" event to look at both fields and do a deferred sanity check.

I disagree. The rule should stay in the table because that is the only place to absolutely ensure data integrity.

Before the value is allowed to change in the first combo, the system should pop up demanding a new value for the second combo.

 

[BiigJiim]

By removing the required field property but adding a "Before Update" event to look at both fields and do a deferred sanity check.

I checked before answering. You can cancel the pending update event if you don't like the situation. It is one of the event cases where you get called with a "Cancel" variable. I.e. Form_BeforeUpdate( Cancel as Integer ) .

The "Cancel" variable is ByRef so if you set it to 1 (or -1) you will prevent the update from occurring. Then you can set up a message box to tweak your user's nose or slap his wrist or whatever image suits you best. If, on the other hand, you like what you see, you just leave Cancel as 0 (which is what it is on entry) and do an End Sub type of return.

This may be the option I have to go for. But I was trying to avoid doing this - what is the point in having a Required property if the limitations of Access prevent you from using it?

Why oh why does Access check the requirement is met every time the bound control is updated, and not when it tries to save the record?!?

I disagree. The rule should stay in the table because that is the only place to absolutely ensure data integrity.

Before the value is allowed to change in the first combo, the system should pop up demanding a new value for the second combo.

I agree, the rule should stay in the table. Unfortunately, it is not practical here to have a popup prompting for the 2nd combo box value.

There must be some other way round this?

Thanks for your help guys,
Jim

 

[Minty]

You could make the form transactional, I think that might get around your problem?
But it's a bit of a sledgehammer to crack a nut.

 

[Galaxiom]

You make a business rule that demands a value in the second combo and quite rightly you put a rule on the table to ensure it.

Now you want to break that rule "so that IT MUST be entered again". But you don't want the system to demand a value when there is none. If you want both modes then you must inform the system that it is permitted under certain circumstances that it can test.

In your case I would have an "out of scope" value that fulfils the Required property but unambiguously indicates the value is a place holder not a real value. Using zero or a negative number when the valid values are invariably positive is one example of this.

It is easy to filter on the place holder when the records without the second field need to be selected.