Record data on form to table only when hitting the "record button"

[Jimmy Shoe]

Hi,



I am new to Access and don't have any SQL experience. I have a form to input some information and save it to a table using a "Record" button. Is there an easy way to only add the information to the table by clicking the record button and not by, for example closing the form?

Thanks

[Rawb]

By default, Access will save any new Records (or any changes to existing Records) as soon as you "leave" the Record. This means that if you browse to a new Record on the Form, it will automatically save changes for you.

If your Form only allows you to view a single Record at a time, or if you have special requirements, then you can create a button with the following command in it's On Click Event to force Access to save the Record immediately:

DoCmd.RunCommand acCmdSaveRecord

[Missinglinq]

I believe Rawb misunderstood your intent

...Is there an easy way to only add the information to the table by clicking the record button and not by...closing the form

To circumvent the default behavior of saving a Record when the Record is moved off of or when closing the Form, you'd have to use an Unbound Forms, and this is not really something for the inexperienced developer to attempt. Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That's because with Bound Forms, Access takes care of the 'heavy lifting,' but with Unbound Forms, the developer has to write code to take care of just about everything.

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

The question here is "Why?" Why do you not want Access to automatically save the Record by default? There's nothing that can be done using Unbound Forms that cannot be accomplished with Bound Forms, and usually accomplished with less work!

Linq ;0)>

[Rawb]

Whoops, you're right Linq, I completely missed what was being asked!

However, there is another option: Create a temporary Table with the same Fields as your actual Table and bind the Form to it. Then make a Button that copies the record(s) from the temporary Table to the actual Table when clicked. This will only work if you don't want to be able to use the same Form to browse/update Records though - you would only be able to create new Records with the Form.

You might also try setting up an On Close Event (and the On Current Event) in the Form to check if Me.Dirty = True and, if so, reset the Record. You should be able to do this by looping through the Fields and setting their .Value Properties to match their .OldValue Properties.

Dim objControl As Object

If Me.Dirty = True Then
  On Error Resume Next ' Not all controls are for data entry, so they won't all have .Value or .OldValue!

  For Each objControl in Me.Controls
    objControl.Value = objControl.OldValue
  Next

  On Error GoTo 0 ' Turn error checking back on
End If

Set objControl = Nothing

[Missinglinq]

A valid approach, given a valid reason! But such reasons are seldom presented, here. And given the Op's statement

...I am new to Access and don't have any SQL experience...

this kind of thing is almost sure to be outside of Jimmy's skills set, requiring a lot of coding.

Linq ;0)>

[Jimmy Shoe]

Thanks to both of you. The form I have set up is used only to add new records and not edit/view but I am not sure how to set up a temporary table. I tried adding the code for the Me.Dirty = True on the two events but it doesn't seem to work. is there anything in the code I would need to change to suit my form?

[Missinglinq]

There is no easy way to do this, no line or two of code that's going to work. We're still waiting to hear why you want to circumvent the normal behavior in an Access Bound Form, and make life more difficult for yourself in the process.

Linq ;0)>

[Jimmy Shoe]

To prevent incomplete records being added because a user decides to stop half way through and close the form.

[Missinglinq]

So ask the user if they want to save the New Record or save the changes to an existing Record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
  If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
   Me.Undo
  End If
Else
  If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
   Me.Undo
  End If
End If
End Sub

If you have specific Fields that have to be populated, to constitute a 'complete Record,' you can also use the Form_BeforeUpdate event to check that these are, in fact, populated, and if they're not, don't allow the Record to be saved.

Linq ;0)>

[Jimmy Shoe]

Thanks Linq, that is a perfect solution!

[Missinglinq]

Glad we could help!

Linq ;0)>