Clearing cascading combo boxes works but users able to save form with blanks!

[Zara D]

Dear all,



I've got a form with three cascading combo boxes, which work just fine. The code sets the 2nd and the 3rd combo boxes to Null if the 1st combo box is changed. This is all great and I do need this functionality. HOWEVER, all of the fields on the form are set as required but once the 2nd and the 3rd combo boxes are set to Null, the form will allow the user to save the form anyway. . What is the reason for that and how can I change it??

Thank you very much.

Cheers,

Zara

[Bulzie]

So your combo boxes are bound to fields in your table and they all must have values to save the record? If so, It is probably not saving as null after you blank it out maybe? You could put code in the Before Insert or Before Update event to say maybe:
If Len(Me.Combo2) < 1 then

Msgbox "Combo2 blank"
Me.Combo2.setfocus
End

Else

If Len(Me.Combo3) < 1 then

Msgbox "Combo3 blank"
Me.Combo3.setfocus
End

End if

End If

Also post your code that nulls out those combo boxes.

[Missinglinq]

Checking if a Control is Null really needs to be done in the Form_BeforeUpdate event, so building on Bulzie's example:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Me.Combo2) < 1 Then
  Cancel = True
  MsgBox "Combo2 blank"
  Me.Combo2.SetFocus
End If

If Len(Me.Combo3) < 1 Then
  Cancel = True
  MsgBox "Combo3 blank"
  Me.Combo3.SetFocus
End If

End Sub

Linq ;0)>

[Zara D]

Hello,

Thank you for trying to help!. Here is my code (I haven't used Null, sorry):


Private Sub cboProcure21_Dirty(Cancel As Integer)
Me.cboContract.Value = " "
Me.cboContract.Requery
Me.cboContractSubtype.Value = " "
Me.cboContractSubtype.Requery
End Sub

Procure 21 is my first combo box, Contract - my second, and Contract Subtype - my third. As Bulzie rightly suggested, this is an entry form bound to a table and all fields are mandatory to fill in. Without the code above, users are unable to save blank fields. With the code, however, they are allowed to save a record without selecting any values for Contract and Contract Subtype.

I also tried adding the code above but didn't help. This is my first data base ever, so I've spent so much time watching tutorials and reading forums like this one but please excuse my complete ignorance. Nonetheless, I am loving it .

Thanks!

Zara

[June7]

I use Null not empty string. The empty string is a value and will meet the required standard. I NEVER allow empty strings in records. Then instead of checking for length: If IsNull(Me.Combo2) Then


However, suggested code to check length of value should work with the empty string. "Didn't work" means what - error message, wrong results, nothing happens.

[Zara D]

I use Null not empty string. The empty string is a value and will meet the required standard. I NEVER allow empty strings in records. Then instead of checking for length: If IsNull(Me.Combo2) Then


However, suggested code to check length of value should work with the empty string. "Didn't work" means what - error message, wrong results, nothing happens.

Hello June 7,

The length code doesn't seem to be doing anything. It is as if it didn't exist at all - no error messages, no change in behavior, etc. I am attaching my database (this is a much earlier version with the corporate logos removed but it is in fact the same). The code is applied to the Entry form.

Is there an obvious reason for the issue?

Thank you for your time and help!

Cheers,

Zara

[June7]

You are not setting to empty string - there is a space between the quote marks. Remove the space.

Suggest you change TabOrder so the cursor progresses through controls sequentially instead of jumping around.

Can use expression in txtProc21 instead of VBA: =cboProcure21.Column(0). But why show the ID at all?

[Zara D]

You are not setting to empty string - there is a space between the quote marks. Remove the space.

Suggest you change TabOrder so the cursor progresses through controls sequentially instead of jumping around.

Can use expression in txtProc21 instead of VBA: =cboProcure21.Column(0). But why show the ID at all?

June 7,

I cannot believe it was something that simple. Now working like charm! THANK YOU!

[June7]

Yes, I should have seen that in the posted code without having to review the file. Glad it's working satisfactorily now.