Solved: checking sub form cbo box value to see if main form checkbox needs checked

[salating]

I have a sub form in my main form with a combo box.

When the user selects a certain value in that combo box its supposed check to see of the date of birth is entered in the main form, if not it has to put a check in the need dob checkbox in the main form.

here is what the Row Source is for the combo box

SELECT ctyLookup.CountyID, ctyLookup.County, ctyLookup.State FROM ctyLookup ORDER BY ctyLookup.County, ctyLookup.State;

the Control Source is CountyID


here is what I have in the code in the OnChange event


Private Sub cboCounty_Change()
If (Me.Parent!DateofBirth = "") And Me!CountyID.Value = "3110" Or "3111" Or "3123" Or "3135" Then
Me.Parent!chkNeedDOB = -1
End If
End Sub


for some reason it puts a check in the need dob box for every selection in the combo box

[geekgirlau]

Try this:


Private Sub cboCounty_Change()
If (Nz(Me.Parent!DateofBirth, "") = "") Then
Select Case Me!CountyID.Value
Case "3110", "3111", "3123", "3135"
Me.Parent!chkNeedDOB = -1
End Select
End If
End Sub



The nz is to cope with any possible Null values. The other thing to check is the data type of the CountyID field - if this is numeric, you need to remove the quotes around "3110", "3111" etc.

[salating]

I didnt think of doing it that way.

I did it this way:


Private Sub cboCounty_AfterUpdate()
If Me.Parent!DateofBirth = "" Or IsNull(Me.Parent!DateofBirth) Then
If Me!CountyID = 3110 Or Me!CountyID = 3111 Or Me!CountyID = 3123 Then
Me.Parent!chkNeedDOB = True
Else
Me.Parent!chkNeedDOB = False
End If
End If
End Sub

[geekgirlau]

Yep, that would work although I'll make a couple of comments http://vbaexpress.com/forum/images/smilies/2cents.gif

The NZ function allows you to shorten this ...

If Me.Parent!DateofBirth = "" Or IsNull(Me.Parent!DateofBirth) Then

to this ...

If NZ(Me.Parent!DateofBirth,"") = "" Then

It also ensures that you don't get any errors due to Null values, which can occur very easily depending on the order of your If statement.

The other point I'd like to make is that where there are multiple If statements, it starts getting cumbersome, especially because you have to include the full comparison each time. For example, you can't just have


If Me!CountyID = 3110 Or 3111 Or 3123 Then

you have to do


If Me!CountyID = 3110 Or Me!CountyID = 3111 Or Me!CountyID = 3123 Then

My personal preference is to use Select Case as soon as my If statement contains more than a couple of choices.

[salating]

yea it is a lot eaiser the way you showed, ill probably change it to that way since its a lot eaiser to work with

thanks