Choosing when to save a record?

[Pat Dools]

Hello,

In my database, I have some code that calls the next form in a series of
forms, and auto-populates four header fields to ensure that the data entry
person remains on the same patient record whether they are entering a new
record, or doing QC for an existing record (see code):

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
Else
Call LockControls(Me)
End If
End Sub

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!studyday = Forms!fScrEligCriteria!studyday
!id = Forms!fScrEligCriteria!id
!ptin = Forms!fScrEligCriteria!ptin
!site = Forms!fScrEligCriteria!site
End With

SetAutoValues_err:
'MsgBox Err.Description
Resume Next

End Sub

Public Sub LockControls(frm As Form)
On Error Resume Next
Dim ctl As Control

For Each ctl In frm.Controls
With ctl

Select Case .ControlType
Case acTextBox
ctl.Locked = True

Case acComboBox
If ctl.Tag = 2 Then
ctl.Locked = False
Else
ctl.Locked = True
End If '(etc., etc. thru all Access' different types of
controls)

In some cases, the data entry person won't have all the forms in the series
at the same time, so if they progress to the next form in the series, and
they don't have a hard-copy data entry sheet for it, I want to give them a
button that prompts them to save, and if they choose 'Yes', then Save record
& Close form, and if they choose 'No', then just Close form. I put this code
behind a Command Button:

Private Sub Command83_Click()
If MsgBox("Would you like to save this record?", vbYesNo) = vbYes Then
DoCmd.Save
DoCmd.Close
Else
DoCmd.Close
End If
End Sub

The problem is that the record on the active form is saved regardless of
whether the user chooses 'Yes' or 'No'. Is there a form setting or something
I can adjust so that the record is saved only when the user chooses 'Yes' in
this scenario?

Thank you.

 

[Arvin Meyer]

The Access bound form model automatically saves a record the moment focus
moves off of it. There are 2 methods you can use to avoid that. The first is
to use unbound forms, and write the record in code whenever you click on the
save button. The second is to go ahead and let it save,, then delete the
record if you don't want it.

You can also leave the record the way it is and add or delete at a later
date.

The methodology you choose should depend upon what percentage of the records
you have in any of the categories.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Pat Dools]

Hi Arvin,

Do you have a sample of some code to write the record once a 'Save' button
is clicked? Also, if the form is 'unbound', is it the code that writes the
record to the desired table? Forgive me, I've never worked with unbound
forms before.

Thanks.

 

[Arvin Meyer]

There are multiple ways. You can write a recors usinf the AddNew method in
DAO, or you can use:

DoCmd.RunSQL "Insert into tblWhatever ..."

or CurrentDb.Execute "Insert into tblWhatever ..."

or

DoCmd.OpenQuery "qryAppendQueryName"
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access