Forms not able to insert new record (1 Viewer)

June7 said:

Wait, why should all comboboxes be UNBOUND? Only Category boxes need to be UNBOUND. Comboboxes are working fine.

Also, code worked without all those additional Exit Sub. When an If condition is met, that segment runs then execution drops out to End If. It's placement of GoToRecord and funky error handling that is issue.

Click to expand...

Private Sub cmdSaveRecord_Click()
.

.

.

On Error GoTo NewRecord_Err
    
    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    
NewRecord_Exit:
    Exit Sub
    
NewRecord_Err:
    Beep
    MsgBox Error$
    Resume NewRecord_Exit
    
    Exit Sub
    
errHandler:
    
    MsgBox Err.Number & ": " & Err.Description

End Sub

missinglinq said:

When validating multiple fields, you have to Exit the Sub each time a validation fails, giving the user a chance to correct errant Control, before moving on to the next validation check...otherwise the code simply 'drops thru' to the next check!

Also, your Comboboxs are all Bound (have Control Sources) and when used like this, to populate Fields in the Form's underlying Table, should be Unbound. Delete the Control Sources for each cbo.

Now, try replacing the validation portion of your code like this...and see what happens (copy your current code and save it somewhere safe!!!)

Code:Copy to clipboard

Private Sub cmdSaveRecord_Click()


      
 If Len(Me.Customer & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter company name."
 
      Me.Customer.SetFocus
      [COLOR="Red"]Exit Sub[/COLOR]
 
 ElseIf Len(Me.Duration & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter duration."
 
      Me.Duration.SetFocus
      [COLOR="Red"]Exit Sub[/COLOR]

 ElseIf Len(Me.ContactPerson & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter contact person name."
 
      Me.ContactPerson.SetFocus
      [COLOR="Red"]Exit Sub[/COLOR]
 
 ElseIf Len(Me.ContactNo & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter contact number."
 
      Me.ContactNo.SetFocus
      [COLOR="Red"]Exit Sub[/COLOR]
      
 ElseIf Len(Me.Email & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter email address."
 
      Me.Email.SetFocus
      [COLOR="Red"]Exit Sub[/COLOR]

ElseIf Len(Me.Item1 & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter item code."
 
      Me.Item1.SetFocus
      [COLOR="Red"]Exit Sub[/COLOR]
      
 ElseIf Len(Me.Desc1 & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter description."
 
      Me.Desc1.SetFocus
      [COLOR="Red"]Exit Sub[/COLOR]
      
 ElseIf Len(Me.Rate1 & vbNullString) = 0 Then
 
      ' Alert the user.
      MsgBox "Please enter rates."
 
      Me.Rate1.SetFocus
      [COLOR="Red"]Exit Sub[/COLOR]

Else

[COLOR="Red"][B]DoCmd.RunCommand acCmdSaveRecord[/B][/COLOR]

..............

Notice that I've also saved the Record, as June7 suggested, and I'm not really sure what you're doing with the error handling you're attempting. You're using an Error Handler that is only set after all of the Sub has run (handlers should be set directly under the Sub's name) and you're using both an EH and On Error Resume Next. I'd comment out all of this code, for now.

Also, note that the user will have to click on the 'Save' button again, after correcting each errant Control.

Linq ;0)>

Click to expand...

June7 said:

Multiple similar name fields indicates a non-normalized data structure - more like a spreadsheet. And now I see you are exporting data to Excel. Are you saying the export fails? Would have been helpful to provide workbook as well. However, I created a workbook with the name used in code and no problem running. Data is exported.

Those Cancel = True lines are meaningless in button click event.

You have more required fields than code validates and record cannot be saved until all are filled in. Because of error handler code, users don't get prompted for missing data. Shouldn't you make sure record is committed before exporting to Excel?

Click to expand...

June7 said:

The fields are set as required in table. This will prevent saving record until those bound controls have data.

Click to expand...