Improve company productivity with a Business Account.Sign Up


Record Validation prior to continue to new record

Posted on 2014-02-20
Medium Priority
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 86
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 41

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.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.


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 41

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

595 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