Combo Box Check for value, if blank two more txt boxes blank ok

[Rita]

Attempting to set up validation at form BeforeUpdate. Am checking a combo
box, if it's "" or Null, then Serial and Order can be left Null or "" (Serial
and Order are both Txt) All controls are bound.
PartID looks at Part Table with 2 columns, only using description in form,
column 1 is 0". Using PartID to populate table being updated with
information. I'm sure I'm not getting null and "" straight. Thanks for any
help!

Here's the code.

If (IsNull(Me![Serial]) Or Me![Serial] = "") Or (IsNull(Me![SOrder]) Or
Me![SOrder] = "") And (Not IsNull(Me![PartID]) Or Me![PartID] <> "") Then
Beep
MsgBox "Equipment Entered. Must enter Serial # and/or Sales Order",
vbCritical
End If

If (IsNull(Me![Serial]) Or Me![Serial] = "") Or (IsNull(Me![SOrder]) Or
Me![SOrder] = "") And (Not IsNull(Me![PartID]) Or Me![PartID] <> "") Then
Cancel = True
Me![Serial].SetFocus
End If

 

[strive4peace]

Hi Rita,

rather than

If (IsNull(Me![Serial]) Or Me![Serial] = "") ...

try this:

If len(trim(nz(Me.Serial,""))) = 0 ...

'~~~~~~

you also need

CANCEL = true

after your first test (the one you have Beep on)

'~~~~~~

I would rewrite the code as follows:

'~~~~~~

If len(trim(nz(Me.PartID,""))) = 0 then exit sub

If len(trim(nz(Me.Serial,""))) = 0 then

msgbox "Equipment Entered -- " _
& " You must enter serial number", _
vbCritical,"Missing data"

me.serial.setfocus


beep
CANCEL = true
exit sub
End If

If len(trim(nz(Me.SOrder,""))) = 0 then

msgbox "Equipment Entered -- " _
& " You must enter sales order", _
vbCritical,"Missing data"

me.SOrder.setfocus

beep
CANCEL = true
exit sub
End If

'~~~~~~


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Rita]

Thanks Crystal. I'll give this a try and let you know how it goes!
--
Business Systems Analyst

Hi Rita,

rather than

If (IsNull(Me![Serial]) Or Me![Serial] = "") ...

try this:

If len(trim(nz(Me.Serial,""))) = 0 ...

'~~~~~~

you also need

CANCEL = true

after your first test (the one you have Beep on)

'~~~~~~

I would rewrite the code as follows:

'~~~~~~

If len(trim(nz(Me.PartID,""))) = 0 then exit sub

If len(trim(nz(Me.Serial,""))) = 0 then

msgbox "Equipment Entered -- " _
& " You must enter serial number", _
vbCritical,"Missing data"

me.serial.setfocus


beep
CANCEL = true
exit sub
End If

If len(trim(nz(Me.SOrder,""))) = 0 then

msgbox "Equipment Entered -- " _
& " You must enter sales order", _
vbCritical,"Missing data"

me.SOrder.setfocus

beep
CANCEL = true
exit sub
End If

'~~~~~~


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Attempting to set up validation at form BeforeUpdate. Am checking a combo
box, if it's "" or Null, then Serial and Order can be left Null or "" (Serial
and Order are both Txt) All controls are bound.
PartID looks at Part Table with 2 columns, only using description in form,
column 1 is 0". Using PartID to populate table being updated with
information. I'm sure I'm not getting null and "" straight. Thanks for any
help!

Here's the code.

If (IsNull(Me![Serial]) Or Me![Serial] = "") Or (IsNull(Me![SOrder]) Or
Me![SOrder] = "") And (Not IsNull(Me![PartID]) Or Me![PartID] <> "") Then
Beep
MsgBox "Equipment Entered. Must enter Serial # and/or Sales Order",
vbCritical
End If

If (IsNull(Me![Serial]) Or Me![Serial] = "") Or (IsNull(Me![SOrder]) Or
Me![SOrder] = "") And (Not IsNull(Me![PartID]) Or Me![PartID] <> "") Then
Cancel = True
Me![Serial].SetFocus
End If

 

[strive4peace]

you're welcome, Rita happy to help

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Rita]

Crystal,
Many thanks. You saved me endless aggravation! Works like a dream!
Have a wonderful day!

 

[strive4peace]

you too, Rita! I am glad you got it working

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*