message box on invalid data

[Carlee]

Hi there all,

I have three combo boxes on my form. I select the first
value and available selections in box two are limited to
only those corresponding to the value in box one. Box two
selection, in turn, narrows the available choices in box
three.

If a user needs modify the selection made, the requery
feature is set to allow that.

I use the following code for this to work:

Private Sub cboShipment_AfterUpdate()
Me!cboVariety.RowSource = "SELECT DISTINCT
[varietynumber] " _
& "FROM [tblpropagation] WHERE shipmentnumber =" _
& Me!cboShipment & " ORDER BY [varietynumber];"
Me!cboVariety.Requery
End Sub

Private Sub cboShipment_Change()
Me.cboShipment.Requery
End Sub

Private Sub cboVariety_AfterUpdate()
Me!cbopropagation.RowSource = "SELECT DISTINCT
[propagationnumber] " _
& "FROM [tblPropagation] WHERE shipmentnumber = " _
& Me!cboShipment & " AND varietynumber = " _
& Me!cboVariety & " ORDER BY [propagationnumber];"
Me!cbopropagation.Requery
End Sub

Private Sub cboVariety_Change()
Me.cboVariety.Requery
End Sub


Problem: I want to have the form alert the user if an
invalid set of data is attempted to be entered.

For example. The user comes in a modifies box two, making
the available choices in box three different. Currently,
the old value stays available and as such, an invalid
entry can be made.

How can i have the system requery and recognize before the
user leaves the form, to make sure their selections are
valid.

Many thanks,

Carlee

 

[Armen Stein]

For situations like this, I do a DCount (of the records in
the table matching the selected criteria) before each
Requery and if the count is 0 I display an error message
and reset things.

Hope this helps!

Howard Brody

-----Original Message-----
Hi there all,

I have three combo boxes on my form. I select the first
value and available selections in box two are limited to
only those corresponding to the value in box one. Box two
selection, in turn, narrows the available choices in box
three.

If a user needs modify the selection made, the requery
feature is set to allow that.

I use the following code for this to work:

Private Sub cboShipment_AfterUpdate()
Me!cboVariety.RowSource = "SELECT DISTINCT
[varietynumber] " _
& "FROM [tblpropagation] WHERE shipmentnumber =" _
& Me!cboShipment & " ORDER BY [varietynumber];"
Me!cboVariety.Requery
End Sub

Private Sub cboShipment_Change()
Me.cboShipment.Requery
End Sub

Private Sub cboVariety_AfterUpdate()
Me!cbopropagation.RowSource = "SELECT DISTINCT
[propagationnumber] " _
& "FROM [tblPropagation] WHERE shipmentnumber = " _
& Me!cboShipment & " AND varietynumber = " _
& Me!cboVariety & " ORDER BY [propagationnumber];"
Me!cbopropagation.Requery
End Sub

Private Sub cboVariety_Change()
Me.cboVariety.Requery
End Sub

For "cascading comboboxes" (as we call them), we usually Null (or set to
<all>) the lower entries if an upper one changes.