Validating through event procedure for two fields 2

[mellay]

Hi!
Could someone tell me how I could validate data between two controls on a form? I have two Fields, "Counted?" and "1-Summary". The "Counted?" field has only (2) possible entries, a "Y" for Yes or a 'N' for No. Both are Combo Boxes.
The "1-Summary" Field may contain many different entries if a "Y" is shown in the "Counted?" field. If the "Counted?" field has an 'N', then the "1-Summary" field MUST have an entry of 'Not Counted'. A lot of the time staff will either leave the 1-Summary" field blank or an entry would be made relevant to a case that is counted.
Is it possible to force an entry of NOT COUNTED in the "1-Summary" field if the "Counted?" field is showing an 'N'? Any help would be greatly appreciated!! PS Love this forum!! Many thanks to Terry H!!

 

[Scoty]

Mellay,
some where before they save record try this:

Select Case Me.Counted?
    Case "N"
        Me.1-Summary="Not Counted"
    Case Else
        'Do Nothing
End select

I'm not trying to be picky but it is really not a good Idea to use special Character (other that _ ) in your field names. For instance "1-Summary" if you do not qualify it with the Me. Statement it will try to subtract the value of Summary from 1 and the ? character on the "Counted?" field could mistaken for a print to debug command. Again I'm not trying to be picky just trying to save you some heartache later on down the road. Other than that I hope the code helps

Scoty : "Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[mellay]

Thanks Scoty for your response and your advice! I appreciate it. I am a newcomer to Access, so forgive me, but would you please tell me how and where the code should go? Thank You!! mellay

 

[mrf1xa]

Hi Mellay

Probably the best place to put Scoty's code would be the After_update event of your counted combo box. It will then run as soon as the counted box is changed by the user.

May be worth adding a bit as well:

Select Case Me.Counted?
Case "N"
Me.1-Summary="Not Counted"
Case Else
Me.1-Summary= Null
End select

What this will do is cover the situation where a user selects 'N' and the second box becomes 'Not Counted' but then goes back and changes their mind. When they change it to 'Y' instead the code will run again and blank out the 'Not Counted' so they can put something else in.

Hope this helps

Nigel

 

[mellay]

IT WORKS!!!! Thank you BOTH So Much!!! mellay

 

[mellay]

Hi! One Final Question? If I wanted to have another field that must be populated if '1-Summary' shows 'NOT COUNTED', how would I write "Must Not Be Null" or perhaps "Like" in the code shown above? This field will be the reason for NOT COUNTED' and may contain many different entries. Thank you for your Excellent Help!! mellay PS Love this Forum!

 

[mrf1xa]

Hi Mellay

The following code will create a messahe box prompting the user to fill in the field if it is empty:

Dim str As String
Me![NAME OF YOUR FIELD].SetFocus
If Me![NAME OF YOUR FIELD].Text = "" Then
str = MsgBox("Please complete NAMEOFYOURFIELD", _
vbExclamation + vbOKOnly, "INFORMATION MISSING&quot
End If

I hope this is the sort of thing you are after?

Nigel