Verify form fields before adding new record

[randolphoralph]

I have a form that has a command button called AllLoad that verifies the current record has certain fields filled in before adding a new record. The code works on the 1st part and returns a message box when the Number field has been left blank, but then does not return the message box when the Date field is blank and the Status field is empty, expired, or Not eligible.



I believe the issue I am encountering is coming from the Me.Status <> 0.

So what I am looking to accomplish on the 1st ElseIf is to return the message box if the Date field is empty and Status field is empty, but not equal to expired, or not eligible.

Private Sub AllLoad_Click()
If Len(Nz(Me.Number, "")) = 0 Then
   MsgBox "Please enter #"
   Me.Number.SetFocus
ElseIf (Len(Nz(Me.Date, "")) = 0) And (Me.Status <> "expired") And (Me.Status <> "Not eligible") And (Me.Status <> 0) Then
   MsgBox "Please enter Date"
   Me.Date.SetFocus
ElseIf (Len(Nz(Me.Name, "")) = 0) And (Me.Status <> "expired") And (Me.Status <> "Not eligible) And (Me.Status <> 0) Then
   MsgBox "Please enter Name"
   Me.Name.SetFocus
   
Else
 DoCmd.GoToRecord acActiveDataObject, , acNewRec
 End If
Number.SetFocus
 
End Sub

[RuralGuy]

Is the Status field a number or text? You seem to be checking for both.

[randolphoralph]

It is a text field. I tried Me.Status <> "" and it did not work.

[Perceptus]

You can check if a field contains a date or a number or other wise like

Untested, just typed it in =)

if isnumeric(me.number) = false then msgbox "please enter a number":me.number.setfocus:exit sub
If isdate(me.date) = false then msgbox "please enter a date":me.date.setfocus:exit sub
if nz(me.name,"")="" then msgbox "please enter a name":me.name.setfocus:exit sub

[RuralGuy]

A text 0 would be "0" not just 0 without the quotes.

[randolphoralph]

Based on this I should not be using a 0 or "0" to determine if the field is blank.


Ok after thinking about it here is what I am trying to doI


IF Date = 0 AND Status = "" Then Date.SetFocus

OR

IF Date = 0 AND Status <> "expired" AND Status <> "Not eligible" Then Date.SetFocus



I need to do an and/or statement. I thought I had it, but it does not appear to work....here is my thoughts....

Current Code:

ElseIf (Len(Nz(Me.Date, "")) = 0) And (Me.Status <> "expired") And (Me.Status <> "Not eligible") And (Me.Status <> 0) Then
MsgBox "Please enter Date"
Me.Date.SetFocus

Attempted Code:

ElseIf ((Len(Nz(Me.Date, "")) = 0) AND (Me.Status = "")) OR ((Len(Nz(Me.Date, "")) = 0) And (Me.Status <> "expired") And (Me.Status <> "Not eligible") And (Me.Status <> 0)) Then
MsgBox "Please enter Date"
Me.Date.SetFocus

[orange]

What exactly does this mean in plain English

IF Date = 0 AND Status = "" Then Date.SetFocus

OR

IF Date = 0 AND Status <> "expired" AND 
Status <> "Not eligible" Then Date.SetFocus

[CJ_London]

I would be surprised if you ever set the focus since Date is a reserved word (it returns today) so your formula is effectively saying 'if today =0 ....' today (3rd April) is 42097 so 42097 is never going to equal 0

[randolphoralph]

Ajax good catch...I have updated Date to DateComplete.

Orange I will try to word it another way. I want to return the message box and set focus on DateComplete when the DateComplete field is blank and the Status field is blank or contains any value other than expired or Not eligible.

[rpeare]

Private Sub AllLoad_Click()
dim sMsg

If isnull(me.number) Then smsg = "# (Number)" & vbcrlf
if isnull(me.[date]) then smsg = smsg & "Date" & vbcrlf
if isnull(me.[name]) then smsg = smsg & "Name" & vbcrlf 

if len(smsg) > 0 then
    msgbox "WARNING:" & vbcrlf & vbcrlf & "The following fields are required" & vbcrlf & vbcrlf & "Please populate the data and try again", vbokonly, "ERROR ADDING RECORD"
    if instr(smsg, "#") > 0 then 
        docmd.gotocontrol("Number")
    elseif instr(smsg, "date") > 0 then
        docmd.gotocontrol("Date")
    elseif instr(smsg, "name") > 0 then
        docmd.gotocontrol("Name")
    endif
Else
     DoCmd.GoToRecord acActiveDataObject, , acNewRec
End If
 
End Sub

[randolphoralph]

Thank you rpeare. How would I add checking the values in the Status field?

[rpeare]

personally I would do this with a TAG property setting like putting REQ in the TAG property of all required fields otherwise you have to continually modify this code whenever you add a new field but as to the question

There are two places you have to added it
1. The portion up top where you are building your error message (fields missing)
2. In the IF loop put it in the position you deem appropriate (i.e. if it's your last data entry field put it in the last elseif loop, if you want it 2nd, put it in the 2nd Elseif loop etc.

[randolphoralph]

Ok thanks. I will try that.