Cancel save after BeforeUpdate code runs

[theDBguy]

BTW - & FWIW - and returning to the main gist of this post - I finally coded everything I laid out earlier and it works great!

Thank You!

That simple loop utilizing the control tags as a marker makes the entire thing really simple.

Hi. Glad to hear it. And yes, VPN is safer than wifi. Good luck!

 

[The_Doc_Man]

As far as the issue here on WiFi:

When dealing with a shared back-end file, Access uses something called SMB protocol (Server Message Block) which is heavily dependent on certain TCP/IP features. The TCP family of protocols uses a lot of features but the one that most often applies to WiFi is the fact that session-oriented protocols have sequence numbers AND security restrictions often prevent reconnection of broken connections.

WiFi as a low-layer protocol carrier (VPN or not) is subject to signal losses a LOT more than hard-wired carriers, so if you take a carrier break, you cannot reconnect the session. The session between client and server becomes hung at whatever serial number. Even if you reconnect with a new connection, the old one is left hanging forever. Well, ... not really forever, it just seems that way. It eventually times out, usually at 30 seconds, which is the default for TCP timeouts. And when that timeout hits, that is when chaos joins the fracas.

It is possible because of that broken carrier that you will have half-updated buffers. If your tablet was inactive, your session might still need restarting but will be a case of no harm, no foul. If your tablet was actively RECEIVING data (e.g. a SELECT query), the FE sometimes gets messed up and you surely have to restart Access itself. The FE will sometimes have to do an auto-recovery in that case but unless you are storing data locally in the FE, that is inconsequential. However, if your tablet was actively SENDING data (e.g. UPDATE or INSERT INTO queries), the BE gets messed up and you might have to stop EVERYONE while you try to recover the BE file.

If you have figured out how to hand-hold your co-users to step them through the data validation sequence, then great work! Be EXTREMELY careful about the tablets. I wish you good luck and a strong, reliable WiFi network. But we have seen cases where folks get burned by WiFi carriers.

 

[Zydeceltico]

So for anyone following along.....here's what I am trying to accomplish:

At work in a manufacturing plant any given manufacturing line might need to be stopped at any given time for a host of reasons. It is REALLY important to document the WHY and the HOW LONG that line was shut down.

So I have a form called frmLineStop.

The first 3 pieces of data that we have to have are 1) which line was shut down; 2) the reason it was shut down; and 3) a "Start" time and date for WHEN it was shut down. After that there are several other pieces of data to collect but it is critical to collect the first three and it is important to NOT click save without all three. I want to insure that the multiple users of this DB always collect those 3 key pieces of info.

You might be wondering "If he is collecting a Start time ......what about an END time?.........." If you look at the first code below you'll see that I have two conditions for opening this form. One is new to add new data and the other is to RETURN to the record to finish entering data..... in essence to at some point in the future to click the "STOP" button on the timer so I can collect the range of time - -the duration of time - that manufacturing line was shut down.

