New Record command not firing

test this codes first

Private Sub btnSave_Click()
'If MsgBox("Do you want to SAVE the record (or the change)", vbYesNo, "Save Record...") = vbNo Then
'        Me.Firstname.SetFocus
'        'Me.Company.SetFocus
'       Me.Undo
'     Exit Sub
' Validates that Control has Data for required feilds
If Len(Firstname) = 0 Or IsNull(Firstname) = True Then
  Call MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
  'Me.Company.SetFocus
  Me.Firstname.SetFocus
  Exit Sub
ElseIf Len(Lastname) = 0 Or IsNull(Lastname) = True Then
  Call MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
  'Me.Company.SetFocus
  Me.Lastname.SetFocus
  Exit Sub
ElseIf Len(Address1) = 0 Or IsNull(Address1) = True Then
  Call MsgBox("You must enter Address1", vbExclamation, "Add Address1")
  'Me.Company.SetFocus
  Me.Address1.SetFocus
  Exit Sub
ElseIf Len(City) = 0 Or IsNull(City) = True Then
  Call MsgBox("You must enter City", vbExclamation, "Add City")
  'Me.Company.SetFocus
  Me.City.SetFocus
  Exit Sub
ElseIf Len(State) = 0 Or IsNull(State) = True Then
  Call MsgBox("You must select a State", vbExclamation, "Add State")
  'Me.Company.SetFocus
  Me.cboState.SetFocus
  Exit Sub
ElseIf Len(Zip) = 0 Or IsNull(Zip) = True Then
  Call MsgBox("You must enter a Zip", vbExclamation, "Add Zip")
  'Me.Company.SetFocus
  Me.Zip.SetFocus
  Exit Sub
ElseIf Len(Phone) = 0 Or IsNull(Phone) = True Then
  Call MsgBox("You must enter a Phone No", vbExclamation, "Add Phone")
  'Me.Company.SetFocus
  Me.Phone.SetFocus
  Exit Sub
ElseIf Len(cboCSR) = 0 Or IsNull(cboCSR) = True Then
    Call MsgBox("You must select a CSR", vbExclamation, "Add CSR")
    'Me.Company.SetFocus
    Me.cboCSR.SetFocus
  Exit Sub
ElseIf Len(cboMod) = 0 Or IsNull(cboMod) = True Then
    Call MsgBox("You must select a Model", vbExclamation, "Add Model")
    'Me.Company.SetFocus
    Me.cboMod.SetFocus
  Exit Sub
ElseIf Len(Serial) = 0 Or IsNull(Serial) = True Then
    Call MsgBox("You must select a Serial Number", vbExclamation, "Add Serial")
    'Me.Company.SetFocus
    Me.Serial.SetFocus
ElseIf Len(ProblemDesc) = 0 Or IsNull(ProblemDesc) = True Then
    Call MsgBox("You must select a Problem Description", vbExclamation, "Add ProblemDesc")
    'Me.Company.SetFocus
    Me.ProblemDesc.SetFocus
	Exit Sub
End if
 'Save the record.
    RunCommand acCmdSaveRecord
    'Load a new record after the save.
    DoCmd.GoToRecord , , acNewRec
End Sub
                                  

Select all Open in new window

there is a more elegant way of coding this.
first, in the design view of your form, select all the controls that are required to be fill in
open the Property sheet and select the Other tab
in the Tag property place REQ or Required

then see this threads

http://www.experts-exchange.com/Database/MS_Access/Q_22653980.html#accepted-solution
http://www.experts-exchange.com/Database/MS_Access/Q_28201725.html#accepted-solution

You're referring to a Cancel variable, but there's not one available. You can use the If ElseIf structure if you declare that variable, and then move the "save record" stuff out of the If - ElseIf - End If structure:

Private Sub btnSave_Click()

'/ you must declare a Cancel variable
Dim cancel as Boolean

'If MsgBox("Do you want to SAVE the record (or the change)", vbYesNo, "Save Record...") = vbNo Then
'        Me.Firstname.SetFocus
'        Me.Company.SetFocus
'       Me.Undo
'      Cancel = True
' Validates that Control has Data for required feilds
If Len(Firstname) = 0 Or IsNull(Firstname) = True Then
  Call MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
  Me.Company.SetFocus
  Me.Firstname.SetFocus
   Cancel = True
ElseIf Len(Lastname) = 0 Or IsNull(Lastname) = True Then
  Call MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
  Me.Company.SetFocus
  Me.Lastname.SetFocus
   Cancel = True
