cascading combo boxes

[SunTop]

Hello everyone
I have a form in my database that allows the user to alter the records in the underlying table. In this form I have two cascading combo boxes, I have this vba code in the After Update event in the first combo box:

Private Sub cmbBox1_AfterUpdate()
Me.cmbBox2.Requery


Me. cmbBox2 = Null
End Sub

This code runs a requery on the second combo box when the user changes the first combo box and then set the second combo box to null to force the user to update it. What happens is that the second combo box is set to null but the underlying field retains its pervious value unless the user updates the second combo box. What I want is that the underlying field of the second combo box is set to null, not only the combo box so that the user is forced to update it.

[RuralGuy]

So does the cmbBox2 have a Control Source? In other words is it a bound control?

[Micron]

It's backwards. Set to Null or empty string ("") first, then requery.
Never mind - I guess it works either way, but I think not if the control is bound.

[John_G]

Does the table field the combo box is bound to allow Null values, i.e. is the Required property of that field set to No? I'm not sure if that is the cause of the problem, though. You could also try me.repaint after the Me. cmbBox2 = Null statement.

[SunTop]

Thanks all for your reply
RuralGuy : yes the cmbBox2 is bound to a control, the user is altering an existing record with this form
Micron: I tired re-ordering the code, but still the underlying table is not updated
John_G: the underlying field is set to required and if I delete the combo box with backspace and try to go to another field or close the form I get the normal required field error message; it is just when I try to delete it with the code above it does not get updated. I also tried me.repaint and I got the same result (the underlying field is not getting updated). I also tried using me.dirty = false and it did not work.

[RuralGuy]

What is the Field Type of the control source field? Is it numeric, date, text?

[SunTop]

the field type of the control source field is text

[RuralGuy]

Are Nulls allowed in your Text Field?

[SunTop]

No, in the field of the underlying table, I have (Is Not Null) in the validation rule

[RuralGuy]

Bingo! I think you have located the source of the issue. How about Do you have warnings turned off by chance? How about using a zero length string instead? ("")

[SunTop]

I am sorry, can you elaborate on that a bit more. do you mean changing my code from (Me. cmbBox2 = Null) to this (Me. cmbBox2 = "")? because I tried that just now and it did not work and how do I check if warnings are turned off or not ?

[RuralGuy]

DoCmd.SetWarnings True will turn on warnings
I would have expected Me. cmbBox2 = "" to work in any case.
Is the record source of your form update-able? Can you change other fields?

[RuralGuy]

BTW you keep showing Me. cmbBox2 = "" with a space after the ".". Is that correct or is it a type?

[SunTop]

yes, the record source is update-able. When I delete the content of cmbBox2 using backspace and I press Tab or close the form without putting anything in the cmbBox2 I get a warning message.

[Micron]

re: set warning - it would mean that somewhere in your code you had Docmd.Setwarnings False, which was executed but for some reason they were not turned on with Docmd.Setwarnings True. If that it the case, your code doesn't contain a properly structured error handling routine. You can turn them on simply by typing Docmd.Setwarnings True and hitting Enter, in the immediate window of the vb editor. What you're doing isn't really the right approach because with each change of the other combo, you are altering the value in a table because you've bound the control you're trying to blank out. Probably both should be unbound and their values either become part of the record append or update process.