Full Version: Insert
UtterAccess Forums > Microsoft® Access > Access Forms
mike60smart
Hi Everyone

I am trying to use the following code in the On Click event of a Subform to add a record to a table and refresh another subform.

When I run the code I get the following error:-

Click to view attachment


Hit Debug and it highlights this line:-

Click to view attachment

Any help appreciated.

the Code is:-

CODE
'Insert a new position into tblEmployeeLevel
If Me.txtTR = 4 And Me.txtNotice = "All Tasks Completed" Then

Dim dbsMydbs As DAO.Database
    Dim rstMyTable As DAO.Recordset
    Me.Dirty = False
        Set dbsMydbs = CurrentDb
        Set rstMyTable = dbsMydbs.OpenRecordset("tblEmployeeLevel")
        With rstMyTable
            .AddNew
            !PosID = 4
            !DateAchieved = Date
            .Update
        End With

    MsgBox "New Position has been added.", vbInformation, "Complete"
Else
    MsgBox "No New Positions have been added.", vbInformation, "InComplete"

End If
        rstMyTable.Close
        Set rstMyTable = Nothing
        Set dbsMydbs = Nothing

[Forms]![frmEmployees]![frmEmployeeLevelSubform].[Form].Requery


DanielPineault
Well depending on the If statement result, you can never open the recordset, thus the error. I'd do something more along the lines of

CODE
Sub YourProcName()
      On Error GoTo Error_Handler
      Dim dbsMydbs              As DAO.Database
      Dim rstMyTable            As DAO.Recordset
  
      'Insert a new position into tblEmployeeLevel
      If Me.txtTR = 4 And Me.txtNotice = "All Tasks Completed" Then
          If Me.Dirty = True Then Me.Dirty = False
          Set dbsMydbs = CurrentDb
          Set rstMyTable = dbsMydbs.OpenRecordset("tblEmployeeLevel")
          With rstMyTable
              .AddNew
              !PosID = 4
              !DateAchieved = Date
              .Update
          End With
  
          MsgBox "New Position has been added.", vbInformation, "Complete"
  
          [Forms]![frmEmployees]![frmEmployeeLevelSubform].[Form].Requery    'Only requery if new entry is made, no?
      Else
          MsgBox "No New Positions have been added.", vbInformation, "InComplete"
      End If
  
  Error_Handler_Exit:
      On Error Resume Next
      If Not rstMyTable Is Nothing Then
          rstMyTable.Close
          Set rstMyTable= Nothing
      End If
      If Not dbsMydbs Is Nothing Then Set dbsMydbs = Nothing
      Exit Sub
  
  Error_Handler:
      MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
             "Error Number: " & Err.Number & vbCrLf & _
             "Error Source: YourProcName" & vbCrLf & _
             "Error Description: " & Err.Description & _
             Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
             , vbOKOnly + vbCritical, "An Error has Occured!"
      Resume Error_Handler_Exit
  End Sub
mike60smart
Hi Daniel

Tried that and now I get the following error:-

Click to view attachment
DanielPineault
Sorry, my generic code sample that I forgot to do a replace in. I've updated the original code, so it should be good now.
mike60smart
Hi Daniel

Works a treat

Many Many thanks yet again
cheers.gif
DanielPineault
Glad I could help.




One little side note, if you are running this through a form, or even a subform, whose recordset is already tblEmployeeLevel you could just as easily add the new record through the form rather than opening a recordset and then needing to requery the form, something like:

CODE
Sub YourProcName()
    On Error GoTo Error_Handler

    'Insert a new position into tblEmployeeLevel
    If Me.txtTR = 4 And Me.txtNotice = "All Tasks Completed" Then
        DoCmd.GoToRecord , , acNewRec
        Me.PosID = 4
        Me.DateAchieved = Date

        MsgBox "New Position has been added.", vbInformation, "Complete"
    Else
        MsgBox "No New Positions have been added.", vbInformation, "InComplete"
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: YourProcName" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub


Just food for thought depending on the scenario.


mike60smart
Hi Daniel

Many thanks for the update

This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.