ElseIf Len(Address1) = 0 Or IsNull(Address1) = True Then
  Call MsgBox("You must enter Address1", vbExclamation, "Add Address1")
  Me.Company.SetFocus
  Me.Address1.SetFocus
   Cancel = True
ElseIf Len(City) = 0 Or IsNull(City) = True Then
  Call MsgBox("You must enter City", vbExclamation, "Add City")
  Me.Company.SetFocus
  Me.City.SetFocus
   Cancel = True
ElseIf Len(State) = 0 Or IsNull(State) = True Then
  Call MsgBox("You must select a State", vbExclamation, "Add State")
  Me.Company.SetFocus
  Me.cboState.SetFocus
   Cancel = True
ElseIf Len(Zip) = 0 Or IsNull(Zip) = True Then
  Call MsgBox("You must enter a Zip", vbExclamation, "Add Zip")
  Me.Company.SetFocus
  Me.Zip.SetFocus
   Cancel = True
ElseIf Len(Phone) = 0 Or IsNull(Phone) = True Then
  Call MsgBox("You must enter a Phone No", vbExclamation, "Add Phone")
  Me.Company.SetFocus
  Me.Phone.SetFocus
   Cancel = True
ElseIf Len(cboCSR) = 0 Or IsNull(cboCSR) = True Then
    Call MsgBox("You must select a CSR", vbExclamation, "Add CSR")
    Me.Company.SetFocus
    Me.cboCSR.SetFocus
   Cancel = True
ElseIf Len(cboMod) = 0 Or IsNull(cboMod) = True Then
    Call MsgBox("You must select a Model", vbExclamation, "Add Model")
    Me.Company.SetFocus
    Me.cboMod.SetFocus
   Cancel = True
ElseIf Len(Serial) = 0 Or IsNull(Serial) = True Then
    Call MsgBox("You must select a Serial Number", vbExclamation, "Add Serial")
    Me.Company.SetFocus
    Me.Serial.SetFocus
ElseIf Len(ProblemDesc) = 0 Or IsNull(ProblemDesc) = True Then
    Call MsgBox("You must select a Problem Description", vbExclamation, "Add ProblemDesc")
    Me.Company.SetFocus
    Me.ProblemDesc.SetFocus
End If
 
 If Not cancel Then
   RunCommand acCmdSaveRecord
   'Load a new record after the save.
   DoCmd.GoToRecord , , acNewRec
 End If

End Sub

Thanks to all who have responded.
Rey, I am intrigued by the code in the second link you provided, would I have to recreate that for each control or simply tag each control with REQ and it will know which control is blank

Private Sub Form_BeforeUpdate(Cancel as integer)
Dim strCtlName As String, NullCtl As String, Msg As String, ctl As Control
strCtlName = ""
For Each ctl In Me.Controls
    If ctl.Tag = "REQ" Then
        If Len(Me(ctl.Name) & "") = 0 Then
            strCtlName = strCtlName & ctl.Name & ";"
        End If
    End If
Next
If Len(strCtlName) > 0 Then
    NullCtl = Mid(strCtlName, 1, InStr(strCtlName, ";") - 1)
    Msg = "Please fill out the required fields!" & vbCr & vbCr
    Msg = Msg & Left(strCtlName, Len(strCtlName) - 1)
    MsgBox Msg, vbCritical, "Required Fields"
    Cancel=True
    Me(NullCtl).SetFocus
    Exit Sub
End If
end sub

Select all Open in new window

Yes,  It's a loop.  It looks at every control and looks a every tag for "REQ"
For Each ctl In Me.Controls
    If ctl.Tag = "REQ" Then
        If Len(Me(ctl.Name) & "") = 0 Then
            strCtlName = strCtlName & ctl.Name & ";"
        End If
    End If
Next

It then does some nice legerdemain -- which may not always play nice -- to coerce the controls value to a string and test if that string is longer than zero.  It then msgbox's about them.

This works.

I don't like it.

It does not customize the message
It tests only for a character in the control, not a valid value ("a" in a control that requires an integer, e.g.)
It throws the focus to the last invalid control found -- but you can't control the order that Me.Controls is checked.
It depends upon the Tag property and therefore splits the functionality between the code and the control's properties.

It works and is commonly used.
Given its flaws, a well-written validation function is a better thing to leave a successor.
YMMV

<I don't like it.>
then don't use it.


@skull52
just simply tag each control with REQ and it will know which control is blank..

