Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

Record Validation prior to continue to new record

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

0
Karen Schaefer
Asked:
Karen Schaefer
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
JimFiveCommented:
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"
0
 
PatHartmanCommented:
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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Karen SchaeferAuthor Commented:
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

invoice
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
0
 
PatHartmanCommented:
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.
0
 
Karen SchaeferAuthor Commented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now