Can I use "OR" in an IsNull statement?

[KerryL]

Can I use an IsNull statement to check more than one control? Prior to closing and saving a customer order entry form I need to check to see if about 6 fields are not null.

Can I create a statement that checks more than one field (see example below) or must I create an If/Then statement for each on individually?

Will either of these statements work:

If IsNull(cboSource)or(cboRep)or(cboItem)or(cboQty) Then
MsgBox "Required fields must be entered before record
can be saved."
Exit Sub
End If

If IsNull(cboSource)cboRep)cboItem)cboQty) Then
MsgBox "Required fields must be entered before record
can be saved."
Exit Sub
End If


Thanks for your help,
KerryL

 

[THoey]

Never seen it done that way. I always saw it as:

If IsNull(cboSource)or
IsNull(cboRep)or
IsNull(cboItem)or
IsNull(cboQty) Then
MsgBox "Required fields must be entered before record
can be saved."
Exit Sub
End If

Terry

 

[KerryL]

Thank you Terry.

I'm experiencing a follow-up problem and I was wondering if you might be able to help with it.

A few of the fields that I need to check for data are on a subform. When I enter the code as you suggested above and reference the field via the subform, Access doesn't recognize it. Here are the details and code:

frmCustRequests (Parent Form)
sfrmRequestDetails (Subform)
cboDivision (Field on subform to be checked)

/code
If IsNull(Me.sfrmRequestDetails.cboDivision) Then
MsgBox "Division must be selected before record can be saved."
Me.sfrmRequestDetails.cboDivision.SetFocus
Exit Sub
End If
/code

When I try this code I get a "Data member or method not found" error message. FYI, I tried Forms! instead of Me. but that didn't work either.

How do I specifically reference a control on the subform from a command button on the parent form?

Thanks for your help,
KerryL

 

[THoey]

I haven't done too much with subforms. From the help:

To refer to a subform or subreport
Refer to the subform or subreport control on the form or report that contains the subform or subreport, then use the Form or Report property of the control to refer to the actual subform or subreport.

· Type the identifier for the form that contains the subform, followed by the name of its subform control, the . (dot) operator, and the Form property. For example, the following identifier refers to the Orders Subform subform on the Orders form:

Forms![ParentForm]![Subform].Control.Property

Terry

 

[GGleason]

KerryL,

See if the following works for your If statement:

If IsNull(Application.Forms("sfrmRequestDetails&quot.Controls(“cboDivision“)) Then


If it does then do likewise for the SetFocus.

hih
GGleason

 

[KerryL]

Thank you Terry & GGleason. We were all very close, but I couldn't get it to work until I used the code below. (Goals2 is the name of the application.)

If IsNull(Goals2.Form_sfrmRequestDetails.cboDivision) Then
MsgBox "Division must be selected before record can be saved."
Goals2.Form_sfrmRequestDetails.cboDivision.SetFocus
Exit Sub
End If


The funny thing is, I used the same syntax for the SetFocus line and it doesn't work. I don't get an error message or anything, but the cursor simply doesn't go the the cboDivision control. I tried setting the focus to a text box control in the subform but that didn't work either. I don't get it. Any ideas?

 

[WildHare]

The syntax for referring to a control on a subform is this:

Forms!MainFormName!{control-name that has subform in it-name}.FORM!control, e.g.

Forms![frmCustRequests]!{Dont know your CONTROL name}.FORM!cboDivision

I always NAME my subform controls "subctrl" - it just makes it easier to remember. Note you DON'T use the name of the form itself.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com