Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Forms (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=9)
-   -   Don't save form until record is saved on second form (https://www.access-programmers.co.uk/forums/showthread.php?t=304259)

Zydeceltico 03-14-2019 03:42 PM

Don't save form until record is saved on second form
 
Hi All -

I have a main form, frmInspectionEvent that has some buttons that open various types of inspection forms - each bound to a different table. The Main form is bound to its own table.

The various types of Inspection forms have "Save Record" buttons on them. The Main form does not.

And the user has to enter specific data in specific controls on the Main form to even be able to open one of the various types of inspection forms.

As it stands, I go through the process of entering data on the Main form, open a secondary form and begin entering data on it - - then I say to myself "Whoops - I opened the wrong form" and I click my Undo button and close without saving - -

However, a record has been created in the table bound to the Main form.

Is there a way I can prevent this from happening and have no records created in any table until the "Save" button is clicked on the second form?

Thanks!

Tim

theDBguy 03-14-2019 04:59 PM

Re: Don't save form until record is saved on second form
 
Hi Tim. It depends. Can you please show us the code you’re using to open the various inspection forms? Thanks.

Zydeceltico 03-14-2019 05:08 PM

Re: Don't save form until record is saved on second form
 
Quote:

Originally Posted by theDBguy (Post 1615413)
Hi Tim. It depends. Can you please show us the code you’re using to open the various inspection forms? Thanks.

This is it. Pretty Simple.

Code:

Private Sub cmdOpenMillInspection_Click()

If fChkCombo = False Then Exit Sub

  Me.Dirty = False
Me.InspectionEvent_PK
    DoCmd.OpenForm "frmInspectMill", , , , , acDialog, Me.InspectionEvent_PK
   
End Sub

fChkCombo simply checks that specific combo boxes on the Main form have data added to them before one of the various secondary forms can be opened.

So - yes - I have to enter data on the main form before opening one of the secondary forms. I would like to "hold" the data in the first form until I click Save on the second.

theDBguy 03-14-2019 05:10 PM

Re: Don't save form until record is saved on second form
 
Okay, no promises but try commenting out the line: Me.Dirty=False

Zydeceltico 03-14-2019 05:14 PM

Re: Don't save form until record is saved on second form
 
Quote:

Originally Posted by theDBguy (Post 1615415)
Okay, no promises but try commenting out the line: Me.Dirty=False

That results in nothing being saved from the Main form at all. Second form saves to it's table.

AS I recall the Me.Dirty = False is necessary to pass the PK to the secondary form as the Main and secondary forms are bound to different tables.

theDBguy 03-14-2019 06:31 PM

Re: Don't save form until record is saved on second form
 
Hi. Remember when I said earlier it depends? So, you asked how not to save the data, and we managed to do that. However, we now found out we do need to save it to open the other forms. So, again, it depends. In your case, you may have to live with it or use unbound forms for everything and just use code to save the whole thing with one big Save button, after making sure there was no mistake.

Zydeceltico 03-14-2019 06:33 PM

Re: Don't save form until record is saved on second form
 
Quote:

Originally Posted by theDBguy (Post 1615426)
Hi. Remember when I said earlier it depends? So, you asked how not to save the data, and we managed to do that. However, we now found out we do need to save it to open the other forms. So, again, it depends. In your case, you may have to live with it or use unbound forms for everything and just use code to save the whole thing with one big Save button, after making sure there was no mistake.

What is the downside to unbound forms? I have to link every field via VBA?

theDBguy 03-14-2019 06:35 PM

Re: Don't save form until record is saved on second form
 
Quote:

Originally Posted by Zydeceltico (Post 1615428)
What is the downside to unbound forms? I have to link every field via VBA?

Pretty much. So, you’ll have to decide which is less work for you, make sure all ducks are in a row before saving all the records, or delete unwanted records after the fact?

Zydeceltico 03-14-2019 06:42 PM

Re: Don't save form until record is saved on second form
 
Quote:

Originally Posted by theDBguy (Post 1615429)
Pretty much. So, you’ll have to decide which is less work for you, make sure all ducks are in a row before saving all the records, or delete unwanted records after the fact?

What should I look at to delete after the fact? In other words - it would be a huge pain to redo all of the bound forms and I would much prefer to work out something that can run after the fact that deletes childless parents.

I just don't know what to even look at to do this.

Another great poster here gave me this:

Code:

SELECT tblinspectionevent.inspectionevent_pk
FROM  ((((tblinspectionevent
          LEFT JOIN tblinspectweldtests
                  ON tblinspectionevent.inspectionevent_pk =
                    tblinspectweldtests.inspectionevent_fk)
          LEFT JOIN tblinspectfabrication
                ON tblinspectionevent.inspectionevent_pk =
                    tblinspectfabrication.inspectionevent_fk)
        LEFT JOIN tblinspectweldassemble
                ON tblinspectionevent.inspectionevent_pk =
                  tblinspectweldassemble.inspectionevent_fk)
        LEFT JOIN tblinspectmill
              ON tblinspectionevent.inspectionevent_pk =
                  tblinspectmill.inspectionevent_fk)
      LEFT JOIN tblinspectgeneral
              ON tblinspectionevent.inspectionevent_pk =
                tblinspectgeneral.inspectionevent_fk
WHERE  (( NOT ( tblinspectfabrication.inspectionevent_fk ) IS NULL ))
        OR (( NOT ( tblinspectweldtests.inspectionevent_fk ) IS NULL ))
        OR (( NOT ( tblinspectgeneral.inspectionevent_fk ) IS NULL ))
        OR (( NOT ( tblinspectweldassemble.inspectionevent_fk ) IS NULL ))
        OR (( NOT ( tblinspectmill.inspectionevent_fk ) IS NULL ));

and this:

Code:

Public Function EventHasInspections(EventID As Long) As Boolean
  EventHasInspections = (DCount("*", "qryEventsWithInspections", "InspectionEvent_PK = " & EventID) > 0)
End Function

I know what they do but I am not at all sure how to utilize them - meaning I am not sure where to PUT them.

theDBguy 03-14-2019 06:50 PM

Re: Don't save form until record is saved on second form
 
Hi. I suppose you would put it at a place where you’re ready to check whether there were any mistakes made in opening the wrong forms or not. How would you normally check for this? How would you do it manually? Once you have a manual process, you could then try to automate it.

By the way, I can only give you general ideas because I am not familiar with your database, so I can’t offer any specific suggestions.

Zydeceltico 03-14-2019 06:53 PM

Re: Don't save form until record is saved on second form
 
Quote:

Originally Posted by theDBguy (Post 1615434)
Hi. I suppose you would put it at a place where you’re ready to check whether there were any mistakes made in opening the wrong forms or not. How would you normally check for this? How would you do it manually? Once you have a manual process, you could then try to automate it.


Should be right before I click "Save" om the secondary form. Before Update?

Does the SQL "live" as a saved query or is it in code?

Sorry for the "freshman" question. We have officially left my sphere of confidence. :-)

theDBguy 03-14-2019 06:58 PM

Re: Don't save form until record is saved on second form
 
Hi. SQL could mean several things and it could also “live” in several places. However, I am not sure it is relevant to this problem. It looks like you may have to use VBA to solve it. Of course, VBA could also execute SQL statements.

Zydeceltico 03-14-2019 07:12 PM

Re: Don't save form until record is saved on second form
 
Quote:

Originally Posted by theDBguy (Post 1615437)
It looks like you may have to use VBA to solve it. Of course, VBA could also execute SQL statements.


Do you happen to have any idea what the code looks like to say:

I know you've completed Form1 and moved on to Form2 while Form1 is still open and you didn't click a Save button on Form 1. And I know while you are on Form2 you decided this was a bad idea and wish to close Form2 and start over - and I know that you are hoping when you close Form 2 that all data on Form 1 will be erased - - - so click this button and it will happen?

:-)


Given that Form 1 is bound to a different table than Form 2 and the two are linked via the PK of the table behind Form1.

Zydeceltico 03-14-2019 07:34 PM

Re: Don't save form until record is saved on second form
 
Quote:

Originally Posted by theDBguy (Post 1615437)
Hi. SQL could mean several things and it could also “live” in several places. However, I am not sure it is relevant to this problem. It looks like you may have to use VBA to solve it. Of course, VBA could also execute SQL statements.

I should also probably mention that I have this code on all of the "various secondary forms":

Code:

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
    End If
  End If
End Sub

Again - this is to pass the ID from the originating Main form to the secondary forms and tables.

theDBguy 03-14-2019 07:35 PM

Re: Don't save form until record is saved on second form
 
Hi. There you go, you may have answered the question of where to put the code. If you add a “cancel” button on your inspection forms, you can use its Click event to delete the parent record if there’s no related child records in the other tables.


All times are GMT -8. The time now is 02:53 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World