Solved

Record Validation prior to continue to new record

Posted on 2014-02-20
6
556 Views
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

0
Comment
Question by:Karen Schaefer
6 Comments
 
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.
0
 
LVL 15

Expert Comment

by:JimFive
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"
0
 
LVL 35

Expert Comment

by:PatHartman
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Accepted Solution

by:
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

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
 
LVL 35

Expert Comment

by:PatHartman
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.
0
 

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
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

776 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