and this function is a general function to test if an entry was made or not in a control.
it is not a do everything to check the content of a control.
i have one like that but it was requested and paid for by a client and can't post it here

@Rey
<I don't like it.>
 then don't use it.

I don't.
And I don't recommend it.
And I point out why I don't recommend it.

it is not a do everything to check the content of a control.
Everywhere you go, every platform you use, for as long as you are in the business, you should be validating user input before doing anything with it.  That's a good, life-long habit to cultivate.

The Bobby Tables cartoon
http://xkcd.com/327/

Rey, I tested it and it works just fine, I don't have content validation on any of the controls so that is not an issue, the message informs the user they have left a required field blank and which one(s), If you leave more than one required field blank and you fix one and not the others it prompts and places focus on each one progressively. I have no controls that are integers, so no issue there either.

The click event of your save button is the WRONG place to do validation.  Your save button should force the record to save and that will fire the FORM's BeforeUpdate event and the FORM's BeforeUpdate event is where the validation code actually belongs.  That way, no matter what the user does, the code will fire if Access decides it needs to save the current record.

Split the conditions into individual If statements as Nick suggested.  the conditions are not dependent on each other so logically it is incorrect to nest them.

If all you want to check for is presence, then the code posted by Rey is OK but it needs to be included in each form.  I prefer to put this type of code in a standard code module.  Then I call it from the BeforeUpdate event of the form and pass in a reference to the form object.

