Save button on Form

[pasin]

Hello everybody.
I have a form with a subform. The subform is a datasheet. I am trying to put a SAVE button on form ,so that records entered in subform are saved only when you click the button on the form.
I have create a hidden text field in the subform called buli which holds true or false and the code is below:

Private Sub Form_Current()   'çode on subfrom
Me.buli.Value = False
Me.buli.Properties("ColumnHidden") = True

End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)  'çode on subform

 If Me.Dirty Then
If Me.buli.Value = False Then

    Cancel = True
    Me.Undo
 
    Cancel = False
End If
End If

End Sub


Private Sub btnSave_Click()    'code on form

    Forms!frmData!frmDataSubform.Form!buli.Value = True

    Me.frmDataSubform.SetFocus

      DoCmd.RunCommand acCmdSaveRecord
      DoCmd.GoToRecord , , acNewRec
     
    MsgBox ("Data saved")
    Forms!frmData!frmDataSubform.Form!buli.Value = False
    End Sub

The code is preventing autosave but when I click save button it is not saving anything.
It looks like the problem is that as soon as I click the button, the before update event is called before starting to execute button click lines of code where buli becomes true, so buli remains false when entering before update sub. Please your suggestions

 

[isladogs]

Why not add an Else clause in the form current event so that if 'buli' is true then you save by setting me.dirty =false

Also, welcome to the forum

 

[arnelgp]

you need Transaction in order to accomplish that.
once you leave the subform and click anywhere on the mainform, the records are automatically saved on the subform.

 

[pasin]

Actually nothing is saved at all, even if you click outside or the save button

 

[arnelgp]

nothing is saved because of your BeforeUpdate code. you are Undoing it.

 

[pasin]

The start and commit transaction must be coded on the form or on the subform?

 

[arnelgp]

see this sample db especially the form with Subform.
see the Class module.
see the code behind the main form.

add records to subform, delete some records then press the discard button.
do same as above but this time press the save button.

close the form and re-open it, does it save the changes?

 

[pasin]

Thank you very much. It works. But I am not sure why I am getting a problem. The form has some combo box . When they are filled , the subform shows related records based on combo choosen values. Now that I put your code , when I choose combo box values , they are no reflected on the subform datasheet and nothing is displayed there. looks like your code is stopping query on the subform. Looks like the problem is in this line of code

Set .Recordset = OpenRS(strRecordSource)

If i remove that line, combo selections are reflected innsubform. But if Close the form with X icon records are saved even if I don't click on SAVE

 

[arnelgp]

upload a zip sample db and ill take a look.

 

[pasin]

Please click on DATA when you open Form.
Thanks

upload a zip sample db and ill take a look.

 

[arnelgp]

ok i see the problem, remove the Unload event of the Main form.

 

[pasin]

I removed the Unload event on main form but I also have to comment the line of code I mentioned above in order for the combo query to work.
When I click the X icon , while I filled one record with data , I get a message Do you want to commit all changes? I click no but the record added is saved

ok i see the problem, remove the Unload event of the Main form.

 

[arnelgp]

check the code in the main form.
test if it save even if youre answer is No.

 

[pasin]

Thanks,
It is working now for the save issue, but there are some calculation columns that now are not working like Tot_lab. It is showing #Name? Also I had a total row in the end of the records that is not working too.
If you could check also this issue, you'd help me a lot.

check the code in the main form.
test if it save even if youre answer is No.

 

[arnelgp]

What is your formula for tot_lab?

 

[pasin]

It is summing column Lab1 and Lab2. So there is a total lab for every row. You can see it in the subform Data Tab where is the sql created.

What is your formula for tot_lab?

 

[arnelgp]

here is your db. see the changes i make on the vba.

 

[pasin]

As soon as I click on Data button I get an error : The expression you entered refers to an object that is closed or does not exist. The error refers to line

TransForm.Init Me.frmDataSubform.Form, SUB_RECORDSOURCE & " WHERE (1=0);"

here is your db. see the changes i make on the vba.

 

[arnelgp]

i have to compact and repair

 

[pasin]

Thank you so much, it is working like a charm. One last question. When I fill all combo boxes with a value from the drop down. If i go again to the last dropbox and I want to select another value, it is showing no values at all, just blank. I am sure I am doing sth wrong> pleas if you have a suggestion for this also

i have to compact and repair