Results 1 to 11 of 11
  1. #1
    Join Date
    May 2010
    Posts
    67

    Unanswered: Why does my Form automatically store a new record without a INSERT statement

    Can someone tell me why my form automatically stores a record without an explicit INSERT statement.

    I am testing my code, I have a ADD button to add a record to the database. I am testing the code that validates and edit the data. I have not added the INSERT statement as of yet, but the test record is ADDED anyway.

    Why is ACCESS VBA adding the record?

    Thank you in advance!

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    Can someone tell me why my form automatically stores a record without an explicit INSERT statement.

    I am testing my code, I have a ADD button to add a record to the database. I am testing the code that validates and edit the data. I have not added the INSERT statement as of yet, but the test record is ADDED anyway.

    Why is ACCESS VBA adding the record?

    Thank you in advance!
    Are you trying to make a bound or unbound form?

    Is the Form's record source property set to something?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2010
    Posts
    67
    The form is bound to a database record and the record source is set to a database record.

    Question: So, if the form is bound to a database record, I can not control the INSERTION of records into the table?

    Should I unbound the controls(fields) on the form?

    Your suggestions are truly valued!

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    The form is bound to a database record and the record source is set to a database record.

    Question: So, if the form is bound to a database record, I can not control the INSERTION of records into the table?

    Should I unbound the controls(fields) on the form?

    Your suggestions are truly valued!
    I almost always recommend using bound forms.

    You can control the insertion of a record. I like to use the before update event.

    Here is VBA code for the Before Update event using the Customer form in the NorthWind example database

    Code:
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
     Cancel = False
    
    
    ' perform data validation
    If IsNull(Me.CompanyName) Then
    
       MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."
          
       Cancel = True
    
    
    End If
    
    
    If Not Cancel Then
      ' passed the validation process
    
        If Me.NewRecord Then
            If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
                Cancel = True
            Else
                ' run code for new record before saving
            
            End If
        
        
        Else
            If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
                Cancel = True
            Else
               ' run code before an existing record is saved
               ' example: update date last modified
                
            End If
        End If
    
    End If
    
    
    ' if the save has been canceled or did not pass the validation , then ask to Undo changes
    If Cancel Then
    
        If MsgBox("Do you want to undo all changes?", vbYesNo, "Confirm") = vbYes Then
            Me.Undo
    
        End If
        
    End If
    
    
    
    End Sub

    Hope this helps ...
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    Can someone tell me why my form automatically stores a record without an explicit INSERT statement.
    When you navigate from a record, it is automatically saved.

    If you want to use a "Save" button, then all you need to do in that button is something like this:

    Code:
    Private Sub cmdSave_Click()
    On Error GoTo Err_cmdSave_Click
    
    
        If Me.Dirty Then
           DoCmd****nCommand acCmdSaveRecord
        End If
    
    
    Exit_cmdSave_Click:
        Exit Sub
    
    Err_cmdSave_Click:
    
      ' check to see if the Before Update event canceled the save
        If Err.Number <> 2501 Then
            MsgBox Err.Description
        End If
        Resume Exit_cmdSave_Click
        
    End Sub
    All the valicdation is done in the Before Update event.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  6. #6
    Join Date
    May 2010
    Posts
    67
    Thanks again HiTechCoach

    Your advice makes a lot of sense.

    As you are aware, I am learning as I go. I am still coding like a COBOL coder. I have a VB book, but this site is wonderful for persons new to ACCESS and Visual Basic, like myself.

    More questions may follow.

    Again, thank you for your help!

  7. #7
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    Thanks again HiTechCoach

    Your advice makes a lot of sense.

    As you are aware, I am learning as I go. I am still coding like a COBOL coder. I have a VB book, but this site is wonderful for persons new to ACCESS and Visual Basic, like myself.

    More questions may follow.

    Again, thank you for your help!
    I also came from COBOL to Windows programming. It was a huge leap to change my thinking to events and objects.

    With Access you do not usually have to write lots of code. I like to say I "sprinkle code in events".

    What really helped me was to learn the order events fire. Once you understand what causes different events to fire, it makes a lot more sense where to "sprinkle" some code.

    Look for the Access Object Models at Microsoft's site.

    I had an example about events and when they fire. I will look for it.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    May 2010
    Posts
    67
    I will read up on Access Object Modules and I would appreciate any ACCESS and VB references you know about.

    Thanks!

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    I will read up on Access Object Modules and I would appreciate any ACCESS and VB references you know about.

    Thanks!
    A great resource is the book Access Developer's Handbook. Even the Access 2000 version of the Handbook has lots that still apply to all the later versions.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    May 2010
    Posts
    67
    HiTechCoach

    Is Access Developer's Handbook you recommend...authors Litwin, Getz, Gunderloy?

    The book link below:
    Amazon.com: Access 2002 Developer&#39;s Handbook Set (0025211440117):…

    The latest version is 2002, even though I am working in MS 2007 will it fit most of my programming needs?

    Thanking you is advance!

  11. #11
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by eva.thaeh View Post
    HiTechCoach

    Is Access Developer's Handbook you recommend...authors Litwin, Getz, Gunderloy?

    The book link below:
    Amazon.com: Access 2002 Developer's Handbook Set (0025211440117):…

    The latest version is 2002, even though I am working in MS 2007 will it fit most of my programming needs?

    Thanking you is advance!
    Yes, that is the one!

    Everything in 2002 does apply and can be used in 2007. Access 2007 is suposed to be totally compatible with 2002 and run all the 2000/2002/2003 databases.

    The only thing major new feature that it will no cover is the new Ribbon.

    It does do a great job of explaining events. It has a great example database that you can run to see events firing. IIRC, it is in Chapter 2.

    If I could have only one Access book, this is the one I would pick.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •