Don't save form until record is saved on second form (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
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

I’m here to help
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
10,693
Hi Tim. It depends. Can you please show us the code you’re using to open the various inspection forms? Thanks.
 

Zydeceltico

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
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

I’m here to help
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
10,693
Okay, no promises but try commenting out the line: Me.Dirty=False
 

Zydeceltico

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
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

I’m here to help
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
10,693
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

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
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

I’m here to help
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
10,693
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

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
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

I’m here to help
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
10,693
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

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
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

I’m here to help
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
10,693
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

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
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.
 
Last edited:

Zydeceltico

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
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

I’m here to help
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
10,693
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.
 

Zydeceltico

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
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.
Please accept my - current state. :)

Any even remote idea what the code looks like on that cancel button to reach across to the originating form to delete the parent record?

I am soooo grateful that you are following this post with me, It's one of the "last big hurdles."
 

Zydeceltico

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
Please accept my - current state. :)

Any even remote idea what the code looks like on that cancel button to reach across to the originating form to delete the parent record?

I am soooo grateful that you are following this post with me, It's one of the "last big hurdles."

Or more specifically - - How do I reference back from Form 2 to Form 1?
 

Zydeceltico

Registered User.
Local time
Today, 10:02
Joined
Dec 5, 2017
Messages
764
Google tells me to look to look at Unmatched Query Wizard and convert to Delete query. I'll play with that for awhile.
 

theDBguy

I’m here to help
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
10,693
Hi. You don't need to reach back to form 1 because you already passed the ID to form 2, you should be able to use the same information to delete the unwanted record.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:02
Joined
May 21, 2018
Messages
3,180
The purpose of the code I posted is that you cannot do a simple unmatched query. You can have inspections in multiple tables. So the query checks if have any child inspection is in any table.

https://www.access-programmers.co.uk/forums/showpost.php?p=1615432&postcount=9

If you do not have any inspection and return to the main form, you now can decide to delete the InspectionEvent. You can run the check at any time.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom