Record Validation prior to continue to new record

Posted on 2014-02-20
Last Modified: 2014-02-26
I am looking for the best approach to validating that a record has been printed prior to allowing the user to move onto the next record.

I have a hidden field named "RecordLock",  I need to check prior to the entry of new record that no records have a blank value for Recordlock - if there is then force the  user to return the that record and finish the processing (meaning click the Print button)

Here is my code so far:  

On beforeupdate of the named combo.  See the ;>> I need to be able to return to the previous record and print then set the "RecordLock" field = true.  This is where I am having an issue.

PS.  keep in mind that this is a Mainform/suform and field in question is on the subform - but the Print button is on the Mainform

Private Sub cboVendorName_BeforeUpdate(Cancel As Integer)
Dim curDB As DAO.Database
Dim strSQL As String
Dim rs As DAO.Recordset
Dim X As Integer
Dim ctl As Control
Dim frm As AccessObject

Set curDB = CurrentDb

gContractID = Me.ContractNumber
gInvID = Me.IDInvoice
strSQL = "SELECT RecordLock" & _
        " FROM tblinvoice" & _
        " WHERE (((ContractNumber)=" & Chr(34) & gContractID & Chr(34) & ") AND ((RecordLock)=False))"
Set rs = curDB.OpenRecordset(strSQL)

    If rs.RecordCount > 0 Then
        Select Case MsgBox("Sorry unable to process request until previous Invoice has been printed." _
                           & vbCrLf & "" _
                           & vbCrLf & "Do you wish to print last Invoice entry?" _
                           , vbYesNoCancel Or vbCritical Or vbDefaultButton1, "Invoice Needs to be Printed")
            Case vbYes
                DoCmd.OpenReport "rptAPCodingSlip", acViewPreview, "IDInvoice = " & Chr(39) & gInvID & Chr(39) & ""
                DoCmd.OpenReport "rptInvoiceActivity_Slip", acViewPreview, "ContractNo = " & Chr(34) & gContractID & Chr(34) & ""
     '>>>>>                   If X = 0 Then
                            DoCmd.RunSQL "Update tblinvoice Set RecordLock = -1 where IDInvoice = " & gInvID & ""
                       End If
            Case vbNo Or vbCancel
                Exit Sub
        End Select
    End If
End Sub

Open in new window

Question by:Karen Schaefer
LVL 84
ID: 39876416
You cannot verify whether a document has physically printed. The only thing you can do is issue a DoCmd.Print call with the acViewNormal argument, and then assume that was completed:

 DoCmd.OpenReport "rptAPCodingSlip", acViewNormal, "IDInvoice = " & Chr(39) & gInvID & Chr(39) & ""
                DoCmd.OpenReport "rptInvoiceActivity_Slip", acViewNormal, "ContractNo = " & Chr(34) & gContractID & Chr(34) & ""

Or you could further involve the user to verify the print took place:

If Msgbox("Printing Complete?", vbYesNo) = vbYes Then
  ' continue on with other code
End If

This can be disruptive, and many users will simply automatically hit the Enter key, or click Yes, regardless of the state of the printout.
LVL 15

Expert Comment

ID: 39876810
I would suggest looking into using either the Current Event or the Lost Focus event of the Form to trigger your check.

I'm not sure what you are trying to check with "IF X=0"
LVL 36

Expert Comment

ID: 39876921
Access doesn't provide us with a "leaving the record" event so there is no clean way to implement your request.  Currently you are hanging your hat on an event that might not even fire.  What happens if the user closes the form or doesn't change the VendorName?

What I would do is add a printDate to the table and update it when the user presses the print button.  As Scott already mentioned, you don't have a way to verify that the print actually happened and I agree that pestering the user with a question will desensitize him and he'll simply press yes to close the box.  Then every time the application opens or the form you use to start this data entry process opens, I would run a query that looks for any records with a null PrintDate and remind the user to finish the process.  This gets a little more complicated in a multi-user environment unless you want to pester all users regardless of who did the initial data entry.  If you only want to warn the person who added the record, you will also need to have a field in the table that tracks the user who made the change.  I do this for all tables as a matter of course.  It's not much of an audit trail but is simple and only takes one line of code in each module's BeforeUpdate event to populate it.
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.


Accepted Solution

Karen Schaefer earned 0 total points
ID: 39877116
Sorry to be so confusing - let me restate my issue.

in the tblInvoice - contains afield named "recordLock" (checkbox). This box will be updated via an Update query when the user Clicks a button on the Main form to print the reports.  I do not need to verify if the report printed I just need to verify that the  Field ("RecordLock) does not contain a null or equals zero value prior to the user entering the next record.

Note:  " Then every time the application opens or the form you use to start this data entry process opens, I would run a query that looks for any records with a null PrintDate and remind the user to finish the process.  "  THIS IS WHAT I AM ATTEMPTING.

My thought was to Before update of the dropdown (cboVendorName) the code would verify that there aren't any previous records for the current ContractNo where the REcordLock field = 0.

I got that part working ok, the problem I am having is current entry from user removed and going to the record that has the Recordlock=0 - hence forcing the user to deal with the not-printed record prior to moving forward with their dataentry of the next record.

Sorry I wasn't more clear in the beginning.

"What happens if the user closes the form or doesn't change the VendorName?"

The report will not print, but the next time they attempt to enter a new record for the ContractNumber in question - they should be forced to deal with it.  Note I also have code that requires all visible fields to contain data. -- since the Recordlock is not updated til the user clicks the Print button that is why I am using this field to check for unprinted documents.  This is why I placed the code on BeforeUpdate of the CboVendorName.

Thanks for the input.

I also moved the code to afterupdate of Vendor name .
here is a sample of the screen - see hightlighted area

I have found some code that helps me partially -

Dim GoBackToThisRecord As Integer

DoCmd.RunCommand acCmdUndo
GoBackToThisRecord = Me.CurrentRecord
Me.Recordset.Move GoBackToThisRecord '- 1

Open in new window

this code clears the current entry, however, I would like to have the code automatically return to the first record where Record Lock = 0, then forcing the user to deal with the printing of the invoice.

Debug code the following:

Me.Recordset.Move GoBackToThisRecord '- 1
GoBackToThisRecord  value = 11
LVL 36

Expert Comment

ID: 39877332
The report will not print, but the next time they attempt to enter a new record for the ContractNumber in question - they should be forced to deal with it.
Using ANY control level event for this purpose is inappropriate.  What happens if they don't add/change a record for the same Contract for weeks?  Or, never?  If you really want to enforce the printing, you need to do it at a higher level as I suggested.

Author Closing Comment

by:Karen Schaefer
ID: 39888293
Thanks for the input, however, I was able to find the perfect solution.

                DoCmd.RunCommand acCmdUndo
                Set rst = Me.RecordsetClone
                strCriteria = "RecordLock=False"
                rst.FindFirst strCriteria
                Me.Bookmark = rst.Bookmark

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question