CODEWITHSUNDEEP
ms-accessvbams-access-2010
JonBlumfeld
JonBlumfeld

Reputation: 399

Access 2010 VBA manually save record

an Access question this time. I have a form that is bound to a certain table and I want this form to only allow adding new entries (not editing or deleting) by clicking on a "Save" button. First problem was that the record was updated while editing the textboxes. The Solution I have has several problems

Option Compare Database
Option Explicit

Private bSaveRecord As Boolean

Private Sub btCreateRecord_Click()
   bSaveRecord = True
   Me.tblUMgmtUser_UserDetailsID.Value = Me.tblUMgmtUserDetails_UserDetailsID.Value
   Me.tbSetUserHashPW = "12312"
   Me.cbSetInitPW = True
   DoCmd.GoToRecord , , acNext
End Sub

Private Sub btResetRecord_Click()
    ResetRecord
End Sub

Private Sub Form_AfterUpdate()
    bSaveRecord = False
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not bSaveRecord Then
        Cancel = True
        Me.Undo
    End If
 End Sub

Private Sub Form_Load()
    Me.Username.SetFocus
    DoCmd.GoToRecord , , acNewRec
    bSaveRecord = False
End Sub

 Private Sub ResetRecord()
    Dim cControl As Control

    bSaveRecord = False
    For Each cControl In Me.Controls
        If cControl.Name Like "Text*" Then cControl = vbNullString
    Next
    Me.cbResponsible.Value = False
    Me.Undo
End Sub

Problem 1: I have to add hidden textboxes to save values to the record I want to be generated automatically

Problem 2: the ID-column counts up every time I open the form, even if I haven't added a record previously

Generally spoken my solution doesn't feel very robust and elegant. Any suggestions are greatly appreciated

Many thanks Jon

Upvotes: 1

Views: 11264

Answers (2)

hoopzbarkley
hoopzbarkley

Reputation: 89

This is not my solution, but it allows you to use bound fields while preventing them from automatically updating.

http://bytes.com/topic/access/insights/891249-how-stop-bound-forms-updating-automatically

Upvotes: 1

PowerUser
PowerUser

Reputation: 11791

You are binding the data entry form directly to the target table. As a result, when the user enters data, they edit the table directly including creating new records. This is why the ID auto-increments (because the user is immediately creating new records). Your current partial work-around is to use hidden textboxes to store the values.

Your goal of a data entry form sounds like a common scenario. Try this:

  1. First, do NOT set your target table as your form's source. Leave the source blank for now and all your fields unbound. Since they are now separate, the user can't edit existing records or add new ones just by entering data. In fact, nothing will happen if the user just enters data and then closes the form.

  2. Go ahead and make your Save button. This button will validate the data to make sure it is what you want, then create and execute a SQL Insert query to add the data to your target table.

Upvotes: 1

Related Questions