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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 37

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.
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.


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 37

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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