So I made it that when the user opens the form for a Line Stop ALL controls are disabled except for the first required one. I did this using/defining the "Tag" property of all controls. I defined them as either "required" or "secondary." This is the Load code for the form (it contains both conditions for opening whether it is "new" for data entry or whether it is returning to enter a STOP time:

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
                Me.txtFinalProd_FK = intFinalProdID
                Me.NavigationButtons = False
                Me.cmdSaveLineStop.Enabled = False
                For Each ctl In Me.Controls
                    If ctl.Tag = "required" Or ctl.Tag = "secondary" Then
                    ctl.Enabled = False
                    End If
                Next
                Me.cboLine1Workstation.Enabled = True
            End If
    Else
        If IsNull(Me.OpenArgs) Then
            Me.Filter = "LineStopBegin Is Not Null AND LineStopEnd Is Null"
            Me.FilterOn = True
            Me.NavigationButtons = True
        End If
    End If
End Sub

Notice that I turned the first required control back on with:

                Me.cboLine1Workstation.Enabled = True

It is FAR easier to turn them all off and turn one back on than it is to try and be selective. Waste of time.

I am also going to light that control up by adding some BackColor to it just for emphasis.....

But my point is - the user is forced to enter data in that control. There are no other options.

When data is entered in that control I now have the opportunity to utilize that control's On Dirty event which I use to set my next required control to Enabled = True.

I do the same thing with the third and final control.

When the third control has a value placed in it I can call it's On Dirty event and use :

                For Each ctl In Me.Controls
                    If ctl.Tag = "secondary" Then
                    ctl.Enabled = True
                    End If
                Next

....and Enable = True all of the rest of the controls....including the Form's Save button which I failed to mention earlier was my primary goal - - - - get the data I want from the user before they accidentally/inadvertently click "Save" before they should.

I only write this novel because so many great folks have helped me out so much that I thought it might be time to make an effort to give back a little.......and I sure hop this helps someone more than it confuses them.

Thanks,

Tim

 

[Zydeceltico]

The FE will sometimes have to do an auto-recovery in that case but unless you are storing data locally in the FE, that is inconsequential.

Is there a way to store data w/ an FE to be later appended to a BE? I didn't think this was possible.

 

[Zydeceltico]

Hi. Glad to hear it. And yes, VPN is safer than wifi. Good luck!

I just noticed that my major On Load code works just fine despite the fact that I never declared a variable called "ctl" anywhere.

How would I declare that?

Dim ctl as ?

 

[theDBguy]

I just noticed that my major On Load code works just fine despite the fact that I never declared a variable called "ctl" anywhere.

How would I declare that?

Dim ctl as ?

As Variant or Object or Access.Control

 

[The_Doc_Man]

Is there a way to store data w/ an FE to be later appended to a BE?

Just because you HAVE a formal back-end doesn't mean all of your data has to go there. You can put data in a front-end table that ISN'T linked but rather is local. No biggie there, just build the table where it needs to be for the problem.

Note, however, that doing so will often result in the subsequent need to clean up the FE file by deleting the local info, then doing a Compact & Repair on it. OR... you can take ANOTHER approach if you are using FE tables:

Make part of your operational procedure that every day, your tablet has to physically connect to the network, download a new copy of the FE, and then go on about the business of taking data locally. But then, as part of the end-of-the-day routine, you have to physically connect to the network and upload your data. That way, you do not have to depend on the WiFi issues.

The morning download of the FE gets rid of bloat. The end-of-shift upload can resync the day's worth of data. However, to do that without running into major issues, you would have ONLY local tables on the tablet and would run a procedure, maybe a form, that explicitly opens a remote database over a physical connection. You can then run UPDATE or INSERT INTO queries from the local tables to the table reached through the physical network connection. I.e. it is NOT a statically linked BE but a dynamically linked BE. WiFi never enters into that situation.

Here is an example of using an "IN" clause in query (that isn't related to a subquery):

https://access-programmers.co.uk/forums/showthread.php?t=292728

Here is a more detailed technical article:

https://docs.microsoft.com/en-us/of.../miscellaneous/in-clause-microsoft-access-sql

In essence, you would declare the location of the target table using an IN clause behind the table-name in the appropriate place within the query. A JOIN query using this kind of connection to join one local and one remote table might not be wise, but it should work OK for UPDATE or INSERT INTO queries with a target table or query that is entirely on the targeted host.

There is another approach involving the idea that you can have the tablet build transaction files, essentially flat files detailing what is to be uploaded. Maybe make a CSV file as your output. Then trigger a script that uses the WiFi to transfer the file using some sort of FTP connection. SMB protocols really don't like interrupted transfers, but FTP is (a) slightly different, and (b) you will know if the file made it. Once the transaction file is present, you can have an active Access on the BE server continually looking for transaction files to be imported. The key is that if you transfer a completed file, you can do it in stages using an FTP script to (1) transfer the file (2) verify the file (3) rename the file to some format that your BE Access app can recognize as "good to go." And the BE app can rename the file or delete it once it has been imported, as needed by your process requirements.