Validation of data in a form

[reglarh]

I am trying to validate data held in two controls on a form.
The first control records membership status and is a combobox with 5 values allowed.

1 is 'member'
2 is 'under consideration'
3 is 'left' etc.

The combobox displays the description, the underlying table the number 1-5.

The second control records 'reason for leaving' and again is a combobox with 7 entries just as above.

I need to ensure that if a value is entered into the second control the value of the first control is '1' or 'member'.

Whatever I do I cannot get any code to work.

So, what event should I link to the code attached to the second combobox? On lost focus, before update or what?

What value do I check for in the comboboxes, the text or the number?

How do I set focus back to the first combobox in the event of an error?

I would post some sample code but this might leave me open to ridicule........

 

[maw230]

So basically, after the update of combobox 2 you would need to set combobox 1's value to 'member'.

Something like:

Private Sub combo2_AfterUpdate()
combo1.Value = 'member'

I didn't test that. Don't be afraid to post your code. Gotta start somewhere.

 

[reglarh]

Yes, but I want to check whether the entering of a reason code was correct with a message such as "you must change membership status to 'left' before giving a reason" and then setting focus back to the first combobox.

 

[maw230]

Yes, but I want to check whether the entering of a reason code was correct with a message such as "you must change membership status to 'left' before giving a reason" and then setting focus back to the first combobox.

Try something like this:

Private Sub combo2_AfterUpdate()
If combo1.value <> 'left' then
MsgBox "You must change status to left .... etc"
combo1.SetFocus
End if

 

[reglarh]

This is my code:

Private Sub Combo546_Exit(Cancel As Integer)
If Me.Combo548.Value > 1 And Me.Combo546.Value = 1 Then
MsgBox ("You must set member to 'left' before you can specify a reason")
Me.Combo548.Value = 1
Me.Combo546.SetFocus
End If
End Sub
Nothing happens!

I am not sure at what event I should place this code, or indeed whether this code is correct. When I message the value in the combobox it is the numeric value not the associated text value that is displayed. hence the use of numeric values in my code.

 

[maw230]

This is my code:

Private Sub Combo546_Exit(Cancel As Integer)
If Me.Combo548.Value > 1 And Me.Combo546.Value = 1 Then
MsgBox ("You must set member to 'left' before you can specify a reason")
Me.Combo548.Value = 1
Me.Combo546.SetFocus
End If
End Sub
Nothing happens!

I am not sure at what event I should place this code, or indeed whether this code is correct. When I message the value in the combobox it is the numeric value not the associated text value that is displayed. hence the use of numeric values in my code.

First, it might help you (and us) to give your controls like comboboxes identifying names, so you quickly know what they refer to. I'm guessing Combo548 is the 'member' selection.

I would put the code in the after update event of the second combobox, so that it's triggered immediately, but you don't necessarily want that code to execute if a user happens to make an incorrect selection, so maybe reconsider that. Try different events until something works the way you want it to.

Are your comboboxes displaying numbers or values? IF they are displaying values which they should be then I will assume you need to check for values in your code, but I'm not certain. You can go to the Property Sheet for your combobox and look at the Bound Column value to see which column from the Row Source the combo is referencing.

Your code looks fine my guess it that there is an error with the combo.value sections. Make sure you are actually "exiting" the combo to get the event to trigger.

 

[reglarh]

I have the code working after a fashion using the event of 'after tab'.
This does assume that the user does actually tab since there is only one more control on the form and this is not relevant to members who have left. When I try to insert the code in other events I get a number of different error messages.

Yes, the value of the entry in each combo box is the bound value, not the displayed text.

Any ideas on other events that i might try to incorporate this code?

Thanks for the comments so far.

 

[maw230]

When I try to insert the code in other events I get a number of different error messages.

That seems strange to me. You might want to run a compact and repair on your database and see if that helps. Maybe it is corrupted?