If CheckForEmpty(Me) = true Then
    ''' at least one field is empty
   Cancel = True
   Exit Sub
End If

Public Function CheckForEmpty(frm as Form) as Boolean
    CheckForEmpty = False
    Dim ctl As Control
    For Each ctl In frm.Controls
        If ctl.Tag = "REQ" Then
            If ctl.Tag & "" = "" Then
                Msgbox ctl.Caption & " is required.  Please enter a value.",vbOKOnly
                ctl.SetFocus
                CheckForEmpty = True
                Exit Sub
            End if
        End If
   Next ctl
   Set ctl = Nothing
Exit Sub

Select all Open in new window

Pat, To explore all avenues I tried as nick suggested and Split the conditions into individual If statements and put that validation in the  BeforeUpdate event and the Save and New record code on the On Click event of the save button, if a control is blank it fires the validation with a message identifying the blank control, but it throws a Debug Error 2501 (the run command was canceled) in procedure btnSave_click at that point you have to click OK on the error it will then put focus on the blank control and after adding data it will save. However I can't have that error in production.

Save button

Private Sub btnSave_Click()
'Save the record.
   On Error GoTo btnSave_Click_Error

    RunCommand acCmdSaveRecord
    'Load a new record after the save.
    DoCmd.GoToRecord , , acNewRec

   On Error GoTo 0
   Exit Sub

btnSave_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC"
End Sub

Select all Open in new window

Before Update

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Validates that Control has Data for required feilds
If Len(Firstname) = 0 Or IsNull(Firstname) = True Then
  Call MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
  Me.Company.SetFocus
  Me.Firstname.SetFocus
   Cancel = True
    Exit Sub
End If
If Len(Lastname) = 0 Or IsNull(Lastname) = True Then
  Call MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
  Me.Company.SetFocus
  Me.Lastname.SetFocus
   Cancel = True
      Exit Sub
End If
If Len(Address1) = 0 Or IsNull(Address1) = True Then
  Call MsgBox("You must enter Address1", vbExclamation, "Add Address1")
  Me.Company.SetFocus
  Me.Address1.SetFocus
   Cancel = True
      Exit Sub
End If
If Len(City) = 0 Or IsNull(City) = True Then
  Call MsgBox("You must enter City", vbExclamation, "Add City")
  Me.Company.SetFocus
  Me.City.SetFocus
   Cancel = True
     Exit Sub
End If
If Len(State) = 0 Or IsNull(State) = True Then
  Call MsgBox("You must select a State", vbExclamation, "Add State")
  Me.Company.SetFocus
  Me.cboState.SetFocus
   Cancel = True
      Exit Sub
End If
If Len(Zip) = 0 Or IsNull(Zip) = True Then
  Call MsgBox("You must enter a Zip", vbExclamation, "Add Zip")
  Me.Company.SetFocus
  Me.Zip.SetFocus
   Cancel = True
      Exit Sub
End If
If Len(Phone) = 0 Or IsNull(Phone) = True Then
  Call MsgBox("You must enter a Phone No", vbExclamation, "Add Phone")
  Me.Company.SetFocus
  Me.Phone.SetFocus
   Cancel = True
      Exit Sub
End If
If Len(cboCSR) = 0 Or IsNull(cboCSR) = True Then
    Call MsgBox("You must select a CSR", vbExclamation, "Add CSR")
    Me.Company.SetFocus
    Me.cboCSR.SetFocus
   Cancel = True
      Exit Sub
End If
If Len(cboMod) = 0 Or IsNull(cboMod) = True Then
    Call MsgBox("You must select a Model", vbExclamation, "Add Model")
    Me.Company.SetFocus
    Me.cboMod.SetFocus
   Cancel = True
      Exit Sub
End If
If Len(Serial) = 0 Or IsNull(Serial) = True Then
    Call MsgBox("You must select a Serial Number", vbExclamation, "Add Serial")
    Me.Company.SetFocus
    Me.Serial.SetFocus
    Cancel = True
       Exit Sub
End If
If Len(ProblemDesc) = 0 Or IsNull(ProblemDesc) = True Then
    Call MsgBox("You must select a Problem Description", vbExclamation, "Add ProblemDesc")
    Me.Company.SetFocus
    Me.ProblemDesc.SetFocus
    Cancel = True
       Exit Sub
End If
End Sub

Select all Open in new window

Debug Error 2501 (the run command was canceled)
However I can't have that error in production.


Since that is both an expected and desired error -- you did cancel the action -- handle it

btnSave_Click_Error:
    if err.Number = 2501 then exit sub
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC"

OK... Nick, after adding that line of code it doesn't Error when the control is blank but when data is added and the save button is click the record saves but another error pops up "Error 0 () in procedure btnSave_click  of VBA Document Form_ISSC" so I commented out the  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC" section of btnSave_Click_Error: and that stopped that. Was that the correct procedure.

@skull52

did you try the codes I posted at http:#a40631048

after adding that line of code it doesn't Error when the control is blank
A bit loose in the wording
The btnSave_Click() routine is suppose to attempt a Save.
That forces a BeforeUpdate event
If the code in BeforeUpdate causes the save to cancel, an error will cascade back to btnSave_Click()
That should be the 2501 error and it is now handled by silently exiting
if err.Number = 2501 then exit sub
Is that a proper description of what happens with an empty control?

but when data is added and the save button is click the record saves but another error pops up "Error 0 () in procedure btnSave_click  of VBA Document Form_ISSC"
Error 0 is indicating success, so that too, we'd like to handle with a silent exiting

In general, with an error handler, you'd like the code to specify what will occur with the errors that occur
A Select Case True structure is brilliant for this:

btnSave_Click_Error:
Select Case True
    Case err.Number = 0 'no error, just let the sub end
    Case err.Number = 2501 'BeforeUpdate was cancelled an a msgbox generated, let this sub end
    Case else 'something we haven't encountered yet has occurred.  Msgbox it
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_ISSC"
End Select

so I commented out...
Was that the correct procedure.

Not really.
That gutted your error handler
In effect, all errors silently fail when you do that

OK... through testing the various solutions I have decided the best for this application is to go with discrete blocks and exit subs as Nick suggested as I want to annoy the user with popups of each control left empty, and to use the Select Case True structure for error handling, again I thank everyone for their suggestions.

For what it is worth,
I go with the Select Case True structure for my validating, instead of If Thens

Select Case True
Case  Len(nz(Firstname,0)) = 0 'no valid firstname
  MsgBox("You must enter First Name", vbExclamation, "Add Firstname")
  Me.Firstname.SetFocus
Case Len(nz(Lastname,0)) = 0 'no valid lastname
  MsgBox("You must enter Last Name", vbExclamation, "Add Lastname")
  Me.Lastname.SetFocus
....
Case Else 'nothing unhappy happened, save and advance
    'Save the record.
    RunCommand acCmdSaveRecord
    'Load a new record after the save.
    DoCmd.GoToRecord , , acNewRec
End Select

A single nicely-readable conditional walks once down the possibilities of things gone wrong, and if none are found, executes what needs doing.

Select Case True is very powerful.
Any number of cases that evaluate to a Boolean can be constructed and they test in order.
The first one that evaluates true has its code execute and then the structure exits.
It removes the need for the exit sub statements and about a third of the code length, as the closing end ifs aren't needed, either.

But at the beginning, I didn't know if you would want a Select Case true, understand it, or want to refactor the Ifs that much.  Since I discovered it, I don't think I've used an ElseIf since.
And since you can put Ifs in the code that executes for a true case, it can make for a lot cleaner and more readable code than nested ifs ever could.

YMMV
Nick67

Yeah... I like it. I use case statements in my MSSQL codes a lot, didn't know I could use them in Access as well,  I will rewrite my validation statements to use that, thanks.

Nick would I put this on the on_click event of the save button or on the beforupdate event?

As you are coming to see, there are questions of style in how a user data entry form gets created.
The challenge is 'how do I keep bad data from being entered'
@ PatHartman denotes one style
Your save button should force the record to save and that will fire the FORM's BeforeUpdate event and the FORM's BeforeUpdate event is where the validation code actually belongs.  That way, no matter what the user does, the code will fire if Access decides it needs to save the current record.


As you have seen, trying to keep the user from experiencing any VBA-generated errors when you go that route is not intuitive (not to me anyway.)  Many things can set the BeforeUpdate event off, and when the code in BeforeUpdate enacts a Cancel, it throws the error back to whatever set it off -- and then you have to find all those spots and build error handlers, or have a generic error handler up and working, and it ALL get bloody complex in a hurry.

I don't care for that.

What I typically do is put unbound controls on the form for the user to pick, select or enter data.  My command button validates that the data is good and writes the records.  This is kick-ass for those multi-table situation where the order that the records get created in matter for referential integrity.  I can also ask the user if they are certain before committing.
And the data entered in the unbound controls -- well, its unbound!  So if the user exits the form, or navigates or the power fails, or whatever, no bad data is partially entered.  This allows the user to bail out AFTER they have started entry without consequences.  That's hard to do otherwise.  Data either gets pounded in by MY coding, after validating, or not at all.  This is more work to set up, but I think it makes for a better user experience AND  bulletproof data.  The unbound controls I put in the form header.  Below I put a subform showing the committed records for viewing and possible editing.

Nick would I put this on the on_click event of the save button or on the beforupdate event?

I would put the code validating in its own function that returns a Boolean
In the function, set it as false at the beginning and true in the Case Else
You can then call the code from BOTH the button and the BeforeUpdate
The button will run things nicely and error-less as you intend and the users will gravitate to that.
The BeforeUpdate may toss some unhappiness -- and the users will quickly learn not to do anything but use your button.

Does that make sense?

I guess it is a matter of style.  Somehow, I have no trouble using bound forms and the BeforeUpdate event to validate data.  If you want the user to be able to bail without saving, teach him how to do it using the esc key or the undo or give him a button.  The way I look at it, I've written my million lines of code so I don't need the practice.  That is one of the best things about Access.  Once you understand how events and properties work, you can save yourself a ton of code.  I once took 1500 lines of code that attempted to ensure fields were not null and turned them into 30 that were called from the correct event.  Notice I said attempted.  The programmer had code in every conceivable event EXCEPT the correct one - the BeforeUpdate event and bad records were being saved regularly because of it.

<earnest and heartfelt>
@PatHartman
I invited the discussion.
I cannot provide what I have never been able to certainly ascertain.
Which is -- how in the name of the seven bells of hell do I ABSOLUTLEY, POSITIVELY ensure that no VBA error gets to the user if I code up a BeforeUpdate event.
You clearly know how to get that done.  I don't.  So teach me and skull52 at the same time, please.
Doesn't mean I'll use bound controls for data entry, but it would be nice to have that tool in the shed.
I don't at the moment

If you want the user to be able to bail without saving, teach him how to do it using the esc key or the undo or give him a button.
Excuse me while I ROFLMAO for a moment.  If that could be managed, it'd be one of the great miracles of modern life.
I went the direction I did precisely because that task was so frustrating.

Once you understand how events and properties work, you can save yourself a ton of code.
I understand BeforeInsert and BeforeUpdate well enough.
I've never been able to trap the errors that a Cancel=True generates to my satisfaction -- which is to say, 100% of the time.
So I went a different path.  I don't use them, and they don't generate errors 100% of the time.
And while me.undo is a nice idea, my users were pissed off when ALL the stuff they entered disappeared.
And they were pissed when they would try to close an object and instead be trapped on it.
Or close the whole app and be trapped in it
They had UI expectations that the 'x' in the upper corner would close something
And they expected that 'x' to be there
And some of them knew ctrl-alt-delete | task manager well enough not to take any crap from the app.

So, it is a matter of style, and I have acknowledged that you have a style that you have mastered well.
If you could provide a roadmap about how to avoid all the pitfalls in your style, I'd be grateful.
</earnest and heartfelt>

Nick67